Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -7422,10 +7422,19 @@ rc = clearDatabasePage(pBt, (Pgno)iTable, 0, pnChange); } sqlite3BtreeLeave(p); return rc; } + +/* +** Delete all information from the single table that pCur is open on. +** +** This routine only work for pCur on an ephemeral table. +*/ +int sqlite3BtreeClearTableOfCursor(BtCursor *pCur){ + return sqlite3BtreeClearTable(pCur->pBtree, pCur->pgnoRoot, 0); +} /* ** Erase all information in a table and add the root of the table to ** the freelist. Except, the root of the principle table (the one on ** page 1) is never added to the freelist. Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -113,10 +113,11 @@ #define BTREE_INTKEY 1 /* Table has only 64-bit signed integer keys */ #define BTREE_BLOBKEY 2 /* Table has keys only - no data */ int sqlite3BtreeDropTable(Btree*, int, int*); int sqlite3BtreeClearTable(Btree*, int, int*); +int sqlite3BtreeClearTableOfCursor(BtCursor*); void sqlite3BtreeTripAllCursors(Btree*, int); void sqlite3BtreeGetMeta(Btree *pBtree, int idx, u32 *pValue); int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -953,11 +953,10 @@ struct ExprList_item *pItem, *pOldItem; int i; if( p==0 ) return 0; pNew = sqlite3DbMallocRaw(db, sizeof(*pNew) ); if( pNew==0 ) return 0; - pNew->iECursor = 0; pNew->nExpr = i = p->nExpr; if( (flags & EXPRDUP_REDUCE)==0 ) for(i=1; inExpr; i+=i){} pNew->a = pItem = sqlite3DbMallocRaw(db, i*sizeof(p->a[0]) ); if( pItem==0 ){ sqlite3DbFree(db, pNew); @@ -1066,11 +1065,10 @@ pNew->iLimit = 0; pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; - pNew->addrOpenEphm[2] = -1; pNew->nSelectRow = p->nSelectRow; pNew->pWith = withDup(db, p->pWith); return pNew; } #else @@ -2338,11 +2336,11 @@ */ void sqlite3ExprCodeMove(Parse *pParse, int iFrom, int iTo, int nReg){ int i; struct yColCache *p; assert( iFrom>=iTo+nReg || iFrom+nReg<=iTo ); - sqlite3VdbeAddOp3(pParse->pVdbe, OP_Move, iFrom, iTo, nReg-1); + sqlite3VdbeAddOp3(pParse->pVdbe, OP_Move, iFrom, iTo, nReg); for(i=0, p=pParse->aColCache; iiReg; if( x>=iFrom && xiReg += iTo-iFrom; } @@ -2739,11 +2737,11 @@ pDef->funcFlags & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG); } } sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */ - sqlite3ExprCodeExprList(pParse, pFarg, r1, + sqlite3ExprCodeExprList(pParse, pFarg, r1, SQLITE_ECEL_DUP|SQLITE_ECEL_FACTOR); sqlite3ExprCachePop(pParse, 1); /* Ticket 2ea2425d34be */ }else{ r1 = 0; } Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1873,11 +1873,11 @@ sqlite3VdbeChangeP5(v, (u8)i); addr = sqlite3VdbeAddOp1(v, OP_IsNull, 2); VdbeCoverage(v); sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, sqlite3MPrintf(db, "*** in database %s ***\n", db->aDb[i].zName), P4_DYNAMIC); - sqlite3VdbeAddOp2(v, OP_Move, 2, 4); + sqlite3VdbeAddOp3(v, OP_Move, 2, 4, 1); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 2); sqlite3VdbeAddOp2(v, OP_ResultRow, 2, 1); sqlite3VdbeJumpHere(v, addr); /* Make sure all the indices are constructed correctly. Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -12,10 +12,38 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. */ #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 ** the select structure itself. */ @@ -85,11 +113,10 @@ pNew->pLimit = pLimit; pNew->pOffset = pOffset; 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); pNew = 0; }else{ @@ -417,38 +444,75 @@ } } 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 nExpr = pSort->pOrderBy->nExpr; int regBase = sqlite3GetTempRange(pParse, nExpr+2); int regRecord = sqlite3GetTempReg(pParse); + int nOBSat = pSort->nOBSat; int op; 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 */ + + 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); + pOp->p2 = nKey + 1; + sqlite3VdbeChangeP4(v, -1, (char*)pOp->p4.pKeyInfo, 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( nOBSat==0 ){ + sqlite3ReleaseTempReg(pParse, regRecord); + sqlite3ReleaseTempRange(pParse, regBase, nExpr+2); + } if( pSelect->iLimit ){ int addr1, addr2; int iLimit; if( pSelect->iOffset ){ iLimit = pSelect->iOffset+1; @@ -457,12 +521,12 @@ } 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); } } /* @@ -532,23 +596,10 @@ return 0; } } #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 is negative, then the pEList expressions @@ -559,11 +610,11 @@ 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 */ - 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 */ int iBreak /* Jump here to break out of the inner loop */ ){ @@ -576,11 +627,12 @@ int nResultCol; /* Number of result columns */ assert( v ); assert( pEList!=0 ); hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP; - if( pOrderBy==0 && !hasDistinct ){ + if( pSort && pSort->pOrderBy==0 ) pSort = 0; + if( pSort==0 && !hasDistinct ){ assert( iContinue!=0 ); codeOffset(v, p->iOffset, iContinue); } /* Pull the requested columns. @@ -667,11 +719,11 @@ assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED ); codeDistinct(pParse, pDistinct->tabTnct, iContinue, nResultCol, regResult); break; } } - if( pOrderBy==0 ){ + if( pSort==0 ){ codeOffset(v, p->iOffset, iContinue); } } switch( eDest ){ @@ -716,15 +768,15 @@ ** 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( pOrderBy==0 ); + assert( pSort==0 ); } #endif - if( pOrderBy ){ - pushOntoSorter(pParse, pOrderBy, p, r1); + if( pSort ){ + pushOntoSorter(pParse, pSort, p, r1); }else{ int r2 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2); sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); @@ -741,16 +793,16 @@ */ case SRT_Set: { 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); sqlite3ExprCacheAffinityChange(pParse, regResult, 1); sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); @@ -771,12 +823,12 @@ ** store the results in the appropriate memory cell and break out ** of the scan loop. */ case SRT_Mem: { assert( nResultCol==1 ); - if( pOrderBy ){ - pushOntoSorter(pParse, pOrderBy, p, regResult); + if( pSort ){ + pushOntoSorter(pParse, pSort, p, regResult); }else{ sqlite3ExprCodeMove(pParse, regResult, iParm, 1); /* The LIMIT clause will jump out of the loop for us */ } break; @@ -785,14 +837,14 @@ 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, nResultCol, r1); - pushOntoSorter(pParse, pOrderBy, p, 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, nResultCol); @@ -865,11 +917,11 @@ /* Jump to the end of the loop if the LIMIT is reached. Except, if ** there is a sorter, in which case the sorter has already limited ** the output for us. */ - if( pOrderBy==0 && p->iLimit ){ + if( pSort==0 && p->iLimit ){ sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v); } } /* @@ -936,27 +988,32 @@ ** ** Space to hold the KeyInfo structure is obtain from malloc. The calling ** function is responsible for seeing that this structure is eventually ** freed. */ -static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList, int nExtra){ +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 = sqlite3KeyInfoAlloc(db, nExpr+nExtra, 1); + pInfo = sqlite3KeyInfoAlloc(db, nExpr+nExtra-iStart, 1); if( pInfo ){ assert( sqlite3KeyInfoIsWriteable(pInfo) ); - for(i=0, pItem=pList->a; ia+iStart; ipExpr); if( !pColl ) pColl = db->pDfltColl; - pInfo->aColl[i] = pColl; - pInfo->aSortOrder[i] = pItem->sortOrder; + pInfo->aColl[i-iStart] = pColl; + pInfo->aSortOrder[i-iStart] = pItem->sortOrder; } } return pInfo; } @@ -1054,50 +1111,60 @@ ** routine generates the code needed to do that. */ 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++; sqlite3VdbeAddOp3(v, OP_OpenPseudo, pseudoTab, regRow, nColumn); regRowid = 0; }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); 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{ + 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, pOrderBy->nExpr+1, regRow); + sqlite3VdbeAddOp3(v, OP_Column, iTab, nKey+1, regRow); } switch( eDest ){ case SRT_Table: case SRT_EphemTab: { testcase( eDest==SRT_Table ); @@ -1148,15 +1215,16 @@ sqlite3ReleaseTempReg(pParse, regRowid); /* The bottom of the loop */ sqlite3VdbeResolveLabel(v, addrContinue); - if( p->selFlags & SF_UseSorter ){ + if( pSort->sortFlags & SORTFLAG_UseSorter ){ sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); VdbeCoverage(v); }else{ 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); } } @@ -4310,11 +4378,11 @@ if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){ sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one " "argument"); pFunc->iDistinct = -1; }else{ - KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0); + KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0, 0); sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0, (char*)pKeyInfo, P4_KEYINFO); } } } @@ -4465,16 +4533,15 @@ Vdbe *v; /* The virtual machine under construction */ int isAgg; /* True for select lists like "count(*)" */ 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 */ #ifndef SQLITE_OMIT_EXPLAIN @@ -4503,11 +4570,12 @@ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; 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 ){ goto select_end; } @@ -4625,11 +4693,11 @@ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ - pOrderBy = p->pOrderBy; + sSort.pOrderBy = p->pOrderBy; } } pEList = p->pEList; #endif pWhere = p->pWhere; @@ -4652,13 +4720,13 @@ ** 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, -1)==0 + if( sqlite3ExprListCompare(p->pGroupBy, sSort.pOrderBy, -1)==0 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){ - pOrderBy = 0; + sSort.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: @@ -4673,16 +4741,16 @@ ** used for both the ORDER BY and DISTINCT processing. As originally ** written the query must use a temp-table for at least one of the ORDER ** 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, -1)==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 */ assert( sDistinct.isTnct ); } @@ -4692,20 +4760,20 @@ ** extracted in pre-sorted order. If that is the case, then the ** OP_OpenEphemeral instruction will be changed to an OP_Noop once ** 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, 0); - 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, + 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. */ if( pDest->eDest==SRT_EphemTab ){ @@ -4715,22 +4783,22 @@ /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); 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. */ if( p->selFlags & SF_Distinct ){ sDistinct.tabTnct = pParse->nTab++; sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenEphemeral, sDistinct.tabTnct, 0, 0, - (char*)keyInfoFromExprList(pParse, p->pEList, 0), + (char*)keyInfoFromExprList(pParse, p->pEList,0,0), P4_KEYINFO); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED; }else{ sDistinct.eTnctType = WHERE_DISTINCT_NOOP; @@ -4739,32 +4807,36 @@ if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0); /* Begin the database scan. */ - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList, - wctrlFlags, 0); + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy, + p->pEList, wctrlFlags, 0); if( pWInfo==0 ) goto select_end; if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){ p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo); } if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){ sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo); } - if( pOrderBy && sqlite3WhereIsOrdered(pWInfo) ) pOrderBy = 0; + 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, -1, pOrderBy, &sDistinct, pDest, + selectInnerLoop(pParse, p, pEList, -1, &sSort, &sDistinct, pDest, sqlite3WhereContinueLabel(pWInfo), sqlite3WhereBreakLabel(pWInfo)); /* End the database scan loop. */ @@ -4816,11 +4888,11 @@ 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); } sAggInfo.nAccumulator = sAggInfo.nColumn; for(i=0; inTab++; - pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0); + pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0, 0); addrSortingIdx = sqlite3VdbeAddOp4(v, OP_SorterOpen, sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 0, (char*)pKeyInfo, P4_KEYINFO); /* Initialize memory locations used by GROUP BY aggregate processing @@ -4879,14 +4951,14 @@ ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, WHERE_GROUPBY, 0); if( pWInfo==0 ) goto select_end; - if( sqlite3WhereIsOrdered(pWInfo) ){ + 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 */ groupBySort = 0; @@ -5033,11 +5105,11 @@ 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, -1, pOrderBy, + selectInnerLoop(pParse, p, p->pEList, -1, &sSort, &sDistinct, pDest, addrOutputRow+1, addrSetAbort); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); VdbeComment((v, "end groupby result generator")); @@ -5165,20 +5237,20 @@ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); - if( sqlite3WhereIsOrdered(pWInfo) ){ + if( sqlite3WhereIsOrdered(pWInfo)>0 ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3WhereBreakLabel(pWInfo)); VdbeComment((v, "%s() by index", (flag==WHERE_ORDERBY_MIN?"min":"max"))); } sqlite3WhereEnd(pWInfo); finalizeAggFunctions(pParse, &sAggInfo); } - pOrderBy = 0; + sSort.pOrderBy = 0; sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL); selectInnerLoop(pParse, p, p->pEList, -1, 0, 0, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } @@ -5191,13 +5263,13 @@ } /* 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 */ sqlite3VdbeResolveLabel(v, iEnd); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1956,11 +1956,10 @@ ** of the result column in the form: DATABASE.TABLE.COLUMN. This later ** form is used for name resolution with nested FROM clauses. */ struct ExprList { int nExpr; /* Number of expressions on the list */ - int iECursor; /* VDBE Cursor associated with this ExprList */ struct ExprList_item { /* For each expression in the list */ Expr *pExpr; /* The list of expressions */ char *zName; /* Token associated with this expression */ char *zSpan; /* Original text of the expression */ u8 sortOrder; /* 1 for DESC or 0 for ASC */ @@ -2180,11 +2179,11 @@ struct Select { ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u16 selFlags; /* Various SF_* values */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ - int addrOpenEphm[3]; /* OP_OpenEphem opcodes related to this select */ + int addrOpenEphm[2]; /* OP_OpenEphem opcodes related to this select */ u64 nSelectRow; /* Estimated number of result rows */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ @@ -2204,13 +2203,13 @@ #define SF_Resolved 0x0002 /* Identifiers have been resolved */ #define SF_Aggregate 0x0004 /* Contains aggregate functions */ #define SF_UsesEphemeral 0x0008 /* Uses the OpenEphemeral opcode */ #define SF_Expanded 0x0010 /* sqlite3SelectExpand() called on this */ #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ -#define SF_UseSorter 0x0040 /* Sort using a sorter */ + /* 0x0040 NOT USED */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ -#define SF_Materialize 0x0100 /* NOT USED */ + /* 0x0100 NOT USED */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ #define SF_Compound 0x1000 /* Part of a compound query */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1078,14 +1078,15 @@ } /* Opcode: Move P1 P2 P3 * * ** Synopsis: r[P2@P3]=r[P1@P3] ** -** Move the values in register P1..P1+P3 over into -** registers P2..P2+P3. Registers P1..P1+P3 are +** Move the P3 values in register P1..P1+P3-1 over into +** registers P2..P2+P3-1. Registers P1..P1+P3-1 are ** left holding a NULL. It is an error for register ranges -** P1..P1+P3 and P2..P2+P3 to overlap. +** P1..P1+P3-1 and P2..P2+P3-1 to overlap. It is an error +** for P3 to be less than 1. */ case OP_Move: { char *zMalloc; /* Holding variable for allocated memory */ int n; /* Number of registers left to copy */ int p1; /* Register to copy from */ @@ -1092,11 +1093,11 @@ int p2; /* Register to copy to */ n = pOp->p3; p1 = pOp->p1; p2 = pOp->p2; - assert( n>=0 && p1>0 && p2>0 ); + assert( n>0 && p1>0 && p2>0 ); assert( p1+n<=p2 || p2+n<=p1 ); pIn1 = &aMem[p1]; pOut = &aMem[p2]; do{ @@ -1116,11 +1117,11 @@ pIn1->xDel = 0; pIn1->zMalloc = zMalloc; REGISTER_TRACE(p2++, pOut); pIn1++; pOut++; - }while( n-- ); + }while( --n ); break; } /* Opcode: Copy P1 P2 P3 * * ** Synopsis: r[P2@P3+1]=r[P1@P3+1] @@ -1993,10 +1994,11 @@ aPermute = pOp->p4.ai; break; } /* Opcode: Compare P1 P2 P3 P4 P5 +** Synopsis: r[P1@P3] <-> r[P2@P3] ** ** Compare two vectors of registers in reg(P1)..reg(P1+P3-1) (call this ** vector "A") and in reg(P2)..reg(P2+P3-1) ("B"). Save the result of ** the comparison for use by the next OP_Jump instruct. ** @@ -3328,10 +3330,11 @@ assert( pOp->p1>=0 ); assert( pOp->p2>=0 ); pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, 1); if( pCx==0 ) goto no_mem; pCx->nullRow = 1; + pCx->isEphemeral = 1; rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBt, BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags); if( rc==SQLITE_OK ){ rc = sqlite3BtreeBeginTrans(pCx->pBt, 1); } @@ -3818,11 +3821,11 @@ pC->seekResult = res; break; } /* Opcode: Sequence P1 P2 * * * -** Synopsis: r[P2]=rowid +** Synopsis: r[P2]=cursor[P1].ctr++ ** ** Find the next available sequence number for cursor P1. ** Write the sequence number into register P2. ** The sequence number on the cursor is incremented after this ** instruction. @@ -4866,10 +4869,33 @@ aMem[pOp->p3].u.i += nChange; } } break; } + +/* Opcode: ResetSorter P1 * * * * +** +** Delete all contents from the ephemeral table or sorter +** that is open on cursor P1. +** +** This opcode only works for cursors used for sorting and +** opened with OP_OpenEphemeral or OP_SorterOpen. +*/ +case OP_ResetSorter: { + VdbeCursor *pC; + + assert( pOp->p1>=0 && pOp->p1nCursor ); + pC = p->apCsr[pOp->p1]; + assert( pC!=0 ); + if( pC->pSorter ){ + sqlite3VdbeSorterReset(db, pC->pSorter); + }else{ + assert( pC->isEphemeral ); + rc = sqlite3BtreeClearTableOfCursor(pC->pCursor); + } + break; +} /* Opcode: CreateTable P1 P2 * * * ** Synopsis: r[P2]=root iDb=P1 ** ** Allocate a new table in the main database file if P1==0 or in the Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -70,10 +70,11 @@ u16 nHdrParsed; /* Number of header fields parsed so far */ i8 iDb; /* Index of cursor database in db->aDb[] (or -1) */ u8 nullRow; /* True if pointing to a row with no data */ u8 rowidIsValid; /* True if lastRowid is valid */ u8 deferredMoveto; /* A call to sqlite3BtreeMoveto() is needed */ + Bool isEphemeral:1; /* True for an ephemeral table */ Bool useRandomRowid:1;/* Generate new record numbers semi-randomly */ Bool isTable:1; /* True if a table requiring integer keys */ Bool isOrdered:1; /* True if the underlying table is BTREE_UNORDERED */ sqlite3_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ i64 seqCount; /* Sequence counter */ @@ -435,10 +436,11 @@ void sqlite3VdbeFrameDelete(VdbeFrame*); int sqlite3VdbeFrameRestore(VdbeFrame *); int sqlite3VdbeTransferError(Vdbe *p); int sqlite3VdbeSorterInit(sqlite3 *, VdbeCursor *); +void sqlite3VdbeSorterReset(sqlite3 *, VdbeSorter *); void sqlite3VdbeSorterClose(sqlite3 *, VdbeCursor *); int sqlite3VdbeSorterRowkey(const VdbeCursor *, Mem *); int sqlite3VdbeSorterNext(sqlite3 *, const VdbeCursor *, int *); int sqlite3VdbeSorterRewind(sqlite3 *, const VdbeCursor *, int *); int sqlite3VdbeSorterWrite(sqlite3 *, const VdbeCursor *, Mem *); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -781,11 +781,13 @@ assert( addrnOp ); if( addr<0 ){ addr = p->nOp - 1; } pOp = &p->aOp[addr]; - assert( pOp->p4type==P4_NOTUSED || pOp->p4type==P4_INT32 ); + assert( pOp->p4type==P4_NOTUSED + || pOp->p4type==P4_INT32 + || pOp->p4type==P4_KEYINFO ); freeP4(db, pOp->p4type, pOp->p4.p); pOp->p4.p = 0; if( n==P4_INT32 ){ /* Note: this cast is safe, because the origin data point was an int ** that was cast to a (const char *). */ Index: src/vdbesort.c ================================================================== --- src/vdbesort.c +++ src/vdbesort.c @@ -501,28 +501,45 @@ for(p=pRecord; p; p=pNext){ pNext = p->pNext; sqlite3DbFree(db, p); } } + +/* +** Reset a sorting cursor back to its original empty state. +*/ +void sqlite3VdbeSorterReset(sqlite3 *db, VdbeSorter *pSorter){ + if( pSorter->aIter ){ + int i; + for(i=0; inTree; i++){ + vdbeSorterIterZero(db, &pSorter->aIter[i]); + } + sqlite3DbFree(db, pSorter->aIter); + pSorter->aIter = 0; + } + if( pSorter->pTemp1 ){ + sqlite3OsCloseFree(pSorter->pTemp1); + pSorter->pTemp1 = 0; + } + vdbeSorterRecordFree(db, pSorter->pRecord); + pSorter->pRecord = 0; + pSorter->iWriteOff = 0; + pSorter->iReadOff = 0; + pSorter->nInMemory = 0; + pSorter->nTree = 0; + pSorter->nPMA = 0; + pSorter->aTree = 0; +} + /* ** Free any cursor components allocated by sqlite3VdbeSorterXXX routines. */ void sqlite3VdbeSorterClose(sqlite3 *db, VdbeCursor *pCsr){ VdbeSorter *pSorter = pCsr->pSorter; if( pSorter ){ - if( pSorter->aIter ){ - int i; - for(i=0; inTree; i++){ - vdbeSorterIterZero(db, &pSorter->aIter[i]); - } - sqlite3DbFree(db, pSorter->aIter); - } - if( pSorter->pTemp1 ){ - sqlite3OsCloseFree(pSorter->pTemp1); - } - vdbeSorterRecordFree(db, pSorter->pRecord); + sqlite3VdbeSorterReset(db, pSorter); sqlite3DbFree(db, pSorter->pUnpacked); sqlite3DbFree(db, pSorter); pCsr->pSorter = 0; } } Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -37,11 +37,11 @@ /* ** Return TRUE if the WHERE clause returns rows in ORDER BY order. ** Return FALSE if the output needs to be sorted. */ int sqlite3WhereIsOrdered(WhereInfo *pWInfo){ - return pWInfo->bOBSat!=0; + return pWInfo->nOBSat; } /* ** Return the VDBE address or label to jump to in order to continue ** immediately with the next row of a WHERE clause. @@ -3035,12 +3035,15 @@ ** a single iteration. This means that the first row returned ** should not have a NULL value stored in 'x'. If column 'x' is ** the first one after the nEq equality constraints in the index, ** this requires some special handling. */ + assert( pWInfo->pOrderBy==0 + || pWInfo->pOrderBy->nExpr==1 + || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 ); if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0 - && (pWInfo->bOBSat!=0) + && pWInfo->nOBSat>0 && (pIdx->nKeyCol>nEq) ){ assert( pLoop->u.btree.nSkip==0 ); bSeekPastNull = 1; nExtraReg = 1; @@ -4506,12 +4509,12 @@ assert( pNew->nLTerm<=pNew->nLSlot ); pNew->u.vtab.idxNum = pIdxInfo->idxNum; pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; pIdxInfo->needToFreeIdxStr = 0; pNew->u.vtab.idxStr = pIdxInfo->idxStr; - pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0) - && pIdxInfo->orderByConsumed); + pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ? + pIdxInfo->nOrderBy : 0); pNew->rSetup = 0; pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost); pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows); whereLoopInsert(pBuilder, pNew); if( pNew->u.vtab.needFree ){ @@ -4668,25 +4671,25 @@ } /* ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th ** parameters) to see if it outputs rows in the requested ORDER BY -** (or GROUP BY) without requiring a separate sort operation. Return: +** (or GROUP BY) without requiring a separate sort operation. Return N: ** -** 0: ORDER BY is not satisfied. Sorting required -** 1: ORDER BY is satisfied. Omit sorting -** -1: Unknown at this time +** N>0: N terms of the ORDER BY clause are satisfied +** N==0: No terms of the ORDER BY clause are satisfied +** N<0: Unknown yet how many terms of ORDER BY might be satisfied. ** ** Note that processing for WHERE_GROUPBY and WHERE_DISTINCTBY is not as ** strict. With GROUP BY and DISTINCT the only requirement is that ** equivalent rows appear immediately adjacent to one another. GROUP BY ** and DISTINT do not require rows to appear in any particular order as long ** as equivelent rows are grouped together. Thus for GROUP BY and DISTINCT ** the pOrderBy terms can be matched in any order. With ORDER BY, the ** pOrderBy terms must be matched in strict left-to-right order. */ -static int wherePathSatisfiesOrderBy( +static i8 wherePathSatisfiesOrderBy( WhereInfo *pWInfo, /* The WHERE clause */ ExprList *pOrderBy, /* ORDER BY or GROUP BY or DISTINCT clause to check */ WherePath *pPath, /* The WherePath to check */ u16 wctrlFlags, /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */ u16 nLoop, /* Number of entries in pPath->aLoop[] */ @@ -4915,12 +4918,18 @@ obSat |= MASKBIT(i); } } } } /* End the loop over all WhereLoops from outer-most down to inner-most */ - if( obSat==obDone ) return 1; - if( !isOrderDistinct ) return 0; + if( obSat==obDone ) return nOrderBy; + if( !isOrderDistinct ){ + for(i=nOrderBy-1; i>0; i--){ + Bitmask m = MASKBIT(i) - 1; + if( (obSat&m)==m ) return i; + } + return 0; + } return -1; } #ifdef WHERETRACE_ENABLED /* For debugging use only: */ @@ -4953,15 +4962,15 @@ Parse *pParse; /* Parsing context */ sqlite3 *db; /* The database connection */ int iLoop; /* Loop counter over the terms of the join */ int ii, jj; /* Loop counters */ int mxI = 0; /* Index of next entry to replace */ + int nOrderBy; /* Number of ORDER BY clause terms */ LogEst rCost; /* Cost of a path */ LogEst nOut; /* Number of outputs */ LogEst mxCost = 0; /* Maximum cost of a set of paths */ LogEst mxOut = 0; /* Maximum nOut value on the set of paths */ - LogEst rSortCost; /* Cost to do a sort */ int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */ WherePath *aFrom; /* All nFrom paths at the previous level */ WherePath *aTo; /* The nTo best paths at the current level */ WherePath *pFrom; /* An element of aFrom[] that we are working on */ WherePath *pTo; /* An element of aTo[] that we are working on */ @@ -4999,20 +5008,16 @@ aFrom[0].nRow = MIN(pParse->nQueryLoop, 46); assert( 46==sqlite3LogEst(25) ); nFrom = 1; /* Precompute the cost of sorting the final result set, if the caller ** to sqlite3WhereBegin() was concerned about sorting */ - rSortCost = 0; if( pWInfo->pOrderBy==0 || nRowEst==0 ){ - aFrom[0].isOrderedValid = 1; + aFrom[0].isOrdered = 0; + nOrderBy = 0; }else{ - /* TUNING: Estimated cost of sorting is 48*N*log2(N) where N is the - ** number of output rows. The 48 is the expected size of a row to sort. - ** FIXME: compute a better estimate of the 48 multiplier based on the - ** result set expressions. */ - rSortCost = nRowEst + estLog(nRowEst); - WHERETRACE(0x002,("---- sort cost=%-3d\n", rSortCost)); + aFrom[0].isOrdered = -1; + nOrderBy = pWInfo->pOrderBy->nExpr; } /* Compute successively longer WherePaths using the previous generation ** of WherePaths as the basis for the next. Keep track of the mxChoice ** best paths at each generation */ @@ -5020,43 +5025,48 @@ nTo = 0; for(ii=0, pFrom=aFrom; iipLoops; pWLoop; pWLoop=pWLoop->pNextLoop){ Bitmask maskNew; Bitmask revMask = 0; - u8 isOrderedValid = pFrom->isOrderedValid; - u8 isOrdered = pFrom->isOrdered; + i8 isOrdered = pFrom->isOrdered; if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue; if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ rCost = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); rCost = sqlite3LogEstAdd(rCost, pFrom->rCost); nOut = pFrom->nRow + pWLoop->nOut; maskNew = pFrom->maskLoop | pWLoop->maskSelf; - if( !isOrderedValid ){ - switch( wherePathSatisfiesOrderBy(pWInfo, + if( isOrdered<0 ){ + isOrdered = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags, - iLoop, pWLoop, &revMask) ){ - case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */ - isOrdered = 1; - isOrderedValid = 1; - break; - case 0: /* No. pFrom+pWLoop will require a separate sort */ - isOrdered = 0; - isOrderedValid = 1; - rCost = sqlite3LogEstAdd(rCost, rSortCost); - break; - default: /* Cannot tell yet. Try again on the next iteration */ - break; + iLoop, pWLoop, &revMask); + if( isOrdered>=0 && isOrderedwctrlFlags & WHERE_WANT_DISTINCT ){ + rSortCost += 16; + } + WHERETRACE(0x002, + ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n", + rSortCost, (nOrderBy-isOrdered), nOrderBy, rCost, + sqlite3LogEstAdd(rCost,rSortCost))); + rCost = sqlite3LogEstAdd(rCost, rSortCost); } }else{ revMask = pFrom->revLoop; } /* Check to see if pWLoop should be added to the mxChoice best so far */ for(jj=0, pTo=aTo; jjmaskLoop==maskNew - && pTo->isOrderedValid==isOrderedValid + && ((pTo->isOrdered^isOrdered)&80)==0 && ((pTo->rCost<=rCost && pTo->nRow<=nOut) || (pTo->rCost>=rCost && pTo->nRow>=nOut)) ){ testcase( jj==nTo-1 ); break; @@ -5066,11 +5076,11 @@ if( nTo>=mxChoice && rCost>=mxCost ){ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf("Skip %s cost=%-3d,%3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, - isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); + isOrdered>=0 ? isOrdered+'0' : '?'); } #endif continue; } /* Add a new Path to the aTo[] set */ @@ -5084,24 +5094,24 @@ pTo = &aTo[jj]; #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf("New %s cost=%-3d,%3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, - isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); + isOrdered>=0 ? isOrdered+'0' : '?'); } #endif }else{ if( pTo->rCost<=rCost && pTo->nRow<=nOut ){ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( "Skip %s cost=%-3d,%3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, - isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); + isOrdered>=0 ? isOrdered+'0' : '?'); sqlite3DebugPrintf(" vs %s cost=%-3d,%d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, - pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); + pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); } #endif testcase( pTo->rCost==rCost ); continue; } @@ -5110,23 +5120,22 @@ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( "Update %s cost=%-3d,%3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, - isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); + isOrdered>=0 ? isOrdered+'0' : '?'); sqlite3DebugPrintf(" was %s cost=%-3d,%3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, - pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); + pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); } #endif } /* pWLoop is a winner. Add it to the set of best so far */ pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf; pTo->revLoop = revMask; pTo->nRow = nOut; pTo->rCost = rCost; - pTo->isOrderedValid = isOrderedValid; pTo->isOrdered = isOrdered; memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop); pTo->aLoop[iLoop] = pWLoop; if( nTo>=mxChoice ){ mxI = 0; @@ -5147,12 +5156,12 @@ if( sqlite3WhereTrace>=2 ){ sqlite3DebugPrintf("---- after round %d ----\n", iLoop); for(ii=0, pTo=aTo; iirCost, pTo->nRow, - pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); - if( pTo->isOrderedValid && pTo->isOrdered ){ + pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?'); + if( pTo->isOrdered>0 ){ sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); }else{ sqlite3DebugPrintf("\n"); } } @@ -5191,17 +5200,22 @@ && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pResultSet, pFrom, WHERE_DISTINCTBY, nLoop-1, pFrom->aLoop[nLoop-1], ¬Used); - if( rc==1 ) pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; + if( rc==pWInfo->pResultSet->nExpr ){ + pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; + } } - if( pFrom->isOrdered ){ + if( pWInfo->pOrderBy ){ if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){ - pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; + if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){ + pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; + } }else{ - pWInfo->bOBSat = 1; + pWInfo->nOBSat = pFrom->isOrdered; + if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0; pWInfo->revMask = pFrom->revLoop; } } pWInfo->nRowOut = pFrom->nRow; @@ -5282,11 +5296,11 @@ pLoop->nOut = (LogEst)1; pWInfo->a[0].pWLoop = pLoop; pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur); pWInfo->a[0].iTabCur = iCur; pWInfo->nRowOut = 1; - if( pWInfo->pOrderBy ) pWInfo->bOBSat = 1; + if( pWInfo->pOrderBy ) pWInfo->nOBSat = pWInfo->pOrderBy->nExpr; if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } #ifdef SQLITE_DEBUG pLoop->cId = '0'; @@ -5386,11 +5400,11 @@ */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* FROM clause: A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ - ExprList *pOrderBy, /* An ORDER BY clause, or NULL */ + ExprList *pOrderBy, /* An ORDER BY (or GROUP BY) clause, or NULL */ ExprList *pResultSet, /* Result set of the query */ u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ ){ int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ @@ -5408,10 +5422,14 @@ /* Variable initialization */ db = pParse->db; memset(&sWLB, 0, sizeof(sWLB)); + + /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */ + testcase( pOrderBy && pOrderBy->nExpr==BMS-1 ); + if( pOrderBy && pOrderBy->nExpr>=BMS ) pOrderBy = 0; sWLB.pOrderBy = pOrderBy; /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ if( OptimizationDisabled(db, SQLITE_DistinctOpt) ){ @@ -5486,11 +5504,11 @@ } /* Special case: No FROM clause */ if( nTabList==0 ){ - if( pOrderBy ) pWInfo->bOBSat = 1; + if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr; if( wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } } @@ -5597,12 +5615,12 @@ } #ifdef WHERETRACE_ENABLED /* !=0 */ if( sqlite3WhereTrace ){ int ii; sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut); - if( pWInfo->bOBSat ){ - sqlite3DebugPrintf(" ORDERBY=0x%llx", pWInfo->revMask); + if( pWInfo->nOBSat>0 ){ + sqlite3DebugPrintf(" ORDERBY=%d,0x%llx", pWInfo->nOBSat, pWInfo->revMask); } switch( pWInfo->eDistinct ){ case WHERE_DISTINCT_UNIQUE: { sqlite3DebugPrintf(" DISTINCT=unique"); break; Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -119,11 +119,11 @@ Index *pIndex; /* Index used, or NULL */ } btree; struct { /* Information for virtual tables */ int idxNum; /* Index number */ u8 needFree; /* True if sqlite3_free(idxStr) is needed */ - u8 isOrdered; /* True if satisfies ORDER BY */ + i8 isOrdered; /* True if satisfies ORDER BY */ u16 omitMask; /* Terms that may be omitted */ char *idxStr; /* Index identifier string */ } vtab; } u; u32 wsFlags; /* WHERE_* flags describing the plan */ @@ -181,12 +181,11 @@ struct WherePath { Bitmask maskLoop; /* Bitmask of all WhereLoop objects in this path */ Bitmask revLoop; /* aLoop[]s that should be reversed for ORDER BY */ LogEst nRow; /* Estimated number of rows generated by this path */ LogEst rCost; /* Total cost of this path */ - u8 isOrdered; /* True if this path satisfies ORDER BY */ - u8 isOrderedValid; /* True if the isOrdered field is valid */ + i8 isOrdered; /* No. of ORDER BY terms satisfied. -1 for unknown */ WhereLoop **aLoop; /* Array of WhereLoop objects implementing this path */ }; /* ** The query generator uses an array of instances of this structure to @@ -396,11 +395,11 @@ ExprList *pResultSet; /* Result set. DISTINCT operates on these */ WhereLoop *pLoops; /* List of all WhereLoop objects */ Bitmask revMask; /* Mask of ORDER BY terms that need reversing */ LogEst nRowOut; /* Estimated number of output rows */ u16 wctrlFlags; /* Flags originally passed to sqlite3WhereBegin() */ - u8 bOBSat; /* ORDER BY satisfied by indices */ + i8 nOBSat; /* Number of ORDER BY terms satisfied by indices */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ int iTop; /* The very beginning of the WHERE loop */ Index: test/distinct.test ================================================================== --- test/distinct.test +++ test/distinct.test @@ -160,11 +160,11 @@ } foreach {tn sql temptables res} { 1 "a, b FROM t1" {} {A B a b} 2 "b, a FROM t1" {} {B A b a} - 3 "a, b, c FROM t1" {hash} {a b c A B C} + 3 "a, b, c FROM t1" {hash} {A B C a b c} 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c} 5 "b FROM t1 WHERE a = 'a'" {} {b} 6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b} 7 "a FROM t1" {} {A a} 8 "b COLLATE nocase FROM t1" {} {b} Index: test/orderby5.test ================================================================== --- test/orderby5.test +++ test/orderby5.test @@ -62,14 +62,32 @@ } {~/B-TREE/} do_execsql_test 1.7 { EXPLAIN QUERY PLAN SELECT DISTINCT c, b, a FROM t1 WHERE +a=0; } {/B-TREE/} -do_execsql_test 2.1 { + +# In some cases, it is faster to do repeated index lookups than it is to +# sort. But in other cases, it is faster to sort than to do repeated index +# lookups. +# +do_execsql_test 2.1a { + CREATE TABLE t2(a,b,c); + CREATE INDEX t2bc ON t2(b,c); + ANALYZE; + INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9'); + INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5'); + ANALYZE sqlite_master; + + EXPLAIN QUERY PLAN + SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c; +} {~/B-TREE/} +do_execsql_test 2.1b { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=0 ORDER BY a, b, c; -} {~/B-TREE/} +} {/B-TREE/} + + do_execsql_test 2.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c; } {/B-TREE/} do_execsql_test 2.3 { ADDED test/orderby6.test Index: test/orderby6.test ================================================================== --- test/orderby6.test +++ test/orderby6.test @@ -0,0 +1,183 @@ +# 2014-03-21 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing that the block-sort optimization. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix orderby6 + +# Run all tests twice. Once with a normal table and a second time +# with a WITHOUT ROWID table +# +foreach {tn rowidclause} {1 {} 2 {WITHOUT ROWID}} { + + # Construct a table with 1000 rows and a split primary key + # + reset_db + do_test $tn.1 { + db eval "CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c)) $rowidclause;" + db eval { + WITH RECURSIVE + cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000) + INSERT INTO t1 SELECT x, x%40, x/40 FROM cnt; + } + } {} + + # Run various ORDER BY queries that can benefit from block-sort. + # Compare the output to the same output using a full-sort enforced + # by adding + to each term of the ORDER BY clause. + # + do_execsql_test $tn.2 { + SELECT b,a,c FROM t1 ORDER BY b,a,c; + } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+a,+c}] + do_execsql_test $tn.3 { + SELECT b,a,c FROM t1 ORDER BY b,c DESC,a; + } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+c DESC,+a}] + do_execsql_test $tn.4 { + SELECT b,a,c FROM t1 ORDER BY b DESC,c,a; + } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+c,+a}] + do_execsql_test $tn.5 { + SELECT b,a,c FROM t1 ORDER BY b DESC,a,c; + } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+a,+c}] + + # LIMIT and OFFSET clauses on block-sort queries. + # + do_execsql_test $tn.11 { + SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20; + } {840 880 920 960 1000 1 41 81 121 161} + do_execsql_test $tn.11x { + SELECT a FROM t1 ORDER BY +b, a LIMIT 10 OFFSET 20; + } {840 880 920 960 1000 1 41 81 121 161} + + do_execsql_test $tn.12 { + SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 20; + } {839 879 919 959 999 38 78 118 158 198} + do_execsql_test $tn.12 { + SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 20; + } {839 879 919 959 999 38 78 118 158 198} + + do_execsql_test $tn.13 { + SELECT a FROM t1 ORDER BY b, a DESC LIMIT 10 OFFSET 45; + } {161 121 81 41 1 962 922 882 842 802} + do_execsql_test $tn.13x { + SELECT a FROM t1 ORDER BY +b, a DESC LIMIT 10 OFFSET 45; + } {161 121 81 41 1 962 922 882 842 802} + + do_execsql_test $tn.14 { + SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 45; + } {838 878 918 958 998 37 77 117 157 197} + do_execsql_test $tn.14x { + SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 45; + } {838 878 918 958 998 37 77 117 157 197} + + # Many test cases where the LIMIT+OFFSET window is in various + # alignments with block-sort boundaries. + # + foreach {tx limit offset orderby} { + 1 10 24 {+b,+a} + 2 10 25 {+b,+a} + 3 10 26 {+b,+a} + 4 10 39 {+b,+a} + 5 10 40 {+b,+a} + 6 10 41 {+b,+a} + 7 27 24 {+b,+a} + 8 27 49 {+b,+a} + 11 10 24 {+b DESC,+a} + 12 10 25 {+b DESC,+a} + 13 10 26 {+b DESC,+a} + 14 10 39 {+b DESC,+a} + 15 10 40 {+b DESC,+a} + 16 10 41 {+b DESC,+a} + 17 27 24 {+b DESC,+a} + 18 27 49 {+b DESC,+a} + 21 10 24 {+b,+a DESC} + 22 10 25 {+b,+a DESC} + 23 10 26 {+b,+a DESC} + 24 10 39 {+b,+a DESC} + 25 10 40 {+b,+a DESC} + 26 10 41 {+b,+a DESC} + 27 27 24 {+b,+a DESC} + 28 27 49 {+b,+a DESC} + 31 10 24 {+b DESC,+a DESC} + 32 10 25 {+b DESC,+a DESC} + 33 10 26 {+b DESC,+a DESC} + 34 10 39 {+b DESC,+a DESC} + 35 10 40 {+b DESC,+a DESC} + 36 10 41 {+b DESC,+a DESC} + 37 27 24 {+b DESC,+a DESC} + 38 27 49 {+b DESC,+a DESC} + } { + set sql1 "SELECT a FROM t1 ORDER BY $orderby LIMIT $limit OFFSET $offset;" + set sql2 [string map {+ {}} $sql1] + # puts $sql2\n$sql1\n[db eval $sql2] + do_test $tn.21.$tx {db eval $::sql2} [db eval $sql1] + } + + ######################################################################## + # A second test table, t2, has many columns open to sorting. + do_test $tn.31 { + db eval "CREATE TABLE t2(a,b,c,d,e,f,PRIMARY KEY(b,c,d,e,f)) $rowidclause;" + db eval { + WITH RECURSIVE + cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<242) + INSERT INTO t2 SELECT x, x%3, (x/3)%3, (x/9)%3, (x/27)%3, (x/81)%3 + FROM cnt; + } + } {} + + do_execsql_test $tn.32 { + SELECT a FROM t2 ORDER BY b,c,d,e,f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] + do_execsql_test $tn.33 { + SELECT a FROM t2 ORDER BY b,c,d,e,+f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] + do_execsql_test $tn.34 { + SELECT a FROM t2 ORDER BY b,c,d,+e,+f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] + do_execsql_test $tn.35 { + SELECT a FROM t2 ORDER BY b,c,+d,+e,+f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] + do_execsql_test $tn.36 { + SELECT a FROM t2 ORDER BY b,+c,+d,+e,+f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}] + + do_execsql_test $tn.37 { + SELECT a FROM t2 ORDER BY b,c,d,e,f DESC; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC;}] + do_execsql_test $tn.38 { + SELECT a FROM t2 ORDER BY b,c,d,e DESC,f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e DESC,+f;}] + do_execsql_test $tn.39 { + SELECT a FROM t2 ORDER BY b,c,d DESC,e,f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d DESC,+e,+f;}] + do_execsql_test $tn.40 { + SELECT a FROM t2 ORDER BY b,c DESC,d,e,f; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c DESC,+d,+e,+f;}] + do_execsql_test $tn.41 { + SELECT a FROM t2 ORDER BY b DESC,c,d,e,f; + } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c,+d,+e,+f;}] + + do_execsql_test $tn.42 { + SELECT a FROM t2 ORDER BY b DESC,c DESC,d,e,f LIMIT 31; + } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c DESC,+d,+e,+f LIMIT 31}] + do_execsql_test $tn.43 { + SELECT a FROM t2 ORDER BY b,c,d,e,f DESC LIMIT 8 OFFSET 7; + } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC LIMIT 8 OFFSET 7}] + + +} + + + +finish_test Index: test/whereG.test ================================================================== --- test/whereG.test +++ test/whereG.test @@ -93,11 +93,11 @@ SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; -} {/.*track.*composer.*album.*/} +} {/.*track.*(composer.*album|album.*composer).*/} do_execsql_test whereG-1.6 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid @@ -108,11 +108,11 @@ SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND unlikely(composer.cid=track.cid) AND unlikely(album.aid=track.aid); -} {/.*track.*composer.*album.*/} +} {/.*track.*(composer.*album|album.*composer).*/} do_execsql_test whereG-1.8 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND unlikely(composer.cid=track.cid)