/ Check-in [e6a752bf]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow negative values for LIMIT and OFFSET. Add tests for negative LIMITs and OFFSETs. Make the OFFSET work even if LIMIT is 0 or negative. (CVS 1052)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e6a752bfef24f773973c151c6262ff331a9dc57a
User & Date: drh 2003-07-16 02:19:38
Context
2003-07-16
11:51
Make LIMIT 0 return no rows. LIMIT -1 still returns all rows. Ticket #346. (CVS 1053) check-in: a31d0bd9 user: drh tags: trunk
02:19
Allow negative values for LIMIT and OFFSET. Add tests for negative LIMITs and OFFSETs. Make the OFFSET work even if LIMIT is 0 or negative. (CVS 1052) check-in: e6a752bf user: drh tags: trunk
00:54
Initialize a variable to prevent an MSVC compiler warning. Ticket #394. (CVS 1051) check-in: 96e3c539 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
149
150
151
152
153
154
155
156

157
158
159
160
161
162
163
164
165
...
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.98 2003/05/17 19:04:04 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
type ::= typename(X).                    {sqliteAddColumnType(pParse,&X,&X);}
type ::= typename(X) LP signed RP(Y).    {sqliteAddColumnType(pParse,&X,&Y);}
type ::= typename(X) LP signed COMMA signed RP(Y).
                                         {sqliteAddColumnType(pParse,&X,&Y);}
%type typename {Token}
typename(A) ::= ids(X).           {A = X;}
typename(A) ::= typename(X) ids.  {A = X;}
signed ::= INTEGER.

signed ::= PLUS INTEGER.
signed ::= MINUS INTEGER.
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT nm ccons.
carg ::= ccons.
carg ::= DEFAULT STRING(X).          {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT ID(X).              {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT INTEGER(X).         {sqliteAddDefaultValue(pParse,&X,0);}
................................................................................

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}
limit_opt(A) ::= .                  {A.limit = -1; A.offset = 0;}
limit_opt(A) ::= LIMIT INTEGER(X).  {A.limit = atoi(X.z); A.offset = 0;}
limit_opt(A) ::= LIMIT INTEGER(X) OFFSET INTEGER(Y). 
                                    {A.limit = atoi(X.z); A.offset = atoi(Y.z);}
limit_opt(A) ::= LIMIT INTEGER(X) COMMA INTEGER(Y). 
                                    {A.limit = atoi(Y.z); A.offset = atoi(X.z);}

/////////////////////////// The DELETE statement /////////////////////////////
//
cmd ::= DELETE FROM nm(X) dbnm(D) where_opt(Y). {
   sqliteDeleteFrom(pParse, sqliteSrcListAppend(0,&X,&D), Y);
}








|







 







|
>
|
|







 







|
|
|
|
|
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.99 2003/07/16 02:19:38 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
type ::= typename(X).                    {sqliteAddColumnType(pParse,&X,&X);}
type ::= typename(X) LP signed RP(Y).    {sqliteAddColumnType(pParse,&X,&Y);}
type ::= typename(X) LP signed COMMA signed RP(Y).
                                         {sqliteAddColumnType(pParse,&X,&Y);}
%type typename {Token}
typename(A) ::= ids(X).           {A = X;}
typename(A) ::= typename(X) ids.  {A = X;}
%type signed {int}
signed(A) ::= INTEGER(X).         { A = atoi(X.z); }
signed(A) ::= PLUS INTEGER(X).    { A = atoi(X.z); }
signed(A) ::= MINUS INTEGER(X).   { A = -atoi(X.z); }
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT nm ccons.
carg ::= ccons.
carg ::= DEFAULT STRING(X).          {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT ID(X).              {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT INTEGER(X).         {sqliteAddDefaultValue(pParse,&X,0);}
................................................................................

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}
limit_opt(A) ::= .                     {A.limit = -1; A.offset = 0;}
limit_opt(A) ::= LIMIT signed(X).      {A.limit = X; A.offset = 0;}
limit_opt(A) ::= LIMIT signed(X) OFFSET signed(Y). 
                                       {A.limit = X; A.offset = Y;}
limit_opt(A) ::= LIMIT signed(X) COMMA signed(Y). 
                                       {A.limit = Y; A.offset = X;}

/////////////////////////// The DELETE statement /////////////////////////////
//
cmd ::= DELETE FROM nm(X) dbnm(D) where_opt(Y). {
   sqliteDeleteFrom(pParse, sqliteSrcListAppend(0,&X,&D), Y);
}

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2136
2137
2138
2139
2140
2141
2142
2143







2144
2145
2146
2147
2148
2149
2150
2151
2152

2153
2154
2155
2156

2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.141 2003/06/16 00:40:35 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  int isDistinct,       /* true if the DISTINCT keyword is present */
  int nLimit,           /* LIMIT value.  -1 means not used */
  int nOffset           /* OFFSET value.  -1 means not used */
){
  Select *pNew;
  pNew = sqliteMalloc( sizeof(*pNew) );
  if( pNew==0 ){
    sqliteExprListDelete(pEList);
    sqliteSrcListDelete(pSrc);
    sqliteExprDelete(pWhere);
................................................................................
  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, pTabList, pEList);
  }

  /* Set the limiter







  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;
    p->nOffset = 0;
  }else{
    int iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
    p->nLimit = iMem;

    if( p->nOffset<=0 ){
      p->nOffset = 0;
    }else{
      iMem = pParse->nMem++;

      sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
      sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
      p->nOffset = iMem;
    }
  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext;
    int needRestoreContext;







|







 







|







 







|
>
>
>
>
>
>
>



<





>
|
|
|
|
>
|
|
|
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153

2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167

2168
2169
2170
2171
2172
2173
2174
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.142 2003/07/16 02:19:38 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  int isDistinct,       /* true if the DISTINCT keyword is present */
  int nLimit,           /* LIMIT value.  -1 means not used */
  int nOffset           /* OFFSET value.  0 means no offset */
){
  Select *pNew;
  pNew = sqliteMalloc( sizeof(*pNew) );
  if( pNew==0 ){
    sqliteExprListDelete(pEList);
    sqliteSrcListDelete(pSrc);
    sqliteExprDelete(pWhere);
................................................................................
  /* Identify column names if we will be using them in a callback.  This
  ** step is skipped if the output is going to some other destination.
  */
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, pTabList, pEList);
  }

  /* Set the limiter.
  **
  ** The phrase "LIMIT 0" means all rows are shown, not zero rows.
  ** If the comparison is p->nLimit<=0 then "LIMIT 0" shows
  ** all rows.  It is the same as no limit. If the comparision is
  ** p->nLimit<0 then "LIMIT 0" show no rows at all.
  ** "LIMIT -1" always shows all rows.  There is some
  ** contraversy about what the correct behavior should be.
  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;

  }else{
    int iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
    p->nLimit = iMem;
  }
  if( p->nOffset<=0 ){
    p->nOffset = 0;
  }else{
    int iMem = pParse->nMem++;
    if( iMem==0 ) iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
    p->nOffset = iMem;

  }

  /* Generate code for all sub-queries in the FROM clause
  */
  for(i=0; i<pTabList->nSrc; i++){
    const char *zSavedAuthContext;
    int needRestoreContext;

Changes to test/limit.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
158
159
160
161
162
163
164
165








































166
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.7 2003/02/20 00:44:53 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
set fd [open data1.txt w]
................................................................................
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
                   ORDER BY 1, 2 LIMIT 1000;
    SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
  }
} {1000 1528204 593161 0 3107 505 1005}









































finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.8 2003/07/16 02:19:38 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
set fd [open data1.txt w]
................................................................................
  execsql {
    DELETE FROM t5;
    INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
                   ORDER BY 1, 2 LIMIT 1000;
    SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
  }
} {1000 1528204 593161 0 3107 505 1005}

# There is some contraversy about whether LIMIT 0 should be the same as
# no limit at all or if LIMIT 0 should result in zero output rows.
#
do_test limit-6.1 {
  execsql {
    BEGIN;
    CREATE TABLE t6(a);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 SELECT a+2 FROM t6;
    COMMIT;
    SELECT * FROM t6;
  }
} {1 2 3 4}
do_test limit-6.2 {
  execsql {
    SELECT * FROM t6 LIMIT -1 OFFSET -1;
  }
} {1 2 3 4}
do_test limit-6.3 {
  execsql {
    SELECT * FROM t6 LIMIT 2 OFFSET -123;
  }
} {1 2}
do_test limit-6.4 {
  execsql {
    SELECT * FROM t6 LIMIT -432 OFFSET 2;
  }
} {3 4}
do_test limit-6.5 {
  execsql {
    SELECT * FROM t6 LIMIT 0
  }
} {1 2 3 4}
do_test limit-6.6 {
  execsql {
    SELECT * FROM t6 LIMIT 0 OFFSET 1
  }
} {2 3 4}

finish_test