diff options
Diffstat (limited to 'ext/sqlite/libsqlite/src/select.c')
| -rw-r--r-- | ext/sqlite/libsqlite/src/select.c | 221 |
1 files changed, 125 insertions, 96 deletions
diff --git a/ext/sqlite/libsqlite/src/select.c b/ext/sqlite/libsqlite/src/select.c index 03153bcd8a..c0aa4ecfeb 100644 --- a/ext/sqlite/libsqlite/src/select.c +++ b/ext/sqlite/libsqlite/src/select.c @@ -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) ); @@ -52,6 +52,8 @@ Select *sqliteSelectNew( pNew->op = TK_SELECT; pNew->nLimit = nLimit; pNew->nOffset = nOffset; + pNew->iLimit = -1; + pNew->iOffset = -1; } return pNew; } @@ -277,62 +279,6 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ } /* -** This routine implements a minimal Oracle8 join syntax immulation. -** The precise oracle8 syntax is not implemented - it is easy enough -** to get this routine confused. But this routine does make it possible -** to write a single SQL statement that does a left outer join in both -** oracle8 and in SQLite. -** -** This routine looks for TK_COLUMN expression nodes that are marked -** with the EP_Oracle8Join property. Such nodes are generated by a -** column name (either "column" or "table.column") that is followed by -** the special "(+)" operator. If the table of the column marked with -** the (+) operator is the second are subsequent table in a join, then -** that table becomes the left table in a LEFT OUTER JOIN. The expression -** that uses that table becomes part of the ON clause for the join. -** -** It is important to enphasize that this is not exactly how oracle8 -** works. But it is close enough so that one can construct queries that -** will work correctly for both SQLite and Oracle8. -*/ -static int sqliteOracle8JoinFixup( - SrcList *pSrc, /* List of tables being joined */ - Expr *pWhere /* The WHERE clause of the SELECT statement */ -){ - int rc = 0; - if( ExprHasProperty(pWhere, EP_Oracle8Join) && pWhere->op==TK_COLUMN ){ - int idx; - for(idx=0; idx<pSrc->nSrc; idx++){ - if( pSrc->a[idx].iCursor==pWhere->iTable ) break; - } - assert( idx>=0 && idx<pSrc->nSrc ); - if( idx>0 ){ - pSrc->a[idx-1].jointype &= ~JT_INNER; - pSrc->a[idx-1].jointype |= JT_OUTER|JT_LEFT; - return 1; - } - } - if( pWhere->pRight ){ - rc = sqliteOracle8JoinFixup(pSrc, pWhere->pRight); - } - if( pWhere->pLeft ){ - rc |= sqliteOracle8JoinFixup(pSrc, pWhere->pLeft); - } - if( pWhere->pList ){ - int i; - ExprList *pList = pWhere->pList; - for(i=0; i<pList->nExpr && rc==0; i++){ - rc |= sqliteOracle8JoinFixup(pSrc, pList->a[i].pExpr); - } - } - if( rc==1 && (pWhere->op==TK_AND || pWhere->op==TK_EQ) ){ - setJoinExpr(pWhere); - rc = 0; - } - return rc; -} - -/* ** Delete the given Select structure and all of its substructures. */ void sqliteSelectDelete(Select *p){ @@ -450,13 +396,13 @@ static int selectInnerLoop( ** 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 +566,13 @@ static void generateSortTail( 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 +1191,52 @@ static void multiSelectSortOrder(Select *p, ExprList *pOrderBy){ } /* +** 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 +1271,8 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1281,11 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1304,15 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1325,11 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1366,15 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1390,7 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1410,7 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1434,14 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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 +1455,7 @@ static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){ 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); @@ -1578,6 +1597,9 @@ substExprList(ExprList *pList, int iTable, ExprList *pEList){ ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** +** (12) The subquery is not the right term of a LEFT OUTER JOIN or the +** subquery has no WHERE clause. (added by ticket #350) +** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. @@ -1637,6 +1659,24 @@ static int flattenSubquery( return 0; } + /* Restriction 12: If the subquery is the right operand of a left outer + ** join, make sure the subquery has no WHERE clause. + ** An examples of why this is not allowed: + ** + ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0) + ** + ** If we flatten the above, we would get + ** + ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0 + ** + ** But the t2.x>0 test will always fail on a NULL row of t2, which + ** effectively converts the OUTER JOIN into an INNER JOIN. + */ + if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 + && pSub->pWhere!=0 ){ + return 0; + } + /* If we reach this point, it means flattening is permitted for the ** iFrom-th entry of the FROM clause in the outer query. */ @@ -1831,13 +1871,13 @@ static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){ if( pIdx==0 ) return 0; } - /* Identify column names if we will be using the callback. This + /* Identify column types if we will be using the callback. This ** step is skipped if the output is going to a table or a memory cell. + ** The column names have already been generated in the calling function. */ v = sqliteGetVdbe(pParse); if( v==0 ) return 0; if( eDest==SRT_Callback ){ - generateColumnNames(pParse, p->pSrc, p->pEList); generateColumnTypes(pParse, p->pSrc, p->pEList); } @@ -1848,6 +1888,7 @@ static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){ */ 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); @@ -2028,7 +2069,6 @@ int sqliteSelect( if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto select_end; } - sqliteOracle8JoinFixup(pTabList, pWhere); } if( pHaving ){ if( pGroupBy==0 ){ @@ -2099,14 +2139,6 @@ int sqliteSelect( } } - /* Check for the special case of a min() or max() function by itself - ** in the result set. - */ - if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ - rc = 0; - goto select_end; - } - /* Begin generating code. */ v = sqliteGetVdbe(pParse); @@ -2119,24 +2151,12 @@ int sqliteSelect( generateColumnNames(pParse, pTabList, pEList); } - /* Set the limiter + /* Check for the special case of a min() or max() function by itself + ** in the result set. */ - 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( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ + rc = 0; + goto select_end; } /* Generate code for all sub-queries in the FROM clause @@ -2177,9 +2197,18 @@ int sqliteSelect( 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. + ** + ** We have to do this separately from the creation of column names + ** above because if the pTabList contains views then they will not + ** have been resolved and we will not know the column types until + ** now. */ if( eDest==SRT_Callback ){ generateColumnTypes(pParse, pTabList, pEList); |