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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e6a752bfef24f773973c151c6262ff33 |
User & Date: | drh 2003-07-16 02:19:38.000 |
Context
2003-07-16
| ||
11:51 | Make LIMIT 0 return no rows. LIMIT -1 still returns all rows. Ticket #346. (CVS 1053) (check-in: a31d0bd90a 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: e6a752bfef user: drh tags: trunk) | |
00:54 | Initialize a variable to prevent an MSVC compiler warning. Ticket #394. (CVS 1051) (check-in: 96e3c53958 user: drh tags: trunk) | |
Changes
Changes to src/parse.y.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** 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. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** 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 ){ |
︙ | ︙ | |||
149 150 151 152 153 154 155 | 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;} | | > | | | 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 | 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);} |
︙ | ︙ | |||
438 439 440 441 442 443 444 | %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} | | | | | | | | 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 | %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 | ** 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. ** | | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** 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. */ Select *sqliteSelectNew( ExprList *pEList, /* which columns to include in the result */ 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); |
︙ | ︙ | |||
2136 2137 2138 2139 2140 2141 2142 | /* 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); } | | > > > > > > > < > | | | | > | | | < | 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 | /* 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 | # 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. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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] |
︙ | ︙ | |||
158 159 160 161 162 163 164 165 166 | 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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 |