Fix the LIMIT clause so that it applies to the entire query in a compound
query. Prior to this change LIMITs on compound queries did not work at
all. Ticket #393. (CVS 1058)
FossilOrigin-Name: 543479e3aed77976a0c689cf40811bf88353f706
diff --git a/src/expr.c b/src/expr.c
index d6b9b12..7f3f96f 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
-** $Id: expr.c,v 1.96 2003/05/31 16:21:12 drh Exp $
+** $Id: expr.c,v 1.97 2003/07/20 01:16:47 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -235,6 +235,8 @@
pNew->nLimit = p->nLimit;
pNew->nOffset = p->nOffset;
pNew->zSelect = 0;
+ pNew->iLimit = -1;
+ pNew->iOffset = -1;
return pNew;
}
diff --git a/src/select.c b/src/select.c
index b5d1727..e648df2 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.144 2003/07/19 00:44:14 drh Exp $
+** $Id: select.c,v 1.145 2003/07/20 01:16:47 drh Exp $
*/
#include "sqliteInt.h"
@@ -52,6 +52,8 @@
pNew->op = TK_SELECT;
pNew->nLimit = nLimit;
pNew->nOffset = nOffset;
+ pNew->iLimit = -1;
+ pNew->iOffset = -1;
}
return pNew;
}
@@ -450,13 +452,13 @@
** to see if this row should be output.
*/
if( pOrderBy==0 ){
- if( p->nOffset>0 ){
+ if( p->iOffset>=0 ){
int addr = sqliteVdbeCurrentAddr(v);
- sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+2);
+ sqliteVdbeAddOp(v, OP_MemIncr, p->iOffset, addr+2);
sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
}
- if( p->nLimit>=0 ){
- sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, iBreak);
+ if( p->iLimit>=0 ){
+ sqliteVdbeAddOp(v, OP_MemIncr, p->iLimit, iBreak);
}
}
@@ -620,13 +622,13 @@
if( eDest==SRT_Sorter ) return;
sqliteVdbeAddOp(v, OP_Sort, 0, 0);
addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end);
- if( p->nOffset>0 ){
- sqliteVdbeAddOp(v, OP_MemIncr, p->nOffset, addr+4);
+ if( p->iOffset>=0 ){
+ sqliteVdbeAddOp(v, OP_MemIncr, p->iOffset, addr+4);
sqliteVdbeAddOp(v, OP_Pop, 1, 0);
sqliteVdbeAddOp(v, OP_Goto, 0, addr);
}
- if( p->nLimit>=0 ){
- sqliteVdbeAddOp(v, OP_MemIncr, p->nLimit, end);
+ if( p->iLimit>=0 ){
+ sqliteVdbeAddOp(v, OP_MemIncr, p->iLimit, end);
}
switch( eDest ){
case SRT_Callback: {
@@ -1245,6 +1247,52 @@
}
/*
+** Compute the iLimit and iOffset fields of the SELECT based on the
+** nLimit and nOffset fields. nLimit and nOffset hold the integers
+** that appear in the original SQL statement after the LIMIT and OFFSET
+** keywords. Or that hold -1 and 0 if those keywords are omitted.
+** iLimit and iOffset are the integer memory register numbers for
+** counters used to compute the limit and offset. If there is no
+** limit and/or offset, then iLimit and iOffset are negative.
+**
+** This routine changes the values if iLimit and iOffset only if
+** a limit or offset is defined by nLimit and nOffset. iLimit and
+** iOffset should have been preset to appropriate default values
+** (usually but not always -1) prior to calling this routine.
+** Only if nLimit>=0 or nOffset>0 do the limit registers get
+** redefined. The UNION ALL operator uses this property to force
+** the reuse of the same limit and offset registers across multiple
+** SELECT statements.
+*/
+static void computeLimitRegisters(Parse *pParse, Select *p){
+ /*
+ ** 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.
+ ** The current implementation interprets "LIMIT 0" to mean
+ ** no rows.
+ */
+ if( p->nLimit>=0 ){
+ int iMem = pParse->nMem++;
+ Vdbe *v = sqliteGetVdbe(pParse);
+ if( v==0 ) return;
+ sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
+ sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
+ p->iLimit = iMem;
+ }
+ if( p->nOffset>0 ){
+ int iMem = pParse->nMem++;
+ Vdbe *v = sqliteGetVdbe(pParse);
+ if( v==0 ) return;
+ sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
+ sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
+ p->iOffset = iMem;
+ }
+}
+
+/*
** This routine is called to process a query that is really the union
** or intersection of two or more separate queries.
**
@@ -1279,8 +1327,8 @@
Select *pPrior; /* Another SELECT immediately to our left */
Vdbe *v; /* Generate code to this VDBE */
- /* Make sure there is no ORDER BY clause on prior SELECTs. Only the
- ** last SELECT in the series may have an ORDER BY.
+ /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
+ ** the last SELECT in the series may have an ORDER BY or LIMIT.
*/
if( p==0 || p->pPrior==0 ) return 1;
pPrior = p->pPrior;
@@ -1289,6 +1337,11 @@
selectOpName(p->op));
return 1;
}
+ if( pPrior->nLimit>=0 || pPrior->nOffset>0 ){
+ sqliteErrorMsg(pParse,"LIMIT clause should come after %s not before",
+ selectOpName(p->op));
+ return 1;
+ }
/* Make sure we have a valid query engine. If not, create a new one.
*/
@@ -1307,9 +1360,15 @@
switch( p->op ){
case TK_ALL: {
if( p->pOrderBy==0 ){
+ pPrior->nLimit = p->nLimit;
+ pPrior->nOffset = p->nOffset;
rc = sqliteSelect(pParse, pPrior, eDest, iParm, 0, 0, 0);
if( rc ) return rc;
p->pPrior = 0;
+ p->iLimit = pPrior->iLimit;
+ p->iOffset = pPrior->iOffset;
+ p->nLimit = -1;
+ p->nOffset = 0;
rc = sqliteSelect(pParse, p, eDest, iParm, 0, 0, 0);
p->pPrior = pPrior;
if( rc ) return rc;
@@ -1322,10 +1381,11 @@
int unionTab; /* Cursor number of the temporary table holding result */
int op; /* One of the SRT_ operations to apply to self */
int priorOp; /* The SRT_ operation to apply to prior selects */
+ int nLimit, nOffset; /* Saved values of p->nLimit and p->nOffset */
ExprList *pOrderBy; /* The ORDER BY clause for the right SELECT */
priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
- if( eDest==priorOp && p->pOrderBy==0 ){
+ if( eDest==priorOp && p->pOrderBy==0 && p->nLimit<0 && p->nOffset==0 ){
/* We can reuse a temporary table generated by a SELECT to our
** right.
*/
@@ -1362,9 +1422,15 @@
p->pPrior = 0;
pOrderBy = p->pOrderBy;
p->pOrderBy = 0;
+ nLimit = p->nLimit;
+ p->nLimit = -1;
+ nOffset = p->nOffset;
+ p->nOffset = 0;
rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0);
p->pPrior = pPrior;
p->pOrderBy = pOrderBy;
+ p->nLimit = nLimit;
+ p->nOffset = nOffset;
if( rc ) return rc;
/* Convert the data in the temporary table into whatever form
@@ -1380,6 +1446,7 @@
iBreak = sqliteVdbeMakeLabel(v);
iCont = sqliteVdbeMakeLabel(v);
sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
+ computeLimitRegisters(pParse, p);
iStart = sqliteVdbeCurrentAddr(v);
multiSelectSortOrder(p, p->pOrderBy);
rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
@@ -1399,6 +1466,7 @@
case TK_INTERSECT: {
int tab1, tab2;
int iCont, iBreak, iStart;
+ int nLimit, nOffset;
/* INTERSECT is different from the others since it requires
** two temporary tables. Hence it has its own case. Begin
@@ -1422,8 +1490,14 @@
sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1);
sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1);
p->pPrior = 0;
+ nLimit = p->nLimit;
+ p->nLimit = -1;
+ nOffset = p->nOffset;
+ p->nOffset = 0;
rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0);
p->pPrior = pPrior;
+ p->nLimit = nLimit;
+ p->nOffset = nOffset;
if( rc ) return rc;
/* Generate code to take the intersection of the two temporary
@@ -1437,6 +1511,7 @@
iBreak = sqliteVdbeMakeLabel(v);
iCont = sqliteVdbeMakeLabel(v);
sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
+ computeLimitRegisters(pParse, p);
iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
multiSelectSortOrder(p, p->pOrderBy);
@@ -1869,6 +1944,7 @@
*/
sqliteCodeVerifySchema(pParse, pTab->iDb);
base = p->pSrc->a[0].iCursor;
+ computeLimitRegisters(pParse, p);
sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
sqliteVdbeAddOp(v, OP_OpenRead, base, pTab->tnum);
sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
@@ -2132,39 +2208,6 @@
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.
- **
- ** Note that up until this point, the nLimit and nOffset hold
- ** the numeric values of the limit and offset that appeared in
- ** the original SQL. After this code, the nLimit and nOffset hold
- ** the register number of counters used to track the limit and
- ** offset.
- */
- 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;
- }
-
/* Check for the special case of a min() or max() function by itself
** in the result set.
*/
@@ -2211,6 +2254,10 @@
return rc;
}
+ /* Set the limiter.
+ */
+ computeLimitRegisters(pParse, p);
+
/* Identify column types if we will be using a callback. This
** step is skipped if the output is going to a destination other
** than a callback.
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index ceb54c7..465f6af 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.193 2003/06/23 11:06:02 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.194 2003/07/20 01:16:47 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
@@ -778,16 +778,17 @@
** in the VDBE that record the limit and offset counters.
*/
struct Select {
- int isDistinct; /* True if the DISTINCT keyword is present */
ExprList *pEList; /* The fields of the result */
+ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
+ u8 isDistinct; /* True if the DISTINCT keyword is present */
SrcList *pSrc; /* The FROM clause */
Expr *pWhere; /* The WHERE clause */
ExprList *pGroupBy; /* The GROUP BY clause */
Expr *pHaving; /* The HAVING clause */
ExprList *pOrderBy; /* The ORDER BY clause */
- int op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
Select *pPrior; /* Prior select in a compound select statement */
int nLimit, nOffset; /* LIMIT and OFFSET values. -1 means not used */
+ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */
char *zSelect; /* Complete text of the SELECT command */
};