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)

FossilOrigin-Name: e6a752bfef24f773973c151c6262ff331a9dc57a

2 files changed, 30 insertions, 22 deletions

diff --git a/src/parse.y b/src/parse.y
index f3b979516..4efb90ed7 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -14,7 +14,7 @@
 ** 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 $
+** @(#) $Id: parse.y,v 1.99 2003/07/16 02:19:38 drh Exp $
 */
 %token_prefix TK_
 %token_type {Token}
@@ -153,9 +153,10 @@ type ::= typename(X) LP signed COMMA signed RP(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.
+%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.
@@ -442,12 +443,12 @@ 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);}
+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 /////////////////////////////
 //
diff --git a/src/select.c b/src/select.c
index 5b0d37569..22df1d15e 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
 ** 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 $
+** $Id: select.c,v 1.142 2003/07/16 02:19:38 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -30,7 +30,7 @@ Select *sqliteSelectNew(
   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 */
+  int nOffset           /* OFFSET value.  0 means no offset */
 ){
   Select *pNew;
   pNew = sqliteMalloc( sizeof(*pNew) );
@@ -2140,24 +2140,31 @@ int sqliteSelect(
     generateColumnNames(pParse, pTabList, pEList);
   }
 
-  /* Set the limiter
+  /* 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;
-    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;
-    }
+  }
+  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