diff options
Diffstat (limited to 'src/select.c')
| -rw-r--r-- | src/select.c | 1569 |
1 files changed, 1070 insertions, 499 deletions
diff --git a/src/select.c b/src/select.c index f3f1490..932874d 100644 --- a/src/select.c +++ b/src/select.c @@ -14,6 +14,34 @@ */ #include "sqliteInt.h" +/* +** An instance of the following object is used to record information about +** how to process the DISTINCT keyword, to simplify passing that information +** into the selectInnerLoop() routine. +*/ +typedef struct DistinctCtx DistinctCtx; +struct DistinctCtx { + u8 isTnct; /* True if the DISTINCT keyword is present */ + u8 eTnctType; /* One of the WHERE_DISTINCT_* operators */ + int tabTnct; /* Ephemeral table used for DISTINCT processing */ + int addrTnct; /* Address of OP_OpenEphemeral opcode for tabTnct */ +}; + +/* +** An instance of the following object is used to record information about +** the ORDER BY (or GROUP BY) clause of query is being coded. +*/ +typedef struct SortCtx SortCtx; +struct SortCtx { + ExprList *pOrderBy; /* The ORDER BY (or GROUP BY clause) */ + int nOBSat; /* Number of ORDER BY terms satisfied by indices */ + int iECursor; /* Cursor number for the sorter */ + int regReturn; /* Register holding block-output return address */ + int labelBkOut; /* Start label for the block-output subroutine */ + int addrSortIndex; /* Address of the OP_SorterOpen or OP_OpenEphemeral */ + u8 sortFlags; /* Zero or more SORTFLAG_* bits */ +}; +#define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */ /* ** Delete all the content of a Select structure but do not deallocate @@ -29,6 +57,7 @@ static void clearSelect(sqlite3 *db, Select *p){ sqlite3SelectDelete(db, p->pPrior); sqlite3ExprDelete(db, p->pLimit); sqlite3ExprDelete(db, p->pOffset); + sqlite3WithDelete(db, p->pWith); } /* @@ -86,7 +115,6 @@ Select *sqlite3SelectNew( assert( pOffset==0 || pLimit!=0 ); pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; - pNew->addrOpenEphm[2] = -1; if( db->mallocFailed ) { clearSelect(db, pNew); if( pNew!=&standin ) sqlite3DbFree(db, pNew); @@ -109,7 +137,15 @@ void sqlite3SelectDelete(sqlite3 *db, Select *p){ } /* -** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the +** Return a pointer to the right-most SELECT statement in a compound. +*/ +static Select *findRightmost(Select *p){ + while( p->pNext ) p = p->pNext; + return p; +} + +/* +** Given 1 to 3 identifiers preceding the JOIN keyword, determine the ** type of join. Return an integer constant that expresses that type ** in terms of the following bit values: ** @@ -264,8 +300,8 @@ static void addWhereTerm( pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2, 0); if( pEq && isOuterJoin ){ ExprSetProperty(pEq, EP_FromJoin); - assert( !ExprHasAnyProperty(pEq, EP_TokenOnly|EP_Reduced) ); - ExprSetIrreducible(pEq); + assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) ); + ExprSetVVAProperty(pEq, EP_NoReduce); pEq->iRightJoinTable = (i16)pE2->iTable; } *ppWhere = sqlite3ExprAnd(db, *ppWhere, pEq); @@ -300,8 +336,8 @@ static void addWhereTerm( static void setJoinExpr(Expr *p, int iTable){ while( p ){ ExprSetProperty(p, EP_FromJoin); - assert( !ExprHasAnyProperty(p, EP_TokenOnly|EP_Reduced) ); - ExprSetIrreducible(p); + assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) ); + ExprSetVVAProperty(p, EP_NoReduce); p->iRightJoinTable = (i16)iTable; setJoinExpr(p->pLeft, iTable); p = p->pRight; @@ -410,34 +446,73 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ return 0; } +/* Forward reference */ +static KeyInfo *keyInfoFromExprList( + Parse *pParse, /* Parsing context */ + ExprList *pList, /* Form the KeyInfo object from this ExprList */ + int iStart, /* Begin with this column of pList */ + int nExtra /* Add this many extra columns to the end */ +); + /* -** Insert code into "v" that will push the record on the top of the -** stack into the sorter. +** Insert code into "v" that will push the record in register regData +** into the sorter. */ static void pushOntoSorter( Parse *pParse, /* Parser context */ - ExprList *pOrderBy, /* The ORDER BY clause */ + SortCtx *pSort, /* Information about the ORDER BY clause */ Select *pSelect, /* The whole SELECT statement */ int regData /* Register holding data to be sorted */ ){ Vdbe *v = pParse->pVdbe; - int nExpr = pOrderBy->nExpr; - int regBase = sqlite3GetTempRange(pParse, nExpr+2); - int regRecord = sqlite3GetTempReg(pParse); + int nExpr = pSort->pOrderBy->nExpr; + int regRecord = ++pParse->nMem; + int regBase = pParse->nMem+1; + int nOBSat = pSort->nOBSat; int op; + + pParse->nMem += nExpr+2; /* nExpr+2 registers allocated at regBase */ sqlite3ExprCacheClear(pParse); - sqlite3ExprCodeExprList(pParse, pOrderBy, regBase, 0); - sqlite3VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr); + sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, 0); + sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr); sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+1, 1); - sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nExpr + 2, regRecord); - if( pSelect->selFlags & SF_UseSorter ){ + sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nExpr+2-nOBSat,regRecord); + if( nOBSat>0 ){ + int regPrevKey; /* The first nOBSat columns of the previous row */ + int addrFirst; /* Address of the OP_IfNot opcode */ + int addrJmp; /* Address of the OP_Jump opcode */ + VdbeOp *pOp; /* Opcode that opens the sorter */ + int nKey; /* Number of sorting key columns, including OP_Sequence */ + KeyInfo *pKI; /* Original KeyInfo on the sorter table */ + + regPrevKey = pParse->nMem+1; + pParse->nMem += pSort->nOBSat; + nKey = nExpr - pSort->nOBSat + 1; + addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); VdbeCoverage(v); + sqlite3VdbeAddOp3(v, OP_Compare, regPrevKey, regBase, pSort->nOBSat); + pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex); + if( pParse->db->mallocFailed ) return; + pOp->p2 = nKey + 1; + pKI = pOp->p4.pKeyInfo; + memset(pKI->aSortOrder, 0, pKI->nField); /* Makes OP_Jump below testable */ + sqlite3VdbeChangeP4(v, -1, (char*)pKI, P4_KEYINFO); + pOp->p4.pKeyInfo = keyInfoFromExprList(pParse, pSort->pOrderBy, nOBSat, 1); + addrJmp = sqlite3VdbeCurrentAddr(v); + sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v); + pSort->labelBkOut = sqlite3VdbeMakeLabel(v); + pSort->regReturn = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); + sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor); + sqlite3VdbeJumpHere(v, addrFirst); + sqlite3VdbeAddOp3(v, OP_Move, regBase, regPrevKey, pSort->nOBSat); + sqlite3VdbeJumpHere(v, addrJmp); + } + if( pSort->sortFlags & SORTFLAG_UseSorter ){ op = OP_SorterInsert; }else{ op = OP_IdxInsert; } - sqlite3VdbeAddOp2(v, op, pOrderBy->iECursor, regRecord); - sqlite3ReleaseTempReg(pParse, regRecord); - sqlite3ReleaseTempRange(pParse, regBase, nExpr+2); + sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord); if( pSelect->iLimit ){ int addr1, addr2; int iLimit; @@ -446,12 +521,12 @@ static void pushOntoSorter( }else{ iLimit = pSelect->iLimit; } - addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit); + addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1); addr2 = sqlite3VdbeAddOp0(v, OP_Goto); sqlite3VdbeJumpHere(v, addr1); - sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor); - sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor); + sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor); + sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor); sqlite3VdbeJumpHere(v, addr2); } } @@ -461,13 +536,12 @@ static void pushOntoSorter( */ static void codeOffset( Vdbe *v, /* Generate code into this VM */ - Select *p, /* The SELECT statement being coded */ + int iOffset, /* Register holding the offset counter */ int iContinue /* Jump here to skip the current record */ ){ - if( p->iOffset && iContinue!=0 ){ + if( iOffset>0 ){ int addr; - sqlite3VdbeAddOp2(v, OP_AddImm, p->iOffset, -1); - addr = sqlite3VdbeAddOp1(v, OP_IfNeg, p->iOffset); + addr = sqlite3VdbeAddOp3(v, OP_IfNeg, iOffset, 0, -1); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue); VdbeComment((v, "skip OFFSET records")); sqlite3VdbeJumpHere(v, addr); @@ -495,7 +569,7 @@ static void codeDistinct( v = pParse->pVdbe; r1 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, iMem, N); + sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, iMem, N); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, N, r1); sqlite3VdbeAddOp2(v, OP_IdxInsert, iTab, r1); sqlite3ReleaseTempReg(pParse, r1); @@ -526,34 +600,20 @@ static int checkForMultiColumnSelectError( #endif /* -** An instance of the following object is used to record information about -** how to process the DISTINCT keyword, to simplify passing that information -** into the selectInnerLoop() routine. -*/ -typedef struct DistinctCtx DistinctCtx; -struct DistinctCtx { - u8 isTnct; /* True if the DISTINCT keyword is present */ - u8 eTnctType; /* One of the WHERE_DISTINCT_* operators */ - int tabTnct; /* Ephemeral table used for DISTINCT processing */ - int addrTnct; /* Address of OP_OpenEphemeral opcode for tabTnct */ -}; - -/* ** This routine generates the code for the inside of the inner loop ** of a SELECT. ** -** If srcTab and nColumn are both zero, then the pEList expressions -** are evaluated in order to get the data for this row. If nColumn>0 -** then data is pulled from srcTab and pEList is used only to get the -** datatypes for each column. +** If srcTab is negative, then the pEList expressions +** are evaluated in order to get the data for this row. If srcTab is +** zero or more, then data is pulled from srcTab and pEList is used only +** to get number columns and the datatype for each column. */ static void selectInnerLoop( Parse *pParse, /* The parser context */ Select *p, /* The complete select statement being coded */ ExprList *pEList, /* List of values being extracted */ int srcTab, /* Pull data from this table */ - int nColumn, /* Number of columns in the source table */ - ExprList *pOrderBy, /* If not NULL, sort results using this key */ + SortCtx *pSort, /* If not NULL, info on how to process ORDER BY */ DistinctCtx *pDistinct, /* If not NULL, info on how to process DISTINCT */ SelectDest *pDest, /* How to dispose of the results */ int iContinue, /* Jump here to continue with next row */ @@ -568,48 +628,49 @@ static void selectInnerLoop( int nResultCol; /* Number of result columns */ assert( v ); - if( NEVER(v==0) ) return; assert( pEList!=0 ); hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP; - if( pOrderBy==0 && !hasDistinct ){ - codeOffset(v, p, iContinue); + if( pSort && pSort->pOrderBy==0 ) pSort = 0; + if( pSort==0 && !hasDistinct ){ + assert( iContinue!=0 ); + codeOffset(v, p->iOffset, iContinue); } /* Pull the requested columns. */ - if( nColumn>0 ){ - nResultCol = nColumn; - }else{ - nResultCol = pEList->nExpr; - } + nResultCol = pEList->nExpr; + if( pDest->iSdst==0 ){ pDest->iSdst = pParse->nMem+1; - pDest->nSdst = nResultCol; pParse->nMem += nResultCol; - }else{ - assert( pDest->nSdst==nResultCol ); + }else if( pDest->iSdst+nResultCol > pParse->nMem ){ + /* This is an error condition that can result, for example, when a SELECT + ** on the right-hand side of an INSERT contains more result columns than + ** there are columns in the table on the left. The error will be caught + ** and reported later. But we need to make sure enough memory is allocated + ** to avoid other spurious errors in the meantime. */ + pParse->nMem += nResultCol; } + pDest->nSdst = nResultCol; regResult = pDest->iSdst; - if( nColumn>0 ){ - for(i=0; i<nColumn; i++){ + if( srcTab>=0 ){ + for(i=0; i<nResultCol; i++){ sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i); + VdbeComment((v, "%s", pEList->a[i].zName)); } }else if( eDest!=SRT_Exists ){ /* If the destination is an EXISTS(...) expression, the actual ** values returned by the SELECT are not required. */ - sqlite3ExprCacheClear(pParse); - sqlite3ExprCodeExprList(pParse, pEList, regResult, eDest==SRT_Output); + sqlite3ExprCodeExprList(pParse, pEList, regResult, + (eDest==SRT_Output||eDest==SRT_Coroutine)?SQLITE_ECEL_DUP:0); } - nColumn = nResultCol; /* If the DISTINCT keyword was present on the SELECT statement ** and this row has been seen before, then do not make this row ** part of the result. */ if( hasDistinct ){ - assert( pEList!=0 ); - assert( pEList->nExpr==nColumn ); switch( pDistinct->eTnctType ){ case WHERE_DISTINCT_ORDERED: { VdbeOp *pOp; /* No longer required OpenEphemeral instr. */ @@ -618,7 +679,7 @@ static void selectInnerLoop( /* Allocate space for the previous row */ regPrev = pParse->nMem+1; - pParse->nMem += nColumn; + pParse->nMem += nResultCol; /* Change the OP_OpenEphemeral coded earlier to an OP_Null ** sets the MEM_Cleared bit on the first register of the @@ -632,19 +693,21 @@ static void selectInnerLoop( pOp->p1 = 1; pOp->p2 = regPrev; - iJump = sqlite3VdbeCurrentAddr(v) + nColumn; - for(i=0; i<nColumn; i++){ + iJump = sqlite3VdbeCurrentAddr(v) + nResultCol; + for(i=0; i<nResultCol; i++){ CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[i].pExpr); - if( i<nColumn-1 ){ + if( i<nResultCol-1 ){ sqlite3VdbeAddOp3(v, OP_Ne, regResult+i, iJump, regPrev+i); + VdbeCoverage(v); }else{ sqlite3VdbeAddOp3(v, OP_Eq, regResult+i, iContinue, regPrev+i); - } + VdbeCoverage(v); + } sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } - assert( sqlite3VdbeCurrentAddr(v)==iJump ); - sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nColumn-1); + assert( sqlite3VdbeCurrentAddr(v)==iJump || pParse->db->mallocFailed ); + sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nResultCol-1); break; } @@ -655,12 +718,12 @@ static void selectInnerLoop( default: { assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED ); - codeDistinct(pParse, pDistinct->tabTnct, iContinue, nColumn, regResult); + codeDistinct(pParse, pDistinct->tabTnct, iContinue, nResultCol, regResult); break; } } - if( pOrderBy==0 ){ - codeOffset(v, p, iContinue); + if( pSort==0 ){ + codeOffset(v, p->iOffset, iContinue); } } @@ -672,7 +735,7 @@ static void selectInnerLoop( case SRT_Union: { int r1; r1 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1); + sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); sqlite3ReleaseTempReg(pParse, r1); break; @@ -683,21 +746,36 @@ static void selectInnerLoop( ** the temporary table iParm. */ case SRT_Except: { - sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn); + sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nResultCol); break; } -#endif +#endif /* SQLITE_OMIT_COMPOUND_SELECT */ /* Store the result as data using a unique key. */ + case SRT_Fifo: + case SRT_DistFifo: case SRT_Table: case SRT_EphemTab: { int r1 = sqlite3GetTempReg(pParse); testcase( eDest==SRT_Table ); testcase( eDest==SRT_EphemTab ); - sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1); - if( pOrderBy ){ - pushOntoSorter(pParse, pOrderBy, p, r1); + sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1); +#ifndef SQLITE_OMIT_CTE + if( eDest==SRT_DistFifo ){ + /* If the destination is DistFifo, then cursor (iParm+1) is open + ** on an ephemeral index. If the current row is already present + ** in the index, do not write it to the output. If not, add the + ** current row to the index and proceed with writing it to the + ** output table as well. */ + int addr = sqlite3VdbeCurrentAddr(v) + 4; + sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1); + assert( pSort==0 ); + } +#endif + if( pSort ){ + pushOntoSorter(pParse, pSort, p, r1); }else{ int r2 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2); @@ -715,15 +793,15 @@ static void selectInnerLoop( ** item into the set table with bogus data. */ case SRT_Set: { - assert( nColumn==1 ); + assert( nResultCol==1 ); pDest->affSdst = sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst); - if( pOrderBy ){ + if( pSort ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ - pushOntoSorter(pParse, pOrderBy, p, regResult); + pushOntoSorter(pParse, pSort, p, regResult); }else{ int r1 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult,1,r1, &pDest->affSdst, 1); @@ -747,9 +825,9 @@ static void selectInnerLoop( ** of the scan loop. */ case SRT_Mem: { - assert( nColumn==1 ); - if( pOrderBy ){ - pushOntoSorter(pParse, pOrderBy, p, regResult); + assert( nResultCol==1 ); + if( pSort ){ + pushOntoSorter(pParse, pSort, p, regResult); }else{ sqlite3ExprCodeMove(pParse, regResult, iParm, 1); /* The LIMIT clause will jump out of the loop for us */ @@ -758,27 +836,72 @@ static void selectInnerLoop( } #endif /* #ifndef SQLITE_OMIT_SUBQUERY */ - /* Send the data to the callback function or to a subroutine. In the - ** case of a subroutine, the subroutine itself is responsible for - ** popping the data from the stack. - */ - case SRT_Coroutine: - case SRT_Output: { + case SRT_Coroutine: /* Send data to a co-routine */ + case SRT_Output: { /* Return the results */ testcase( eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); - if( pOrderBy ){ + if( pSort ){ int r1 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1); - pushOntoSorter(pParse, pOrderBy, p, r1); + sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1); + pushOntoSorter(pParse, pSort, p, r1); sqlite3ReleaseTempReg(pParse, r1); }else if( eDest==SRT_Coroutine ){ sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm); }else{ - sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nColumn); - sqlite3ExprCacheAffinityChange(pParse, regResult, nColumn); + sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nResultCol); + sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol); + } + break; + } + +#ifndef SQLITE_OMIT_CTE + /* Write the results into a priority queue that is order according to + ** pDest->pOrderBy (in pSO). pDest->iSDParm (in iParm) is the cursor for an + ** index with pSO->nExpr+2 columns. Build a key using pSO for the first + ** pSO->nExpr columns, then make sure all keys are unique by adding a + ** final OP_Sequence column. The last column is the record as a blob. + */ + case SRT_DistQueue: + case SRT_Queue: { + int nKey; + int r1, r2, r3; + int addrTest = 0; + ExprList *pSO; + pSO = pDest->pOrderBy; + assert( pSO ); + nKey = pSO->nExpr; + r1 = sqlite3GetTempReg(pParse); + r2 = sqlite3GetTempRange(pParse, nKey+2); + r3 = r2+nKey+1; + if( eDest==SRT_DistQueue ){ + /* If the destination is DistQueue, then cursor (iParm+1) is open + ** on a second ephemeral index that holds all values every previously + ** added to the queue. */ + addrTest = sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, 0, + regResult, nResultCol); + VdbeCoverage(v); } + sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r3); + if( eDest==SRT_DistQueue ){ + sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r3); + sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); + } + for(i=0; i<nKey; i++){ + sqlite3VdbeAddOp2(v, OP_SCopy, + regResult + pSO->a[i].u.x.iOrderByCol - 1, + r2+i); + } + sqlite3VdbeAddOp2(v, OP_Sequence, iParm, r2+nKey); + sqlite3VdbeAddOp3(v, OP_MakeRecord, r2, nKey+2, r1); + sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); + if( addrTest ) sqlite3VdbeJumpHere(v, addrTest); + sqlite3ReleaseTempReg(pParse, r1); + sqlite3ReleaseTempRange(pParse, r2, nKey+2); break; } +#endif /* SQLITE_OMIT_CTE */ + + #if !defined(SQLITE_OMIT_TRIGGER) /* Discard the results. This is used for SELECT statements inside @@ -797,12 +920,64 @@ static void selectInnerLoop( ** there is a sorter, in which case the sorter has already limited ** the output for us. */ - if( pOrderBy==0 && p->iLimit ){ - sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); + if( pSort==0 && p->iLimit ){ + sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v); + } +} + +/* +** Allocate a KeyInfo object sufficient for an index of N key columns and +** X extra columns. +*/ +KeyInfo *sqlite3KeyInfoAlloc(sqlite3 *db, int N, int X){ + KeyInfo *p = sqlite3DbMallocZero(0, + sizeof(KeyInfo) + (N+X)*(sizeof(CollSeq*)+1)); + if( p ){ + p->aSortOrder = (u8*)&p->aColl[N+X]; + p->nField = (u16)N; + p->nXField = (u16)X; + p->enc = ENC(db); + p->db = db; + p->nRef = 1; + }else{ + db->mallocFailed = 1; + } + return p; +} + +/* +** Deallocate a KeyInfo object +*/ +void sqlite3KeyInfoUnref(KeyInfo *p){ + if( p ){ + assert( p->nRef>0 ); + p->nRef--; + if( p->nRef==0 ) sqlite3DbFree(0, p); } } /* +** Make a new pointer to a KeyInfo object +*/ +KeyInfo *sqlite3KeyInfoRef(KeyInfo *p){ + if( p ){ + assert( p->nRef>0 ); + p->nRef++; + } + return p; +} + +#ifdef SQLITE_DEBUG +/* +** Return TRUE if a KeyInfo object can be change. The KeyInfo object +** can only be changed if this is just a single reference to the object. +** +** This routine is used only inside of assert() statements. +*/ +int sqlite3KeyInfoIsWriteable(KeyInfo *p){ return p->nRef==1; } +#endif /* SQLITE_DEBUG */ + +/* ** Given an expression list, generate a KeyInfo structure that records ** the collating sequence for each expression in that expression list. ** @@ -814,31 +989,30 @@ static void selectInnerLoop( ** ** Space to hold the KeyInfo structure is obtain from malloc. The calling ** function is responsible for seeing that this structure is eventually -** freed. Add the KeyInfo structure to the P4 field of an opcode using -** P4_KEYINFO_HANDOFF is the usual way of dealing with this. +** freed. */ -static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList){ - sqlite3 *db = pParse->db; +static KeyInfo *keyInfoFromExprList( + Parse *pParse, /* Parsing context */ + ExprList *pList, /* Form the KeyInfo object from this ExprList */ + int iStart, /* Begin with this column of pList */ + int nExtra /* Add this many extra columns to the end */ +){ int nExpr; KeyInfo *pInfo; struct ExprList_item *pItem; + sqlite3 *db = pParse->db; int i; nExpr = pList->nExpr; - pInfo = sqlite3DbMallocZero(db, sizeof(*pInfo) + nExpr*(sizeof(CollSeq*)+1) ); + pInfo = sqlite3KeyInfoAlloc(db, nExpr+nExtra-iStart, 1); if( pInfo ){ - pInfo->aSortOrder = (u8*)&pInfo->aColl[nExpr]; - pInfo->nField = (u16)nExpr; - pInfo->enc = ENC(db); - pInfo->db = db; - for(i=0, pItem=pList->a; i<nExpr; i++, pItem++){ + assert( sqlite3KeyInfoIsWriteable(pInfo) ); + for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){ CollSeq *pColl; pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr); - if( !pColl ){ - pColl = db->pDfltColl; - } - pInfo->aColl[i] = pColl; - pInfo->aSortOrder[i] = pItem->sortOrder; + if( !pColl ) pColl = db->pDfltColl; + pInfo->aColl[i-iStart] = pColl; + pInfo->aSortOrder[i-iStart] = pItem->sortOrder; } } return pInfo; @@ -940,24 +1114,31 @@ static void explainComposite( static void generateSortTail( Parse *pParse, /* Parsing context */ Select *p, /* The SELECT statement */ - Vdbe *v, /* Generate code into this VDBE */ + SortCtx *pSort, /* Information on the ORDER BY clause */ int nColumn, /* Number of columns of data */ SelectDest *pDest /* Write the sorted results here */ ){ + Vdbe *v = pParse->pVdbe; /* The prepared statement */ int addrBreak = sqlite3VdbeMakeLabel(v); /* Jump here to exit loop */ int addrContinue = sqlite3VdbeMakeLabel(v); /* Jump here for next cycle */ int addr; + int addrOnce = 0; int iTab; int pseudoTab = 0; - ExprList *pOrderBy = p->pOrderBy; - + ExprList *pOrderBy = pSort->pOrderBy; int eDest = pDest->eDest; int iParm = pDest->iSDParm; - int regRow; int regRowid; + int nKey; - iTab = pOrderBy->iECursor; + if( pSort->labelBkOut ){ + sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut); + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrBreak); + sqlite3VdbeResolveLabel(v, pSort->labelBkOut); + addrOnce = sqlite3CodeOnce(pParse); VdbeCoverage(v); + } + iTab = pSort->iECursor; regRow = sqlite3GetTempReg(pParse); if( eDest==SRT_Output || eDest==SRT_Coroutine ){ pseudoTab = pParse->nTab++; @@ -966,19 +1147,23 @@ static void generateSortTail( }else{ regRowid = sqlite3GetTempReg(pParse); } - if( p->selFlags & SF_UseSorter ){ + nKey = pOrderBy->nExpr - pSort->nOBSat; + if( pSort->sortFlags & SORTFLAG_UseSorter ){ int regSortOut = ++pParse->nMem; int ptab2 = pParse->nTab++; - sqlite3VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2); + sqlite3VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, nKey+2); + if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce); addr = 1 + sqlite3VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak); - codeOffset(v, p, addrContinue); + VdbeCoverage(v); + codeOffset(v, p->iOffset, addrContinue); sqlite3VdbeAddOp2(v, OP_SorterData, iTab, regSortOut); - sqlite3VdbeAddOp3(v, OP_Column, ptab2, pOrderBy->nExpr+1, regRow); + sqlite3VdbeAddOp3(v, OP_Column, ptab2, nKey+1, regRow); sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE); }else{ - addr = 1 + sqlite3VdbeAddOp2(v, OP_Sort, iTab, addrBreak); - codeOffset(v, p, addrContinue); - sqlite3VdbeAddOp3(v, OP_Column, iTab, pOrderBy->nExpr+1, regRow); + if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce); + addr = 1 + sqlite3VdbeAddOp2(v, OP_Sort, iTab, addrBreak); VdbeCoverage(v); + codeOffset(v, p->iOffset, addrContinue); + sqlite3VdbeAddOp3(v, OP_Column, iTab, nKey+1, regRow); } switch( eDest ){ case SRT_Table: @@ -1033,21 +1218,22 @@ static void generateSortTail( /* The bottom of the loop */ sqlite3VdbeResolveLabel(v, addrContinue); - if( p->selFlags & SF_UseSorter ){ - sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); + if( pSort->sortFlags & SORTFLAG_UseSorter ){ + sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); VdbeCoverage(v); }else{ - sqlite3VdbeAddOp2(v, OP_Next, iTab, addr); + sqlite3VdbeAddOp2(v, OP_Next, iTab, addr); VdbeCoverage(v); } + if( pSort->regReturn ) sqlite3VdbeAddOp1(v, OP_Return, pSort->regReturn); sqlite3VdbeResolveLabel(v, addrBreak); - if( eDest==SRT_Output || eDest==SRT_Coroutine ){ - sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0); - } } /* ** Return a pointer to a string containing the 'declaration type' of the ** expression pExpr. The string may be treated as static by the caller. ** +** Also try to estimate the size of the returned value and return that +** result in *pEstWidth. +** ** The declaration type is the exact datatype definition extracted from the ** original CREATE TABLE statement if the expression is a column. The ** declaration type for a ROWID field is INTEGER. Exactly when an expression @@ -1061,21 +1247,36 @@ static void generateSortTail( ** SELECT abc FROM (SELECT col AS abc FROM tbl); ** ** The declaration type for any expression other than a column is NULL. +** +** This routine has either 3 or 6 parameters depending on whether or not +** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used. */ -static const char *columnType( +#ifdef SQLITE_ENABLE_COLUMN_METADATA +# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F) +static const char *columnTypeImpl( NameContext *pNC, Expr *pExpr, - const char **pzOriginDb, - const char **pzOriginTab, - const char **pzOriginCol + const char **pzOrigDb, + const char **pzOrigTab, + const char **pzOrigCol, + u8 *pEstWidth ){ + char const *zOrigDb = 0; + char const *zOrigTab = 0; + char const *zOrigCol = 0; +#else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */ +# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,F) +static const char *columnTypeImpl( + NameContext *pNC, + Expr *pExpr, + u8 *pEstWidth +){ +#endif /* !defined(SQLITE_ENABLE_COLUMN_METADATA) */ char const *zType = 0; - char const *zOriginDb = 0; - char const *zOriginTab = 0; - char const *zOriginCol = 0; int j; - if( NEVER(pExpr==0) || pNC->pSrcList==0 ) return 0; + u8 estWidth = 1; + if( NEVER(pExpr==0) || pNC->pSrcList==0 ) return 0; switch( pExpr->op ){ case TK_AGG_COLUMN: case TK_COLUMN: { @@ -1136,25 +1337,35 @@ static const char *columnType( sNC.pSrcList = pS->pSrc; sNC.pNext = pNC; sNC.pParse = pNC->pParse; - zType = columnType(&sNC, p, &zOriginDb, &zOriginTab, &zOriginCol); + zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol, &estWidth); } - }else if( ALWAYS(pTab->pSchema) ){ + }else if( pTab->pSchema ){ /* A real table */ assert( !pS ); if( iCol<0 ) iCol = pTab->iPKey; assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) ); +#ifdef SQLITE_ENABLE_COLUMN_METADATA if( iCol<0 ){ zType = "INTEGER"; - zOriginCol = "rowid"; + zOrigCol = "rowid"; }else{ zType = pTab->aCol[iCol].zType; - zOriginCol = pTab->aCol[iCol].zName; + zOrigCol = pTab->aCol[iCol].zName; + estWidth = pTab->aCol[iCol].szEst; } - zOriginTab = pTab->zName; + zOrigTab = pTab->zName; if( pNC->pParse ){ int iDb = sqlite3SchemaToIndex(pNC->pParse->db, pTab->pSchema); - zOriginDb = pNC->pParse->db->aDb[iDb].zName; + zOrigDb = pNC->pParse->db->aDb[iDb].zName; } +#else + if( iCol<0 ){ + zType = "INTEGER"; + }else{ + zType = pTab->aCol[iCol].zType; + estWidth = pTab->aCol[iCol].szEst; + } +#endif } break; } @@ -1171,18 +1382,21 @@ static const char *columnType( sNC.pSrcList = pS->pSrc; sNC.pNext = pNC; sNC.pParse = pNC->pParse; - zType = columnType(&sNC, p, &zOriginDb, &zOriginTab, &zOriginCol); + zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &estWidth); break; } #endif } - - if( pzOriginDb ){ - assert( pzOriginTab && pzOriginCol ); - *pzOriginDb = zOriginDb; - *pzOriginTab = zOriginTab; - *pzOriginCol = zOriginCol; + +#ifdef SQLITE_ENABLE_COLUMN_METADATA + if( pzOrigDb ){ + assert( pzOrigTab && pzOrigCol ); + *pzOrigDb = zOrigDb; + *pzOrigTab = zOrigTab; + *pzOrigCol = zOrigCol; } +#endif + if( pEstWidth ) *pEstWidth = estWidth; return zType; } @@ -1208,7 +1422,7 @@ static void generateColumnTypes( const char *zOrigDb = 0; const char *zOrigTab = 0; const char *zOrigCol = 0; - zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol); + zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, 0); /* The vdbe must make its own copy of the column-type and other ** column specific strings, in case the schema is reset before this @@ -1218,11 +1432,11 @@ static void generateColumnTypes( sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, SQLITE_TRANSIENT); sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, SQLITE_TRANSIENT); #else - zType = columnType(&sNC, p, 0, 0, 0); + zType = columnType(&sNC, p, 0, 0, 0, 0); #endif sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT); } -#endif /* SQLITE_OMIT_DECLTYPE */ +#endif /* !defined(SQLITE_OMIT_DECLTYPE) */ } /* @@ -1286,8 +1500,9 @@ static void generateColumnNames( sqlite3VdbeSetColName(v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT); } }else{ - sqlite3VdbeSetColName(v, i, COLNAME_NAME, - sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC); + const char *z = pEList->a[i].zSpan; + z = z==0 ? sqlite3MPrintf(db, "column%d", i+1) : sqlite3DbStrDup(db, z); + sqlite3VdbeSetColName(v, i, COLNAME_NAME, z, SQLITE_DYNAMIC); } } generateColumnTypes(pParse, pTabList, pEList); @@ -1375,7 +1590,7 @@ static int selectColumnsFromExprList( char *zNewName; int k; for(k=nName-1; k>1 && sqlite3Isdigit(zName[k]); k--){} - if( zName[k]==':' ) nName = k; + if( k>=0 && zName[k]==':' ) nName = k; zName[nName] = 0; zNewName = sqlite3MPrintf(db, "%s:%d", zName, ++cnt); sqlite3DbFree(db, zName); @@ -1411,8 +1626,7 @@ static int selectColumnsFromExprList( */ static void selectAddColumnTypeAndCollation( Parse *pParse, /* Parsing contexts */ - int nCol, /* Number of columns */ - Column *aCol, /* List of columns */ + Table *pTab, /* Add column type information to this table */ Select *pSelect /* SELECT used to determine types and collations */ ){ sqlite3 *db = pParse->db; @@ -1422,17 +1636,19 @@ static void selectAddColumnTypeAndCollation( int i; Expr *p; struct ExprList_item *a; + u64 szAll = 0; assert( pSelect!=0 ); assert( (pSelect->selFlags & SF_Resolved)!=0 ); - assert( nCol==pSelect->pEList->nExpr || db->mallocFailed ); + assert( pTab->nCol==pSelect->pEList->nExpr || db->mallocFailed ); if( db->mallocFailed ) return; memset(&sNC, 0, sizeof(sNC)); sNC.pSrcList = pSelect->pSrc; a = pSelect->pEList->a; - for(i=0, pCol=aCol; i<nCol; i++, pCol++){ + for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){ p = a[i].pExpr; - pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p, 0, 0, 0)); + pCol->zType = sqlite3DbStrDup(db, columnType(&sNC, p,0,0,0, &pCol->szEst)); + szAll += pCol->szEst; pCol->affinity = sqlite3ExprAffinity(p); if( pCol->affinity==0 ) pCol->affinity = SQLITE_AFF_NONE; pColl = sqlite3ExprCollSeq(pParse, p); @@ -1440,6 +1656,7 @@ static void selectAddColumnTypeAndCollation( pCol->zColl = sqlite3DbStrDup(db, pColl->zName); } } + pTab->szTabRow = sqlite3LogEst(szAll*4); } /* @@ -1467,9 +1684,9 @@ Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){ assert( db->lookaside.bEnabled==0 ); pTab->nRef = 1; pTab->zName = 0; - pTab->nRowEst = 1000000; + pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol); - selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect); + selectAddColumnTypeAndCollation(pParse, pTab, pSelect); pTab->iPKey = -1; if( db->mallocFailed ){ sqlite3DeleteTable(db, pTab); @@ -1485,12 +1702,14 @@ Table *sqlite3ResultSetOfSelect(Parse *pParse, Select *pSelect){ Vdbe *sqlite3GetVdbe(Parse *pParse){ Vdbe *v = pParse->pVdbe; if( v==0 ){ - v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db); -#ifndef SQLITE_OMIT_TRACE - if( v ){ - sqlite3VdbeAddOp0(v, OP_Trace); + v = pParse->pVdbe = sqlite3VdbeCreate(pParse); + if( v ) sqlite3VdbeAddOp0(v, OP_Init); + if( pParse->pToplevel==0 + && OptimizationEnabled(pParse->db,SQLITE_FactorOutConst) + ){ + pParse->okConstFactor = 1; } -#endif + } return v; } @@ -1507,8 +1726,13 @@ Vdbe *sqlite3GetVdbe(Parse *pParse){ ** ** This routine changes the values of iLimit and iOffset only if ** a limit or offset is defined by pLimit and pOffset. iLimit and -** iOffset should have been preset to appropriate default values -** (usually but not always -1) prior to calling this routine. +** iOffset should have been preset to appropriate default values (zero) +** prior to calling this routine. +** +** The iOffset register (if it exists) is initialized to the value +** of the OFFSET. The iLimit register is initialized to LIMIT. Register +** iOffset+1 is initialized to LIMIT+OFFSET. +** ** Only if pLimit!=0 or pOffset!=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 @@ -1523,7 +1747,7 @@ static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){ /* ** "LIMIT -1" always shows all rows. There is some - ** contraversy about what the correct behavior should be. + ** controversy about what the correct behavior should be. ** The current implementation interprets "LIMIT 0" to mean ** no rows. */ @@ -1532,33 +1756,33 @@ static void computeLimitRegisters(Parse *pParse, Select *p, int iBreak){ if( p->pLimit ){ p->iLimit = iLimit = ++pParse->nMem; v = sqlite3GetVdbe(pParse); - if( NEVER(v==0) ) return; /* VDBE should have already been allocated */ + assert( v!=0 ); if( sqlite3ExprIsInteger(p->pLimit, &n) ){ sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit); VdbeComment((v, "LIMIT counter")); if( n==0 ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, iBreak); - }else{ - if( p->nSelectRow > (double)n ) p->nSelectRow = (double)n; + }else if( n>=0 && p->nSelectRow>(u64)n ){ + p->nSelectRow = n; } }else{ sqlite3ExprCode(pParse, p->pLimit, iLimit); - sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); + sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeCoverage(v); VdbeComment((v, "LIMIT counter")); - sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); + sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); VdbeCoverage(v); } if( p->pOffset ){ p->iOffset = iOffset = ++pParse->nMem; pParse->nMem++; /* Allocate an extra register for limit+offset */ sqlite3ExprCode(pParse, p->pOffset, iOffset); - sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset); + sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset); VdbeCoverage(v); VdbeComment((v, "OFFSET counter")); - addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iOffset); + addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iOffset); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Integer, 0, iOffset); sqlite3VdbeJumpHere(v, addr1); sqlite3VdbeAddOp3(v, OP_Add, iLimit, iOffset, iOffset+1); VdbeComment((v, "LIMIT+OFFSET")); - addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iLimit); + addr1 = sqlite3VdbeAddOp1(v, OP_IfPos, iLimit); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Integer, -1, iOffset+1); sqlite3VdbeJumpHere(v, addr1); } @@ -1587,9 +1811,210 @@ static CollSeq *multiSelectCollSeq(Parse *pParse, Select *p, int iCol){ } return pRet; } -#endif /* SQLITE_OMIT_COMPOUND_SELECT */ -/* Forward reference */ +/* +** The select statement passed as the second parameter is a compound SELECT +** with an ORDER BY clause. This function allocates and returns a KeyInfo +** structure suitable for implementing the ORDER BY. +** +** Space to hold the KeyInfo structure is obtained from malloc. The calling +** function is responsible for ensuring that this structure is eventually +** freed. +*/ +static KeyInfo *multiSelectOrderByKeyInfo(Parse *pParse, Select *p, int nExtra){ + ExprList *pOrderBy = p->pOrderBy; + int nOrderBy = p->pOrderBy->nExpr; + sqlite3 *db = pParse->db; + KeyInfo *pRet = sqlite3KeyInfoAlloc(db, nOrderBy+nExtra, 1); + if( pRet ){ + int i; + for(i=0; i<nOrderBy; i++){ + struct ExprList_item *pItem = &pOrderBy->a[i]; + Expr *pTerm = pItem->pExpr; + CollSeq *pColl; + + if( pTerm->flags & EP_Collate ){ + pColl = sqlite3ExprCollSeq(pParse, pTerm); + }else{ + pColl = multiSelectCollSeq(pParse, p, pItem->u.x.iOrderByCol-1); + if( pColl==0 ) pColl = db->pDfltColl; + pOrderBy->a[i].pExpr = + sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName); + } + assert( sqlite3KeyInfoIsWriteable(pRet) ); + pRet->aColl[i] = pColl; + pRet->aSortOrder[i] = pOrderBy->a[i].sortOrder; + } + } + + return pRet; +} + +#ifndef SQLITE_OMIT_CTE +/* +** This routine generates VDBE code to compute the content of a WITH RECURSIVE +** query of the form: +** +** <recursive-table> AS (<setup-query> UNION [ALL] <recursive-query>) +** \___________/ \_______________/ +** p->pPrior p +** +** +** There is exactly one reference to the recursive-table in the FROM clause +** of recursive-query, marked with the SrcList->a[].isRecursive flag. +** +** The setup-query runs once to generate an initial set of rows that go +** into a Queue table. Rows are extracted from the Queue table one by +** one. Each row extracted from Queue is output to pDest. Then the single +** extracted row (now in the iCurrent table) becomes the content of the +** recursive-table for a recursive-query run. The output of the recursive-query +** is added back into the Queue table. Then another row is extracted from Queue +** and the iteration continues until the Queue table is empty. +** +** If the compound query operator is UNION then no duplicate rows are ever +** inserted into the Queue table. The iDistinct table keeps a copy of all rows +** that have ever been inserted into Queue and causes duplicates to be +** discarded. If the operator is UNION ALL, then duplicates are allowed. +** +** If the query has an ORDER BY, then entries in the Queue table are kept in +** ORDER BY order and the first entry is extracted for each cycle. Without +** an ORDER BY, the Queue table is just a FIFO. +** +** If a LIMIT clause is provided, then the iteration stops after LIMIT rows +** have been output to pDest. A LIMIT of zero means to output no rows and a +** negative LIMIT means to output all rows. If there is also an OFFSET clause +** with a positive value, then the first OFFSET outputs are discarded rather +** than being sent to pDest. The LIMIT count does not begin until after OFFSET +** rows have been skipped. +*/ +static void generateWithRecursiveQuery( + Parse *pParse, /* Parsing context */ + Select *p, /* The recursive SELECT to be coded */ + SelectDest *pDest /* What to do with query results */ +){ + SrcList *pSrc = p->pSrc; /* The FROM clause of the recursive query */ + int nCol = p->pEList->nExpr; /* Number of columns in the recursive table */ + Vdbe *v = pParse->pVdbe; /* The prepared statement under construction */ + Select *pSetup = p->pPrior; /* The setup query */ + int addrTop; /* Top of the loop */ + int addrCont, addrBreak; /* CONTINUE and BREAK addresses */ + int iCurrent = 0; /* The Current table */ + int regCurrent; /* Register holding Current table */ + int iQueue; /* The Queue table */ + int iDistinct = 0; /* To ensure unique results if UNION */ + int eDest = SRT_Fifo; /* How to write to Queue */ + SelectDest destQueue; /* SelectDest targetting the Queue table */ + int i; /* Loop counter */ + int rc; /* Result code */ + ExprList *pOrderBy; /* The ORDER BY clause */ + Expr *pLimit, *pOffset; /* Saved LIMIT and OFFSET */ + int regLimit, regOffset; /* Registers used by LIMIT and OFFSET */ + + /* Obtain authorization to do a recursive query */ + if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ) return; + + /* Process the LIMIT and OFFSET clauses, if they exist */ + addrBreak = sqlite3VdbeMakeLabel(v); + computeLimitRegisters(pParse, p, addrBreak); + pLimit = p->pLimit; + pOffset = p->pOffset; + regLimit = p->iLimit; + regOffset = p->iOffset; + p->pLimit = p->pOffset = 0; + p->iLimit = p->iOffset = 0; + pOrderBy = p->pOrderBy; + + /* Locate the cursor number of the Current table */ + for(i=0; ALWAYS(i<pSrc->nSrc); i++){ + if( pSrc->a[i].isRecursive ){ + iCurrent = pSrc->a[i].iCursor; + break; + } + } + + /* Allocate cursors numbers for Queue and Distinct. The cursor number for + ** the Distinct table must be exactly one greater than Queue in order + ** for the SRT_DistFifo and SRT_DistQueue destinations to work. */ + iQueue = pParse->nTab++; + if( p->op==TK_UNION ){ + eDest = pOrderBy ? SRT_DistQueue : SRT_DistFifo; + iDistinct = pParse->nTab++; + }else{ + eDest = pOrderBy ? SRT_Queue : SRT_Fifo; + } + sqlite3SelectDestInit(&destQueue, eDest, iQueue); + + /* Allocate cursors for Current, Queue, and Distinct. */ + regCurrent = ++pParse->nMem; + sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol); + if( pOrderBy ){ + KeyInfo *pKeyInfo = multiSelectOrderByKeyInfo(pParse, p, 1); + sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0, + (char*)pKeyInfo, P4_KEYINFO); + destQueue.pOrderBy = pOrderBy; + }else{ + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol); + } + VdbeComment((v, "Queue table")); + if( iDistinct ){ + p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0); + p->selFlags |= SF_UsesEphemeral; + } + + /* Detach the ORDER BY clause from the compound SELECT */ + p->pOrderBy = 0; + + /* Store the results of the setup-query in Queue. */ + pSetup->pNext = 0; + rc = sqlite3Select(pParse, pSetup, &destQueue); + pSetup->pNext = p; + if( rc ) goto end_of_recursive_query; + + /* Find the next row in the Queue and output that row */ + addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak); VdbeCoverage(v); + + /* Transfer the next row in Queue over to Current */ + sqlite3VdbeAddOp1(v, OP_NullRow, iCurrent); /* To reset column cache */ + if( pOrderBy ){ + sqlite3VdbeAddOp3(v, OP_Column, iQueue, pOrderBy->nExpr+1, regCurrent); + }else{ + sqlite3VdbeAddOp2(v, OP_RowData, iQueue, regCurrent); + } + sqlite3VdbeAddOp1(v, OP_Delete, iQueue); + + /* Output the single row in Current */ + addrCont = sqlite3VdbeMakeLabel(v); + codeOffset(v, regOffset, addrCont); + selectInnerLoop(pParse, p, p->pEList, iCurrent, + 0, 0, pDest, addrCont, addrBreak); + if( regLimit ){ + sqlite3VdbeAddOp3(v, OP_IfZero, regLimit, addrBreak, -1); + VdbeCoverage(v); + } + sqlite3VdbeResolveLabel(v, addrCont); + + /* Execute the recursive SELECT taking the single row in Current as + ** the value for the recursive-table. Store the results in the Queue. + */ + p->pPrior = 0; + sqlite3Select(pParse, p, &destQueue); + assert( p->pPrior==0 ); + p->pPrior = pSetup; + + /* Keep running the loop until the Queue is empty */ + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop); + sqlite3VdbeResolveLabel(v, addrBreak); + +end_of_recursive_query: + sqlite3ExprListDelete(pParse->db, p->pOrderBy); + p->pOrderBy = pOrderBy; + p->pLimit = pLimit; + p->pOffset = pOffset; + return; +} +#endif /* SQLITE_OMIT_CTE */ + +/* Forward references */ static int multiSelectOrderBy( Parse *pParse, /* Parsing context */ Select *p, /* The right-most of SELECTs to be coded */ @@ -1597,7 +2022,6 @@ static int multiSelectOrderBy( ); -#ifndef SQLITE_OMIT_COMPOUND_SELECT /* ** This routine is called to process a compound query form from ** two or more separate queries using UNION, UNION ALL, EXCEPT, or @@ -1641,18 +2065,17 @@ static int multiSelect( Select *pDelete = 0; /* Chain of simple selects to delete */ sqlite3 *db; /* Database connection */ #ifndef SQLITE_OMIT_EXPLAIN - int iSub1; /* EQP id of left-hand query */ - int iSub2; /* EQP id of right-hand query */ + int iSub1 = 0; /* EQP id of left-hand query */ + int iSub2 = 0; /* EQP id of right-hand query */ #endif /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT. */ assert( p && p->pPrior ); /* Calling function guarantees this much */ + assert( (p->selFlags & SF_Recursive)==0 || p->op==TK_ALL || p->op==TK_UNION ); db = pParse->db; pPrior = p->pPrior; - assert( pPrior->pRightmost!=pPrior ); - assert( pPrior->pRightmost==p->pRightmost ); dest = *pDest; if( pPrior->pOrderBy ){ sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before", @@ -1694,11 +2117,17 @@ static int multiSelect( goto multi_select_end; } +#ifndef SQLITE_OMIT_CTE + if( p->selFlags & SF_Recursive ){ + generateWithRecursiveQuery(pParse, p, &dest); + }else +#endif + /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); - } + }else /* Generate code for the left and right SELECT statements. */ @@ -1722,7 +2151,7 @@ static int multiSelect( p->iLimit = pPrior->iLimit; p->iOffset = pPrior->iOffset; if( p->iLimit ){ - addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit); + addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit); VdbeCoverage(v); VdbeComment((v, "Jump ahead if LIMIT reached")); } explainSetInteger(iSub2, pParse->iNextSelectId); @@ -1733,9 +2162,9 @@ static int multiSelect( p->nSelectRow += pPrior->nSelectRow; if( pPrior->pLimit && sqlite3ExprIsInteger(pPrior->pLimit, &nLimit) - && p->nSelectRow > (double)nLimit + && nLimit>0 && p->nSelectRow > (u64)nLimit ){ - p->nSelectRow = (double)nLimit; + p->nSelectRow = nLimit; } if( addr ){ sqlite3VdbeJumpHere(v, addr); @@ -1754,12 +2183,10 @@ static int multiSelect( testcase( p->op==TK_EXCEPT ); testcase( p->op==TK_UNION ); priorOp = SRT_Union; - if( dest.eDest==priorOp && ALWAYS(!p->pLimit &&!p->pOffset) ){ + if( dest.eDest==priorOp ){ /* We can reuse a temporary table generated by a SELECT to our ** right. */ - assert( p->pRightmost!=p ); /* Can only happen for leftward elements - ** of a 3-way or more compound */ assert( p->pLimit==0 ); /* Not allowed on leftward elements */ assert( p->pOffset==0 ); /* Not allowed on leftward elements */ unionTab = dest.iSDParm; @@ -1772,7 +2199,7 @@ static int multiSelect( addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, unionTab, 0); assert( p->addrOpenEphm[0] == -1 ); p->addrOpenEphm[0] = addr; - p->pRightmost->selFlags |= SF_UsesEphemeral; + findRightmost(p)->selFlags |= SF_UsesEphemeral; assert( p->pEList ); } @@ -1831,12 +2258,12 @@ static int multiSelect( iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); - sqlite3VdbeAddOp2(v, OP_Rewind, unionTab, iBreak); + sqlite3VdbeAddOp2(v, OP_Rewind, unionTab, iBreak); VdbeCoverage(v); iStart = sqlite3VdbeCurrentAddr(v); - selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr, + selectInnerLoop(pParse, p, p->pEList, unionTab, 0, 0, &dest, iCont, iBreak); sqlite3VdbeResolveLabel(v, iCont); - sqlite3VdbeAddOp2(v, OP_Next, unionTab, iStart); + sqlite3VdbeAddOp2(v, OP_Next, unionTab, iStart); VdbeCoverage(v); sqlite3VdbeResolveLabel(v, iBreak); sqlite3VdbeAddOp2(v, OP_Close, unionTab, 0); } @@ -1861,7 +2288,7 @@ static int multiSelect( addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab1, 0); assert( p->addrOpenEphm[0] == -1 ); p->addrOpenEphm[0] = addr; - p->pRightmost->selFlags |= SF_UsesEphemeral; + findRightmost(p)->selFlags |= SF_UsesEphemeral; assert( p->pEList ); /* Code the SELECTs to our left into temporary table "tab1". @@ -1906,15 +2333,15 @@ static int multiSelect( iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); - sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); + sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); VdbeCoverage(v); r1 = sqlite3GetTempReg(pParse); iStart = sqlite3VdbeAddOp2(v, OP_RowKey, tab1, r1); - sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0); + sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0); VdbeCoverage(v); sqlite3ReleaseTempReg(pParse, r1); - selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr, + selectInnerLoop(pParse, p, p->pEList, tab1, 0, 0, &dest, iCont, iBreak); sqlite3VdbeResolveLabel(v, iCont); - sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart); + sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart); VdbeCoverage(v); sqlite3VdbeResolveLabel(v, iBreak); sqlite3VdbeAddOp2(v, OP_Close, tab2, 0); sqlite3VdbeAddOp2(v, OP_Close, tab1, 0); @@ -1940,25 +2367,19 @@ static int multiSelect( CollSeq **apColl; /* For looping through pKeyInfo->aColl[] */ int nCol; /* Number of columns in result set */ - assert( p->pRightmost==p ); + assert( p->pNext==0 ); nCol = p->pEList->nExpr; - pKeyInfo = sqlite3DbMallocZero(db, - sizeof(*pKeyInfo)+nCol*(sizeof(CollSeq*) + 1)); + pKeyInfo = sqlite3KeyInfoAlloc(db, nCol, 1); if( !pKeyInfo ){ rc = SQLITE_NOMEM; goto multi_select_end; } - - pKeyInfo->enc = ENC(db); - pKeyInfo->nField = (u16)nCol; - for(i=0, apColl=pKeyInfo->aColl; i<nCol; i++, apColl++){ *apColl = multiSelectCollSeq(pParse, p, i); if( 0==*apColl ){ *apColl = db->pDfltColl; } } - pKeyInfo->aSortOrder = (u8*)apColl; for(pLoop=p; pLoop; pLoop=pLoop->pPrior){ for(i=0; i<2; i++){ @@ -1970,11 +2391,12 @@ static int multiSelect( break; } sqlite3VdbeChangeP2(v, addr, nCol); - sqlite3VdbeChangeP4(v, addr, (char*)pKeyInfo, P4_KEYINFO); + sqlite3VdbeChangeP4(v, addr, (char*)sqlite3KeyInfoRef(pKeyInfo), + P4_KEYINFO); pLoop->addrOpenEphm[i] = -1; } } - sqlite3DbFree(db, pKeyInfo); + sqlite3KeyInfoUnref(pKeyInfo); } multi_select_end: @@ -2013,7 +2435,6 @@ static int generateOutputSubroutine( int regReturn, /* The return address register */ int regPrev, /* Previous result register. No uniqueness if 0 */ KeyInfo *pKeyInfo, /* For comparing with previous entry */ - int p4type, /* The p4 type for pKeyInfo */ int iBreak /* Jump here if we hit the LIMIT */ ){ Vdbe *v = pParse->pVdbe; @@ -2027,10 +2448,10 @@ static int generateOutputSubroutine( */ if( regPrev ){ int j1, j2; - j1 = sqlite3VdbeAddOp1(v, OP_IfNot, regPrev); + j1 = sqlite3VdbeAddOp1(v, OP_IfNot, regPrev); VdbeCoverage(v); j2 = sqlite3VdbeAddOp4(v, OP_Compare, pIn->iSdst, regPrev+1, pIn->nSdst, - (char*)pKeyInfo, p4type); - sqlite3VdbeAddOp3(v, OP_Jump, j2+2, iContinue, j2+2); + (char*)sqlite3KeyInfoRef(pKeyInfo), P4_KEYINFO); + sqlite3VdbeAddOp3(v, OP_Jump, j2+2, iContinue, j2+2); VdbeCoverage(v); sqlite3VdbeJumpHere(v, j1); sqlite3VdbeAddOp3(v, OP_Copy, pIn->iSdst, regPrev+1, pIn->nSdst-1); sqlite3VdbeAddOp2(v, OP_Integer, 1, regPrev); @@ -2039,7 +2460,7 @@ static int generateOutputSubroutine( /* Suppress the first OFFSET entries if there is an OFFSET clause */ - codeOffset(v, p, iContinue); + codeOffset(v, p->iOffset, iContinue); switch( pDest->eDest ){ /* Store the result as data using a unique key. @@ -2131,7 +2552,7 @@ static int generateOutputSubroutine( /* Jump to the end of the loop if the LIMIT is reached. */ if( p->iLimit ){ - sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); + sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v); } /* Generate the subroutine return @@ -2239,9 +2660,7 @@ static int multiSelectOrderBy( SelectDest destA; /* Destination for coroutine A */ SelectDest destB; /* Destination for coroutine B */ int regAddrA; /* Address register for select-A coroutine */ - int regEofA; /* Flag to indicate when select-A is complete */ int regAddrB; /* Address register for select-B coroutine */ - int regEofB; /* Flag to indicate when select-B is complete */ int addrSelectA; /* Address of the select-A coroutine */ int addrSelectB; /* Address of the select-B coroutine */ int regOutA; /* Address register for the output-A subroutine */ @@ -2249,6 +2668,7 @@ static int multiSelectOrderBy( int addrOutA; /* Address of the output-A subroutine */ int addrOutB = 0; /* Address of the output-B subroutine */ int addrEofA; /* Address of the select-A-exhausted subroutine */ + int addrEofA_noB; /* Alternate addrEofA if B is uninitialized */ int addrEofB; /* Address of the select-B-exhausted subroutine */ int addrAltB; /* Address of the A<B subroutine */ int addrAeqB; /* Address of the A==B subroutine */ @@ -2299,8 +2719,8 @@ static int multiSelectOrderBy( for(i=1; db->mallocFailed==0 && i<=p->pEList->nExpr; i++){ struct ExprList_item *pItem; for(j=0, pItem=pOrderBy->a; j<nOrderBy; j++, pItem++){ - assert( pItem->iOrderByCol>0 ); - if( pItem->iOrderByCol==i ) break; + assert( pItem->u.x.iOrderByCol>0 ); + if( pItem->u.x.iOrderByCol==i ) break; } if( j==nOrderBy ){ Expr *pNew = sqlite3Expr(db, TK_INTEGER, 0); @@ -2308,7 +2728,7 @@ static int multiSelectOrderBy( pNew->flags |= EP_IntValue; pNew->u.iValue = i; pOrderBy = sqlite3ExprListAppend(pParse, pOrderBy, pNew); - if( pOrderBy ) pOrderBy->a[nOrderBy++].iOrderByCol = (u16)i; + if( pOrderBy ) pOrderBy->a[nOrderBy++].u.x.iOrderByCol = (u16)i; } } } @@ -2324,30 +2744,11 @@ static int multiSelectOrderBy( if( aPermute ){ struct ExprList_item *pItem; for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){ - assert( pItem->iOrderByCol>0 && pItem->iOrderByCol<=p->pEList->nExpr ); - aPermute[i] = pItem->iOrderByCol - 1; - } - pKeyMerge = - sqlite3DbMallocRaw(db, sizeof(*pKeyMerge)+nOrderBy*(sizeof(CollSeq*)+1)); - if( pKeyMerge ){ - pKeyMerge->aSortOrder = (u8*)&pKeyMerge->aColl[nOrderBy]; - pKeyMerge->nField = (u16)nOrderBy; - pKeyMerge->enc = ENC(db); - for(i=0; i<nOrderBy; i++){ - CollSeq *pColl; - Expr *pTerm = pOrderBy->a[i].pExpr; - if( pTerm->flags & EP_Collate ){ - pColl = sqlite3ExprCollSeq(pParse, pTerm); - }else{ - pColl = multiSelectCollSeq(pParse, p, aPermute[i]); - if( pColl==0 ) pColl = db->pDfltColl; - pOrderBy->a[i].pExpr = - sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName); - } - pKeyMerge->aColl[i] = pColl; - pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder; - } + assert( pItem->u.x.iOrderByCol>0 + && pItem->u.x.iOrderByCol<=p->pEList->nExpr ); + aPermute[i] = pItem->u.x.iOrderByCol - 1; } + pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1); }else{ pKeyMerge = 0; } @@ -2369,12 +2770,9 @@ static int multiSelectOrderBy( regPrev = pParse->nMem+1; pParse->nMem += nExpr+1; sqlite3VdbeAddOp2(v, OP_Integer, 0, regPrev); - pKeyDup = sqlite3DbMallocZero(db, - sizeof(*pKeyDup) + nExpr*(sizeof(CollSeq*)+1) ); + pKeyDup = sqlite3KeyInfoAlloc(db, nExpr, 1); if( pKeyDup ){ - pKeyDup->aSortOrder = (u8*)&pKeyDup->aColl[nExpr]; - pKeyDup->nField = (u16)nExpr; - pKeyDup->enc = ENC(db); + assert( sqlite3KeyInfoIsWriteable(pKeyDup) ); for(i=0; i<nExpr; i++){ pKeyDup->aColl[i] = multiSelectCollSeq(pParse, p, i); pKeyDup->aSortOrder[i] = 0; @@ -2385,6 +2783,7 @@ static int multiSelectOrderBy( /* Separate the left and the right query from one another */ p->pPrior = 0; + pPrior->pNext = 0; sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER"); if( pPrior->pPrior==0 ){ sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER"); @@ -2407,37 +2806,30 @@ static int multiSelectOrderBy( p->pOffset = 0; regAddrA = ++pParse->nMem; - regEofA = ++pParse->nMem; regAddrB = ++pParse->nMem; - regEofB = ++pParse->nMem; regOutA = ++pParse->nMem; regOutB = ++pParse->nMem; sqlite3SelectDestInit(&destA, SRT_Coroutine, regAddrA); sqlite3SelectDestInit(&destB, SRT_Coroutine, regAddrB); - /* Jump past the various subroutines and coroutines to the main - ** merge loop - */ - j1 = sqlite3VdbeAddOp0(v, OP_Goto); - addrSelectA = sqlite3VdbeCurrentAddr(v); - - /* Generate a coroutine to evaluate the SELECT statement to the ** left of the compound operator - the "A" select. */ - VdbeNoopComment((v, "Begin coroutine for left SELECT")); + addrSelectA = sqlite3VdbeCurrentAddr(v) + 1; + j1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrA, 0, addrSelectA); + VdbeComment((v, "left SELECT")); pPrior->iLimit = regLimitA; explainSetInteger(iSub1, pParse->iNextSelectId); sqlite3Select(pParse, pPrior, &destA); - sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofA); - sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); - VdbeNoopComment((v, "End coroutine for left SELECT")); + sqlite3VdbeAddOp1(v, OP_EndCoroutine, regAddrA); + sqlite3VdbeJumpHere(v, j1); /* Generate a coroutine to evaluate the SELECT statement on ** the right - the "B" select */ - addrSelectB = sqlite3VdbeCurrentAddr(v); - VdbeNoopComment((v, "Begin coroutine for right SELECT")); + addrSelectB = sqlite3VdbeCurrentAddr(v) + 1; + j1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrB, 0, addrSelectB); + VdbeComment((v, "right SELECT")); savedLimit = p->iLimit; savedOffset = p->iOffset; p->iLimit = regLimitB; @@ -2446,9 +2838,7 @@ static int multiSelectOrderBy( sqlite3Select(pParse, p, &destB); p->iLimit = savedLimit; p->iOffset = savedOffset; - sqlite3VdbeAddOp2(v, OP_Integer, 1, regEofB); - sqlite3VdbeAddOp1(v, OP_Yield, regAddrB); - VdbeNoopComment((v, "End coroutine for right SELECT")); + sqlite3VdbeAddOp1(v, OP_EndCoroutine, regAddrB); /* Generate a subroutine that outputs the current row of the A ** select as the next output row of the compound select. @@ -2456,7 +2846,7 @@ static int multiSelectOrderBy( VdbeNoopComment((v, "Output routine for A")); addrOutA = generateOutputSubroutine(pParse, p, &destA, pDest, regOutA, - regPrev, pKeyDup, P4_KEYINFO_HANDOFF, labelEnd); + regPrev, pKeyDup, labelEnd); /* Generate a subroutine that outputs the current row of the B ** select as the next output row of the compound select. @@ -2465,19 +2855,20 @@ static int multiSelectOrderBy( VdbeNoopComment((v, "Output routine for B")); addrOutB = generateOutputSubroutine(pParse, p, &destB, pDest, regOutB, - regPrev, pKeyDup, P4_KEYINFO_STATIC, labelEnd); + regPrev, pKeyDup, labelEnd); } + sqlite3KeyInfoUnref(pKeyDup); /* Generate a subroutine to run when the results from select A ** are exhausted and only data in select B remains. */ - VdbeNoopComment((v, "eof-A subroutine")); if( op==TK_EXCEPT || op==TK_INTERSECT ){ - addrEofA = sqlite3VdbeAddOp2(v, OP_Goto, 0, labelEnd); + addrEofA_noB = addrEofA = labelEnd; }else{ - addrEofA = sqlite3VdbeAddOp2(v, OP_If, regEofB, labelEnd); - sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB); - sqlite3VdbeAddOp1(v, OP_Yield, regAddrB); + VdbeNoopComment((v, "eof-A subroutine")); + addrEofA = sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB); + addrEofA_noB = sqlite3VdbeAddOp2(v, OP_Yield, regAddrB, labelEnd); + VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofA); p->nSelectRow += pPrior->nSelectRow; } @@ -2490,9 +2881,8 @@ static int multiSelectOrderBy( if( p->nSelectRow > pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow; }else{ VdbeNoopComment((v, "eof-B subroutine")); - addrEofB = sqlite3VdbeAddOp2(v, OP_If, regEofA, labelEnd); - sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA); - sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); + addrEofB = sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrA, labelEnd); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrEofB); } @@ -2500,8 +2890,7 @@ static int multiSelectOrderBy( */ VdbeNoopComment((v, "A-lt-B subroutine")); addrAltB = sqlite3VdbeAddOp2(v, OP_Gosub, regOutA, addrOutA); - sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); - sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrA, addrEofA); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr); /* Generate code to handle the case of A==B @@ -2514,8 +2903,7 @@ static int multiSelectOrderBy( }else{ VdbeNoopComment((v, "A-eq-B subroutine")); addrAeqB = - sqlite3VdbeAddOp1(v, OP_Yield, regAddrA); - sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrA, addrEofA); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr); } @@ -2526,28 +2914,23 @@ static int multiSelectOrderBy( if( op==TK_ALL || op==TK_UNION ){ sqlite3VdbeAddOp2(v, OP_Gosub, regOutB, addrOutB); } - sqlite3VdbeAddOp1(v, OP_Yield, regAddrB); - sqlite3VdbeAddOp2(v, OP_If, regEofB, addrEofB); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrB, addrEofB); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, labelCmpr); /* This code runs once to initialize everything. */ sqlite3VdbeJumpHere(v, j1); - sqlite3VdbeAddOp2(v, OP_Integer, 0, regEofA); - sqlite3VdbeAddOp2(v, OP_Integer, 0, regEofB); - sqlite3VdbeAddOp2(v, OP_Gosub, regAddrA, addrSelectA); - sqlite3VdbeAddOp2(v, OP_Gosub, regAddrB, addrSelectB); - sqlite3VdbeAddOp2(v, OP_If, regEofA, addrEofA); - sqlite3VdbeAddOp2(v, OP_If, regEofB, addrEofB); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrA, addrEofA_noB); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Yield, regAddrB, addrEofB); VdbeCoverage(v); /* Implement the main merge loop */ sqlite3VdbeResolveLabel(v, labelCmpr); sqlite3VdbeAddOp4(v, OP_Permutation, 0, 0, 0, (char*)aPermute, P4_INTARRAY); sqlite3VdbeAddOp4(v, OP_Compare, destA.iSdst, destB.iSdst, nOrderBy, - (char*)pKeyMerge, P4_KEYINFO_HANDOFF); + (char*)pKeyMerge, P4_KEYINFO); sqlite3VdbeChangeP5(v, OPFLAG_PERMUTE); - sqlite3VdbeAddOp3(v, OP_Jump, addrAltB, addrAeqB, addrAgtB); + sqlite3VdbeAddOp3(v, OP_Jump, addrAltB, addrAeqB, addrAgtB); VdbeCoverage(v); /* Jump to the this point in order to terminate the query. */ @@ -2567,6 +2950,7 @@ static int multiSelectOrderBy( sqlite3SelectDelete(db, p->pPrior); } p->pPrior = pPrior; + pPrior->pNext = p; /*** TBD: Insert subroutine calls to close cursors on incomplete **** subqueries ****/ @@ -2772,6 +3156,14 @@ static void substSelect( ** (21) The subquery does not use LIMIT or the outer query is not ** DISTINCT. (See ticket [752e1646fc]). ** +** (22) The subquery is not a recursive CTE. +** +** (23) The parent is not a recursive CTE, or the sub-query is not a +** compound query. This restriction is because transforming the +** parent to a compound query confuses the code that handles +** recursive queries in multiSelect(). +** +** ** 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. @@ -2824,7 +3216,7 @@ static int flattenSubquery( ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ - if( p->pRightmost && pSub->pLimit ){ + if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){ return 0; /* Restriction (15) */ } if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */ @@ -2843,6 +3235,8 @@ static int flattenSubquery( if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){ return 0; /* Restriction (21) */ } + if( pSub->selFlags & SF_Recursive ) return 0; /* Restriction (22) */ + if( (p->selFlags & SF_Recursive) && pSub->pPrior ) return 0; /* (23) */ /* OBSOLETE COMMENT 1: ** Restriction 3: If the subquery is a join, make sure the subquery is @@ -2910,7 +3304,7 @@ static int flattenSubquery( if( p->pOrderBy ){ int ii; for(ii=0; ii<p->pOrderBy->nExpr; ii++){ - if( p->pOrderBy->a[ii].iOrderByCol==0 ) return 0; + if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0; } } } @@ -2973,14 +3367,14 @@ static int flattenSubquery( p->pOrderBy = pOrderBy; p->pSrc = pSrc; p->op = TK_ALL; - p->pRightmost = 0; if( pNew==0 ){ - pNew = pPrior; + p->pPrior = pPrior; }else{ pNew->pPrior = pPrior; - pNew->pRightmost = 0; + if( pPrior ) pPrior->pNext = pNew; + pNew->pNext = p; + p->pPrior = pNew; } - p->pPrior = pNew; if( db->mallocFailed ) return 1; } @@ -3229,7 +3623,7 @@ static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){ if( IsVirtual(pTab) ) return 0; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( NEVER(pAggInfo->nFunc==0) ) return 0; - if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0; + if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; if( pExpr->flags&EP_Distinct ) return 0; return pTab; @@ -3319,11 +3713,207 @@ static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){ pNew->pHaving = 0; pNew->pOrderBy = 0; p->pPrior = 0; + p->pNext = 0; + p->selFlags &= ~SF_Compound; + assert( pNew->pPrior!=0 ); + pNew->pPrior->pNext = pNew; pNew->pLimit = 0; pNew->pOffset = 0; return WRC_Continue; } +#ifndef SQLITE_OMIT_CTE +/* +** Argument pWith (which may be NULL) points to a linked list of nested +** WITH contexts, from inner to outermost. If the table identified by +** FROM clause element pItem is really a common-table-expression (CTE) +** then return a pointer to the CTE definition for that table. Otherwise +** return NULL. +** +** If a non-NULL value is returned, set *ppContext to point to the With +** object that the returned CTE belongs to. +*/ +static struct Cte *searchWith( + With *pWith, /* Current outermost WITH clause */ + struct SrcList_item *pItem, /* FROM clause element to resolve */ + With **ppContext /* OUT: WITH clause return value belongs to */ +){ + const char *zName; + if( pItem->zDatabase==0 && (zName = pItem->zName)!=0 ){ + With *p; + for(p=pWith; p; p=p->pOuter){ + int i; + for(i=0; i<p->nCte; i++){ + if( sqlite3StrICmp(zName, p->a[i].zName)==0 ){ + *ppContext = p; + return &p->a[i]; + } + } + } + } + return 0; +} + +/* The code generator maintains a stack of active WITH clauses +** with the inner-most WITH clause being at the top of the stack. +** +** This routine pushes the WITH clause passed as the second argument +** onto the top of the stack. If argument bFree is true, then this +** WITH clause will never be popped from the stack. In this case it +** should be freed along with the Parse object. In other cases, when +** bFree==0, the With object will be freed along with the SELECT +** statement with which it is associated. +*/ +void sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){ + assert( bFree==0 || pParse->pWith==0 ); + if( pWith ){ + pWith->pOuter = pParse->pWith; + pParse->pWith = pWith; + pParse->bFreeWith = bFree; + } +} + +/* +** This function checks if argument pFrom refers to a CTE declared by +** a WITH clause on the stack currently maintained by the parser. And, +** if currently processing a CTE expression, if it is a recursive +** reference to the current CTE. +** +** If pFrom falls into either of the two categories above, pFrom->pTab +** and other fields are populated accordingly. The caller should check +** (pFrom->pTab!=0) to determine whether or not a successful match +** was found. +** +** Whether or not a match is found, SQLITE_OK is returned if no error +** occurs. If an error does occur, an error message is stored in the +** parser and some error code other than SQLITE_OK returned. +*/ +static int withExpand( + Walker *pWalker, + struct SrcList_item *pFrom +){ + Parse *pParse = pWalker->pParse; + sqlite3 *db = pParse->db; + struct Cte *pCte; /* Matched CTE (or NULL if no match) */ + With *pWith; /* WITH clause that pCte belongs to */ + + assert( pFrom->pTab==0 ); + + pCte = searchWith(pParse->pWith, pFrom, &pWith); + if( pCte ){ + Table *pTab; + ExprList *pEList; + Select *pSel; + Select *pLeft; /* Left-most SELECT statement */ + int bMayRecursive; /* True if compound joined by UNION [ALL] */ + With *pSavedWith; /* Initial value of pParse->pWith */ + + /* If pCte->zErr is non-NULL at this point, then this is an illegal + ** recursive reference to CTE pCte. Leave an error in pParse and return + ** early. If pCte->zErr is NULL, then this is not a recursive reference. + ** In this case, proceed. */ + if( pCte->zErr ){ + sqlite3ErrorMsg(pParse, pCte->zErr, pCte->zName); + return SQLITE_ERROR; + } + + assert( pFrom->pTab==0 ); + pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); + if( pTab==0 ) return WRC_Abort; + pTab->nRef = 1; + pTab->zName = sqlite3DbStrDup(db, pCte->zName); + pTab->iPKey = -1; + pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); + pTab->tabFlags |= TF_Ephemeral; + pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0); + if( db->mallocFailed ) return SQLITE_NOMEM; + assert( pFrom->pSelect ); + + /* Check if this is a recursive CTE. */ + pSel = pFrom->pSelect; + bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION ); + if( bMayRecursive ){ + int i; + SrcList *pSrc = pFrom->pSelect->pSrc; + for(i=0; i<pSrc->nSrc; i++){ + struct SrcList_item *pItem = &pSrc->a[i]; + if( pItem->zDatabase==0 + && pItem->zName!=0 + && 0==sqlite3StrICmp(pItem->zName, pCte->zName) + ){ + pItem->pTab = pTab; + pItem->isRecursive = 1; + pTab->nRef++; + pSel->selFlags |= SF_Recursive; + } + } + } + + /* Only one recursive reference is permitted. */ + if( pTab->nRef>2 ){ + sqlite3ErrorMsg( + pParse, "multiple references to recursive table: %s", pCte->zName + ); + return SQLITE_ERROR; + } + assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 )); + + pCte->zErr = "circular reference: %s"; + pSavedWith = pParse->pWith; + pParse->pWith = pWith; + sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel); + + for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior); + pEList = pLeft->pEList; + if( pCte->pCols ){ + if( pEList->nExpr!=pCte->pCols->nExpr ){ + sqlite3ErrorMsg(pParse, "table %s has %d values for %d columns", + pCte->zName, pEList->nExpr, pCte->pCols->nExpr + ); + pParse->pWith = pSavedWith; + return SQLITE_ERROR; + } + pEList = pCte->pCols; + } + + selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol); + if( bMayRecursive ){ + if( pSel->selFlags & SF_Recursive ){ + pCte->zErr = "multiple recursive references: %s"; + }else{ + pCte->zErr = "recursive reference in a subquery: %s"; + } + sqlite3WalkSelect(pWalker, pSel); + } + pCte->zErr = 0; + pParse->pWith = pSavedWith; + } + + return SQLITE_OK; +} +#endif + +#ifndef SQLITE_OMIT_CTE +/* +** If the SELECT passed as the second argument has an associated WITH +** clause, pop it from the stack stored as part of the Parse object. +** +** This function is used as the xSelectCallback2() callback by +** sqlite3SelectExpand() when walking a SELECT tree to resolve table +** names and other FROM clause elements. +*/ +static void selectPopWith(Walker *pWalker, Select *p){ + Parse *pParse = pWalker->pParse; + With *pWith = findRightmost(p)->pWith; + if( pWith!=0 ){ + assert( pParse->pWith==pWith ); + pParse->pWith = pWith->pOuter; + } +} +#else +#define selectPopWith 0 +#endif + /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: @@ -3367,6 +3957,7 @@ static int selectExpander(Walker *pWalker, Select *p){ } pTabList = p->pSrc; pEList = p->pEList; + sqlite3WithPush(pParse, findRightmost(p)->pWith, 0); /* Make sure cursor numbers have been assigned to all entries in ** the FROM clause of the SELECT statement. @@ -3379,12 +3970,21 @@ static int selectExpander(Walker *pWalker, Select *p){ */ for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ Table *pTab; + assert( pFrom->isRecursive==0 || pFrom->pTab ); + if( pFrom->isRecursive ) continue; if( pFrom->pTab!=0 ){ /* This statement has already been prepared. There is no need ** to go further. */ assert( i==0 ); +#ifndef SQLITE_OMIT_CTE + selectPopWith(pWalker, p); +#endif return WRC_Prune; } +#ifndef SQLITE_OMIT_CTE + if( withExpand(pWalker, pFrom) ) return WRC_Abort; + if( pFrom->pTab ) {} else +#endif if( pFrom->zName==0 ){ #ifndef SQLITE_OMIT_SUBQUERY Select *pSel = pFrom->pSelect; @@ -3395,11 +3995,11 @@ static int selectExpander(Walker *pWalker, Select *p){ pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; - pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab); + pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab); while( pSel->pPrior ){ pSel = pSel->pPrior; } selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol); pTab->iPKey = -1; - pTab->nRowEst = 1000000; + pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); pTab->tabFlags |= TF_Ephemeral; #endif }else{ @@ -3640,11 +4240,14 @@ static int exprWalkNoop(Walker *NotUsed, Expr *NotUsed2){ static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){ Walker w; memset(&w, 0, sizeof(w)); - w.xSelectCallback = convertCompoundSelectToSubquery; w.xExprCallback = exprWalkNoop; w.pParse = pParse; - sqlite3WalkSelect(&w, pSelect); + if( pParse->hasCompound ){ + w.xSelectCallback = convertCompoundSelectToSubquery; + sqlite3WalkSelect(&w, pSelect); + } w.xSelectCallback = selectExpander; + w.xSelectCallback2 = selectPopWith; sqlite3WalkSelect(&w, pSelect); } @@ -3663,7 +4266,7 @@ static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){ ** at that point because identifiers had not yet been resolved. This ** routine is called after identifier resolution. */ -static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){ +static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){ Parse *pParse; int i; SrcList *pTabList; @@ -3679,13 +4282,13 @@ static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){ if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){ /* A sub-query in the FROM clause of a SELECT */ Select *pSel = pFrom->pSelect; - assert( pSel ); - while( pSel->pPrior ) pSel = pSel->pPrior; - selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSel); + if( pSel ){ + while( pSel->pPrior ) pSel = pSel->pPrior; + selectAddColumnTypeAndCollation(pParse, pTab, pSel); + } } } } - return WRC_Continue; } #endif @@ -3701,10 +4304,9 @@ static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){ #ifndef SQLITE_OMIT_SUBQUERY Walker w; memset(&w, 0, sizeof(w)); - w.xSelectCallback = selectAddSubqueryTypeInfo; + w.xSelectCallback2 = selectAddSubqueryTypeInfo; w.xExprCallback = exprWalkNoop; w.pParse = pParse; - w.bSelectDepthFirst = 1; sqlite3WalkSelect(&w, pSelect); #endif } @@ -3751,14 +4353,23 @@ static void resetAccumulator(Parse *pParse, AggInfo *pAggInfo){ Vdbe *v = pParse->pVdbe; int i; struct AggInfo_func *pFunc; - if( pAggInfo->nFunc+pAggInfo->nColumn==0 ){ - return; - } + int nReg = pAggInfo->nFunc + pAggInfo->nColumn; + if( nReg==0 ) return; +#ifdef SQLITE_DEBUG + /* Verify that all AggInfo registers are within the range specified by + ** AggInfo.mnReg..AggInfo.mxReg */ + assert( nReg==pAggInfo->mxReg-pAggInfo->mnReg+1 ); for(i=0; i<pAggInfo->nColumn; i++){ - sqlite3VdbeAddOp2(v, OP_Null, 0, pAggInfo->aCol[i].iMem); + assert( pAggInfo->aCol[i].iMem>=pAggInfo->mnReg + && pAggInfo->aCol[i].iMem<=pAggInfo->mxReg ); + } + for(i=0; i<pAggInfo->nFunc; i++){ + assert( pAggInfo->aFunc[i].iMem>=pAggInfo->mnReg + && pAggInfo->aFunc[i].iMem<=pAggInfo->mxReg ); } +#endif + sqlite3VdbeAddOp3(v, OP_Null, 0, pAggInfo->mnReg, pAggInfo->mxReg); for(pFunc=pAggInfo->aFunc, i=0; i<pAggInfo->nFunc; i++, pFunc++){ - sqlite3VdbeAddOp2(v, OP_Null, 0, pFunc->iMem); if( pFunc->iDistinct>=0 ){ Expr *pE = pFunc->pExpr; assert( !ExprHasProperty(pE, EP_xIsSelect) ); @@ -3767,9 +4378,9 @@ static void resetAccumulator(Parse *pParse, AggInfo *pAggInfo){ "argument"); pFunc->iDistinct = -1; }else{ - KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList); + KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0, 0); sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0, - (char*)pKeyInfo, P4_KEYINFO_HANDOFF); + (char*)pKeyInfo, P4_KEYINFO); } } } @@ -3804,7 +4415,6 @@ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ struct AggInfo_col *pC; pAggInfo->directMode = 1; - sqlite3ExprCacheClear(pParse); for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ int nArg; int addrNext = 0; @@ -3814,7 +4424,7 @@ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ if( pList ){ nArg = pList->nExpr; regAgg = sqlite3GetTempRange(pParse, nArg); - sqlite3ExprCodeExprList(pParse, pList, regAgg, 1); + sqlite3ExprCodeExprList(pParse, pList, regAgg, SQLITE_ECEL_DUP); }else{ nArg = 0; regAgg = 0; @@ -3824,7 +4434,7 @@ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ assert( nArg==1 ); codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); } - if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ + if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl = 0; struct ExprList_item *pItem; int j; @@ -3860,7 +4470,7 @@ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ ** values to an OP_Copy. */ if( regHit ){ - addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); + addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v); } sqlite3ExprCacheClear(pParse); for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ @@ -3884,11 +4494,11 @@ static void explainSimpleCount( Index *pIdx /* Index used to optimize scan, or NULL */ ){ if( pParse->explain==2 ){ - char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)", - pTab->zName, - pIdx ? "USING COVERING INDEX " : "", - pIdx ? pIdx->zName : "", - pTab->nRowEst + int bCover = (pIdx!=0 && (HasRowid(pTab) || !IsPrimaryKeyIndex(pIdx))); + char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s", + pTab->zName, + bCover ? " USING COVERING INDEX " : "", + bCover ? pIdx->zName : "" ); sqlite3VdbeAddOp4( pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC @@ -3902,50 +4512,8 @@ static void explainSimpleCount( /* ** Generate code for the SELECT statement given in the p argument. ** -** The results are distributed in various ways depending on the -** contents of the SelectDest structure pointed to by argument pDest -** as follows: -** -** pDest->eDest Result -** ------------ ------------------------------------------- -** SRT_Output Generate a row of output (using the OP_ResultRow -** opcode) for each row in the result set. -** -** SRT_Mem Only valid if the result is a single column. -** Store the first column of the first result row -** in register pDest->iSDParm then abandon the rest -** of the query. This destination implies "LIMIT 1". -** -** SRT_Set The result must be a single column. Store each -** row of result as the key in table pDest->iSDParm. -** Apply the affinity pDest->affSdst before storing -** results. Used to implement "IN (SELECT ...)". -** -** SRT_Union Store results as a key in a temporary table -** identified by pDest->iSDParm. -** -** SRT_Except Remove results from the temporary table pDest->iSDParm. -** -** SRT_Table Store results in temporary table pDest->iSDParm. -** This is like SRT_EphemTab except that the table -** is assumed to already be open. -** -** SRT_EphemTab Create an temporary table pDest->iSDParm and store -** the result there. The cursor is left open after -** returning. This is like SRT_Table except that -** this destination uses OP_OpenEphemeral to create -** the table first. -** -** SRT_Coroutine Generate a co-routine that returns a new row of -** results each time it is invoked. The entry point -** of the co-routine is stored in register pDest->iSDParm. -** -** SRT_Exists Store a 1 in memory cell pDest->iSDParm if the result -** set is not empty. -** -** SRT_Discard Throw the results away. This is used by SELECT -** statements within triggers whose only purpose is -** the side-effects of functions. +** The results are returned according to the SelectDest structure. +** See comments in sqliteInt.h for further information. ** ** This routine returns the number of errors. If any errors are ** encountered, then an appropriate error message is left in @@ -3966,12 +4534,11 @@ int sqlite3Select( ExprList *pEList; /* List of columns to extract. */ SrcList *pTabList; /* List of tables to select from */ Expr *pWhere; /* The WHERE clause. May be NULL */ - ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */ ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */ Expr *pHaving; /* The HAVING clause. May be NULL */ int rc = 1; /* Value to return from this function */ - int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */ DistinctCtx sDistinct; /* Info on how to code the DISTINCT keyword */ + SortCtx sSort; /* Info on how to code the ORDER BY clause */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ @@ -3988,9 +4555,15 @@ int sqlite3Select( if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); + assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistFifo ); + assert( p->pOrderBy==0 || pDest->eDest!=SRT_Fifo ); + assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistQueue ); + assert( p->pOrderBy==0 || pDest->eDest!=SRT_Queue ); if( IgnorableOrderby(pDest) ){ assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || - pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard); + pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard || + pDest->eDest==SRT_Queue || pDest->eDest==SRT_DistFifo || + pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo); /* If ORDER BY makes no difference in the output then neither does ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); @@ -3998,7 +4571,8 @@ int sqlite3Select( p->selFlags &= ~SF_Distinct; } sqlite3SelectPrep(pParse, p, 0); - pOrderBy = p->pOrderBy; + memset(&sSort, 0, sizeof(sSort)); + sSort.pOrderBy = p->pOrderBy; pTabList = p->pSrc; pEList = p->pEList; if( pParse->nErr || db->mallocFailed ){ @@ -4046,7 +4620,7 @@ int sqlite3Select( } /* Increment Parse.nHeight by the height of the largest expression - ** tree refered to by this, the parent select. The child select + ** tree referred to by this, the parent select. The child select ** may contain expression trees of at most ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit ** more conservative than necessary, but much easier than enforcing @@ -4062,42 +4636,24 @@ int sqlite3Select( p->selFlags |= SF_Aggregate; } i = -1; - }else if( pTabList->nSrc==1 && (p->selFlags & SF_Materialize)==0 - && OptimizationEnabled(db, SQLITE_SubqCoroutine) + }else if( pTabList->nSrc==1 + && OptimizationEnabled(db, SQLITE_SubqCoroutine) ){ /* Implement a co-routine that will return a single row of the result ** set on each invocation. */ - int addrTop; - int addrEof; + int addrTop = sqlite3VdbeCurrentAddr(v)+1; pItem->regReturn = ++pParse->nMem; - addrEof = ++pParse->nMem; - /* Before coding the OP_Goto to jump to the start of the main routine, - ** ensure that the jump to the verify-schema routine has already - ** been coded. Otherwise, the verify-schema would likely be coded as - ** part of the co-routine. If the main routine then accessed the - ** database before invoking the co-routine for the first time (for - ** example to initialize a LIMIT register from a sub-select), it would - ** be doing so without having verified the schema version and obtained - ** the required db locks. See ticket d6b36be38. */ - sqlite3CodeVerifySchema(pParse, -1); - sqlite3VdbeAddOp0(v, OP_Goto); - addrTop = sqlite3VdbeAddOp1(v, OP_OpenPseudo, pItem->iCursor); - sqlite3VdbeChangeP5(v, 1); - VdbeComment((v, "coroutine for %s", pItem->pTab->zName)); + sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); + VdbeComment((v, "%s", pItem->pTab->zName)); pItem->addrFillSub = addrTop; - sqlite3VdbeAddOp2(v, OP_Integer, 0, addrEof); - sqlite3VdbeChangeP5(v, 1); sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow; + pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); pItem->viaCoroutine = 1; - sqlite3VdbeChangeP2(v, addrTop, dest.iSdst); - sqlite3VdbeChangeP3(v, addrTop, dest.nSdst); - sqlite3VdbeAddOp2(v, OP_Integer, 1, addrEof); - sqlite3VdbeAddOp1(v, OP_Yield, pItem->regReturn); - VdbeComment((v, "end %s", pItem->pTab->zName)); + pItem->regResult = dest.iSdst; + sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn); sqlite3VdbeJumpHere(v, addrTop-1); sqlite3ClearTempRegCache(pParse); }else{ @@ -4113,17 +4669,19 @@ int sqlite3Select( pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; - VdbeNoopComment((v, "materialize %s", pItem->pTab->zName)); if( pItem->isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of ** a trigger, then we only need to compute the value of the subquery ** once. */ - onceAddr = sqlite3CodeOnce(pParse); + onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); + VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); + }else{ + VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow; + pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow); if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); VdbeComment((v, "end %s", pItem->pTab->zName)); @@ -4136,7 +4694,7 @@ int sqlite3Select( pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ - pOrderBy = p->pOrderBy; + sSort.pOrderBy = p->pOrderBy; } } pEList = p->pEList; @@ -4150,39 +4708,12 @@ int sqlite3Select( /* If there is are a sequence of queries, do the earlier ones first. */ if( p->pPrior ){ - if( p->pRightmost==0 ){ - Select *pLoop, *pRight = 0; - int cnt = 0; - int mxSelect; - for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){ - pLoop->pRightmost = p; - pLoop->pNext = pRight; - pRight = pLoop; - } - mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT]; - if( mxSelect && cnt>mxSelect ){ - sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); - goto select_end; - } - } rc = multiSelect(pParse, p, pDest); explainSetInteger(pParse->iSelectId, iRestoreSelectId); return rc; } #endif - /* If there is both a GROUP BY and an ORDER BY clause and they are - ** identical, then disable the ORDER BY clause since the GROUP BY - ** will cause elements to come out in the correct order. This is - ** an optimization - the correct answer should result regardless. - ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER - ** to disable this optimization for testing purposes. - */ - if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0 - && OptimizationEnabled(db, SQLITE_GroupByOrder) ){ - pOrderBy = 0; - } - /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and ** if the select-list is the same as the ORDER BY list, then this query ** can be rewritten as a GROUP BY. In other words, this: @@ -4199,12 +4730,12 @@ int sqlite3Select( ** BY and DISTINCT, and an index or separate temp-table for the other. */ if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct - && sqlite3ExprListCompare(pOrderBy, p->pEList)==0 + && sqlite3ExprListCompare(sSort.pOrderBy, p->pEList, -1)==0 ){ p->selFlags &= ~SF_Distinct; p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0); pGroupBy = p->pGroupBy; - pOrderBy = 0; + sSort.pOrderBy = 0; /* Notice that even thought SF_Distinct has been cleared from p->selFlags, ** the sDistinct.isTnct is still set. Hence, isTnct represents the ** original setting of the SF_Distinct flag, not the current setting */ @@ -4218,16 +4749,16 @@ int sqlite3Select( ** we figure out that the sorting index is not needed. The addrSortIndex ** variable is used to facilitate that change. */ - if( pOrderBy ){ + if( sSort.pOrderBy ){ KeyInfo *pKeyInfo; - pKeyInfo = keyInfoFromExprList(pParse, pOrderBy); - pOrderBy->iECursor = pParse->nTab++; - p->addrOpenEphm[2] = addrSortIndex = + pKeyInfo = keyInfoFromExprList(pParse, sSort.pOrderBy, 0, 0); + sSort.iECursor = pParse->nTab++; + sSort.addrSortIndex = sqlite3VdbeAddOp4(v, OP_OpenEphemeral, - pOrderBy->iECursor, pOrderBy->nExpr+2, 0, - (char*)pKeyInfo, P4_KEYINFO_HANDOFF); + sSort.iECursor, sSort.pOrderBy->nExpr+2, 0, + (char*)pKeyInfo, P4_KEYINFO); }else{ - addrSortIndex = -1; + sSort.addrSortIndex = -1; } /* If the output is destined for a temporary table, open that table. @@ -4239,11 +4770,11 @@ int sqlite3Select( /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); - p->nSelectRow = (double)LARGEST_INT64; + p->nSelectRow = LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); - if( p->iLimit==0 && addrSortIndex>=0 ){ - sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen; - p->selFlags |= SF_UseSorter; + if( p->iLimit==0 && sSort.addrSortIndex>=0 ){ + sqlite3VdbeGetOp(v, sSort.addrSortIndex)->opcode = OP_SorterOpen; + sSort.sortFlags |= SORTFLAG_UseSorter; } /* Open a virtual index to use for the distinct set. @@ -4252,8 +4783,8 @@ int sqlite3Select( sDistinct.tabTnct = pParse->nTab++; sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenEphemeral, sDistinct.tabTnct, 0, 0, - (char*)keyInfoFromExprList(pParse, p->pEList), - P4_KEYINFO_HANDOFF); + (char*)keyInfoFromExprList(pParse, p->pEList,0,0), + P4_KEYINFO); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED; }else{ @@ -4262,27 +4793,37 @@ int sqlite3Select( if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ - ExprList *pDist = (sDistinct.isTnct ? p->pEList : 0); + u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0); /* Begin the database scan. */ - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, pDist, 0,0); + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy, + p->pEList, wctrlFlags, 0); if( pWInfo==0 ) goto select_end; - if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut; - if( pWInfo->eDistinct ) sDistinct.eTnctType = pWInfo->eDistinct; - if( pOrderBy && pWInfo->nOBSat==pOrderBy->nExpr ) pOrderBy = 0; + if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){ + p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo); + } + if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){ + sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo); + } + if( sSort.pOrderBy ){ + sSort.nOBSat = sqlite3WhereIsOrdered(pWInfo); + if( sSort.nOBSat==sSort.pOrderBy->nExpr ){ + sSort.pOrderBy = 0; + } + } /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ - if( addrSortIndex>=0 && pOrderBy==0 ){ - sqlite3VdbeChangeToNoop(v, addrSortIndex); - p->addrOpenEphm[2] = -1; + if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){ + sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex); } /* Use the standard inner loop. */ - selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, &sDistinct, pDest, - pWInfo->iContinue, pWInfo->iBreak); + selectInnerLoop(pParse, p, pEList, -1, &sSort, &sDistinct, pDest, + sqlite3WhereContinueLabel(pWInfo), + sqlite3WhereBreakLabel(pWInfo)); /* End the database scan loop. */ @@ -4301,6 +4842,7 @@ int sqlite3Select( int addrEnd; /* End of processing for this SELECT */ int sortPTab = 0; /* Pseudotable used to decode sorting results */ int sortOut = 0; /* Output register from the sorter */ + int orderByGrp = 0; /* True if the GROUP BY and ORDER BY are the same */ /* Remove any and all aliases between the result set and the ** GROUP BY clause. @@ -4310,16 +4852,28 @@ int sqlite3Select( struct ExprList_item *pItem; /* For looping over expression in a list */ for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){ - pItem->iAlias = 0; + pItem->u.x.iAlias = 0; } for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){ - pItem->iAlias = 0; + pItem->u.x.iAlias = 0; } - if( p->nSelectRow>(double)100 ) p->nSelectRow = (double)100; + if( p->nSelectRow>100 ) p->nSelectRow = 100; }else{ - p->nSelectRow = (double)1; + p->nSelectRow = 1; } + + /* If there is both a GROUP BY and an ORDER BY clause and they are + ** identical, then it may be possible to disable the ORDER BY clause + ** on the grounds that the GROUP BY will cause elements to come out + ** in the correct order. It also may not - the GROUP BY may use a + ** database index that causes rows to be grouped together as required + ** but not actually sorted. Either way, record the fact that the + ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp + ** variable. */ + if( sqlite3ExprListCompare(pGroupBy, sSort.pOrderBy, -1)==0 ){ + orderByGrp = 1; + } /* Create a label to jump to when we want to abort the query */ addrEnd = sqlite3VdbeMakeLabel(v); @@ -4332,10 +4886,11 @@ int sqlite3Select( sNC.pParse = pParse; sNC.pSrcList = pTabList; sNC.pAggInfo = &sAggInfo; + sAggInfo.mnReg = pParse->nMem+1; sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0; sAggInfo.pGroupBy = pGroupBy; sqlite3ExprAnalyzeAggList(&sNC, pEList); - sqlite3ExprAnalyzeAggList(&sNC, pOrderBy); + sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy); if( pHaving ){ sqlite3ExprAnalyzeAggregates(&sNC, pHaving); } @@ -4346,6 +4901,7 @@ int sqlite3Select( sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList); sNC.ncFlags &= ~NC_InAggFunc; } + sAggInfo.mxReg = pParse->nMem; if( db->mallocFailed ) goto select_end; /* Processing for aggregates with GROUP BY is very different and @@ -4368,10 +4924,10 @@ int sqlite3Select( ** will be converted into a Noop. */ sAggInfo.sortingIdx = pParse->nTab++; - pKeyInfo = keyInfoFromExprList(pParse, pGroupBy); + pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0, 0); addrSortingIdx = sqlite3VdbeAddOp4(v, OP_SorterOpen, sAggInfo.sortingIdx, sAggInfo.nSortingColumn, - 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF); + 0, (char*)pKeyInfo, P4_KEYINFO); /* Initialize memory locations used by GROUP BY aggregate processing */ @@ -4397,9 +4953,11 @@ int sqlite3Select( ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, 0, 0); + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, + WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0 + ); if( pWInfo==0 ) goto select_end; - if( pWInfo->nOBSat==pGroupBy->nExpr ){ + if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenEphemeral table will be ** cancelled later because we still need to use the pKeyInfo @@ -4459,9 +5017,24 @@ int sqlite3Select( sortOut = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol); sqlite3VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd); - VdbeComment((v, "GROUP BY sort")); + VdbeComment((v, "GROUP BY sort")); VdbeCoverage(v); sAggInfo.useSortingIdx = 1; sqlite3ExprCacheClear(pParse); + + } + + /* If the index or temporary table used by the GROUP BY sort + ** will naturally deliver rows in the order required by the ORDER BY + ** clause, cancel the ephemeral table open coded earlier. + ** + ** This is an optimization - the correct answer should result regardless. + ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER to + ** disable this optimization for testing purposes. */ + if( orderByGrp && OptimizationEnabled(db, SQLITE_GroupByOrder) + && (groupBySort || sqlite3WhereIsSorted(pWInfo)) + ){ + sSort.pOrderBy = 0; + sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex); } /* Evaluate the current GROUP BY terms and store in b0, b1, b2... @@ -4484,9 +5057,9 @@ int sqlite3Select( } } sqlite3VdbeAddOp4(v, OP_Compare, iAMem, iBMem, pGroupBy->nExpr, - (char*)pKeyInfo, P4_KEYINFO); + (char*)sqlite3KeyInfoRef(pKeyInfo), P4_KEYINFO); j1 = sqlite3VdbeCurrentAddr(v); - sqlite3VdbeAddOp3(v, OP_Jump, j1+1, 0, j1+1); + sqlite3VdbeAddOp3(v, OP_Jump, j1+1, 0, j1+1); VdbeCoverage(v); /* Generate code that runs whenever the GROUP BY changes. ** Changes in the GROUP BY are detected by the previous code @@ -4500,7 +5073,7 @@ int sqlite3Select( sqlite3ExprCodeMove(pParse, iBMem, iAMem, pGroupBy->nExpr); sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow); VdbeComment((v, "output one row")); - sqlite3VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd); + sqlite3VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd); VdbeCoverage(v); VdbeComment((v, "check abort flag")); sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); VdbeComment((v, "reset accumulator")); @@ -4517,6 +5090,7 @@ int sqlite3Select( */ if( groupBySort ){ sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop); + VdbeCoverage(v); }else{ sqlite3WhereEnd(pWInfo); sqlite3VdbeChangeToNoop(v, addrSortingIdx); @@ -4544,12 +5118,12 @@ int sqlite3Select( sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); sqlite3VdbeResolveLabel(v, addrOutputRow); addrOutputRow = sqlite3VdbeCurrentAddr(v); - sqlite3VdbeAddOp2(v, OP_IfPos, iUseFlag, addrOutputRow+2); + sqlite3VdbeAddOp2(v, OP_IfPos, iUseFlag, addrOutputRow+2); VdbeCoverage(v); VdbeComment((v, "Groupby result generator entry point")); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); finalizeAggFunctions(pParse, &sAggInfo); sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, SQLITE_JUMPIFNULL); - selectInnerLoop(pParse, p, p->pEList, 0, 0, pOrderBy, + selectInnerLoop(pParse, p, p->pEList, -1, &sSort, &sDistinct, pDest, addrOutputRow+1, addrSetAbort); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); @@ -4590,33 +5164,34 @@ int sqlite3Select( sqlite3CodeVerifySchema(pParse, iDb); sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); - /* Search for the index that has the least amount of columns. If - ** there is such an index, and it has less columns than the table - ** does, then we can assume that it consumes less space on disk and - ** will therefore be cheaper to scan to determine the query result. - ** In this case set iRoot to the root page number of the index b-tree - ** and pKeyInfo to the KeyInfo structure required to navigate the - ** index. + /* Search for the index that has the lowest scan cost. ** ** (2011-04-15) Do not do a full scan of an unordered index. ** + ** (2013-10-03) Do not count the entries in a partial index. + ** ** In practice the KeyInfo structure will not be used. It is only ** passed to keep OP_OpenRead happy. */ + if( !HasRowid(pTab) ) pBest = sqlite3PrimaryKeyIndex(pTab); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ - if( pIdx->bUnordered==0 && (!pBest || pIdx->nColumn<pBest->nColumn) ){ + if( pIdx->bUnordered==0 + && pIdx->szIdxRow<pTab->szTabRow + && pIdx->pPartIdxWhere==0 + && (!pBest || pIdx->szIdxRow<pBest->szIdxRow) + ){ pBest = pIdx; } } - if( pBest && pBest->nColumn<pTab->nCol ){ + if( pBest ){ iRoot = pBest->tnum; - pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest); + pKeyInfo = sqlite3KeyInfoOfIndex(pParse, pBest); } /* Open a read-only cursor, execute the OP_Count, close the cursor. */ - sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb); + sqlite3VdbeAddOp4Int(v, OP_OpenRead, iCsr, iRoot, iDb, 1); if( pKeyInfo ){ - sqlite3VdbeChangeP4(v, -1, (char *)pKeyInfo, P4_KEYINFO_HANDOFF); + sqlite3VdbeChangeP4(v, -1, (char *)pKeyInfo, P4_KEYINFO); } sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem); sqlite3VdbeAddOp1(v, OP_Close, iCsr); @@ -4680,8 +5255,8 @@ int sqlite3Select( } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); - if( pWInfo->nOBSat>0 ){ - sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); + if( sqlite3WhereIsOrdered(pWInfo)>0 ){ + sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3WhereBreakLabel(pWInfo)); VdbeComment((v, "%s() by index", (flag==WHERE_ORDERBY_MIN?"min":"max"))); } @@ -4689,9 +5264,9 @@ int sqlite3Select( finalizeAggFunctions(pParse, &sAggInfo); } - pOrderBy = 0; + sSort.pOrderBy = 0; sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL); - selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, 0, + selectInnerLoop(pParse, p, p->pEList, -1, 0, 0, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } @@ -4706,9 +5281,9 @@ int sqlite3Select( /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ - if( pOrderBy ){ - explainTempTable(pParse, "ORDER BY"); - generateSortTail(pParse, p, v, pEList->nExpr, pDest); + if( sSort.pOrderBy ){ + explainTempTable(pParse, sSort.nOBSat>0 ? "RIGHT PART OF ORDER BY":"ORDER BY"); + generateSortTail(pParse, p, &sSort, pEList->nExpr, pDest); } /* Jump here to skip this query @@ -4816,10 +5391,6 @@ void sqlite3ExplainSelect(Vdbe *pVdbe, Select *p){ sqlite3ExplainPrintf(pVdbe, "(null-select)"); return; } - while( p->pPrior ){ - p->pPrior->pNext = p; - p = p->pPrior; - } sqlite3ExplainPush(pVdbe); while( p ){ explainOneSelect(pVdbe, p); |