Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the ability to use indices for the first few terms of an ORDER BY clause, then sort in batches to handle the later terms. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fa06a6fed9f48322d9b89721799ba12c |
User & Date: | drh 2014-03-21 20:58:42.176 |
Context
2014-03-22
| ||
00:27 | Fix the ORDER BY optimization logic so that it will do a block-sort on a partial DESC ORDER BY. This enhancement uncovered a memory leak in pushUntoSorter() which is also fixed. (check-in: c36f74611c user: drh tags: trunk) | |
2014-03-21
| ||
20:58 | Add the ability to use indices for the first few terms of an ORDER BY clause, then sort in batches to handle the later terms. (check-in: fa06a6fed9 user: drh tags: trunk) | |
19:56 | Change the names of SRT_DistTable and SRT_Table used by CTE to more meaningful SRT_DistFifo and SRT_Fifo, respectively. Simplify the IgnorableOrderby() macro in the process. (check-in: 45d8cc678d user: drh tags: trunk) | |
18:45 | Merge the OFFSET-on-query-without-FROM fix from trunk. (check-in: 71e9ae72c2 user: drh tags: orderby-planning) | |
Changes
Changes to src/btree.c.
︙ | ︙ | |||
7420 7421 7422 7423 7424 7425 7426 7427 7428 7429 7430 7431 7432 7433 | ** a no-op). */ invalidateIncrblobCursors(p, 0, 1); rc = clearDatabasePage(pBt, (Pgno)iTable, 0, pnChange); } sqlite3BtreeLeave(p); return rc; } /* ** 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. ** ** This routine will fail with SQLITE_LOCKED if there are any open | > > > > > > > > > | 7420 7421 7422 7423 7424 7425 7426 7427 7428 7429 7430 7431 7432 7433 7434 7435 7436 7437 7438 7439 7440 7441 7442 | ** a no-op). */ invalidateIncrblobCursors(p, 0, 1); 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. ** ** This routine will fail with SQLITE_LOCKED if there are any open |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
111 112 113 114 115 116 117 118 119 120 121 122 123 124 | ** indices.) */ #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*); void sqlite3BtreeTripAllCursors(Btree*, int); void sqlite3BtreeGetMeta(Btree *pBtree, int idx, u32 *pValue); int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value); int sqlite3BtreeNewDb(Btree *p); | > | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | ** indices.) */ #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); int sqlite3BtreeNewDb(Btree *p); |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
951 952 953 954 955 956 957 | ExprList *sqlite3ExprListDup(sqlite3 *db, ExprList *p, int flags){ ExprList *pNew; struct ExprList_item *pItem, *pOldItem; int i; if( p==0 ) return 0; pNew = sqlite3DbMallocRaw(db, sizeof(*pNew) ); if( pNew==0 ) return 0; | < | 951 952 953 954 955 956 957 958 959 960 961 962 963 964 | ExprList *sqlite3ExprListDup(sqlite3 *db, ExprList *p, int flags){ ExprList *pNew; struct ExprList_item *pItem, *pOldItem; int i; if( p==0 ) return 0; pNew = sqlite3DbMallocRaw(db, sizeof(*pNew) ); if( pNew==0 ) return 0; pNew->nExpr = i = p->nExpr; if( (flags & EXPRDUP_REDUCE)==0 ) for(i=1; i<p->nExpr; i+=i){} pNew->a = pItem = sqlite3DbMallocRaw(db, i*sizeof(p->a[0]) ); if( pItem==0 ){ sqlite3DbFree(db, pNew); return 0; } |
︙ | ︙ | |||
1064 1065 1066 1067 1068 1069 1070 | pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags); pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags); pNew->iLimit = 0; pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; | < | 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 | pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags); pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags); pNew->iLimit = 0; pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->nSelectRow = p->nSelectRow; pNew->pWith = withDup(db, p->pWith); return pNew; } #else Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){ assert( p==0 ); |
︙ | ︙ | |||
2336 2337 2338 2339 2340 2341 2342 | ** Generate code to move content from registers iFrom...iFrom+nReg-1 ** over to iTo..iTo+nReg-1. Keep the column cache up-to-date. */ void sqlite3ExprCodeMove(Parse *pParse, int iFrom, int iTo, int nReg){ int i; struct yColCache *p; assert( iFrom>=iTo+nReg || iFrom+nReg<=iTo ); | | | 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 | ** Generate code to move content from registers iFrom...iFrom+nReg-1 ** over to iTo..iTo+nReg-1. Keep the column cache up-to-date. */ 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); for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){ int x = p->iReg; if( x>=iFrom && x<iFrom+nReg ){ p->iReg += iTo-iFrom; } } } |
︙ | ︙ | |||
2737 2738 2739 2740 2741 2742 2743 | testcase( pDef->funcFlags & OPFLAG_LENGTHARG ); pFarg->a[0].pExpr->op2 = pDef->funcFlags & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG); } } sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */ | | | 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 | testcase( pDef->funcFlags & OPFLAG_LENGTHARG ); pFarg->a[0].pExpr->op2 = pDef->funcFlags & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG); } } sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */ sqlite3ExprCodeExprList(pParse, pFarg, r1, SQLITE_ECEL_DUP|SQLITE_ECEL_FACTOR); sqlite3ExprCachePop(pParse, 1); /* Ticket 2ea2425d34be */ }else{ r1 = 0; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* Possibly overload the function if the first argument is |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1871 1872 1873 1874 1875 1876 1877 | /* Do the b-tree integrity checks */ sqlite3VdbeAddOp3(v, OP_IntegrityCk, 2, cnt, 1); 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); | | | 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 | /* Do the b-tree integrity checks */ sqlite3VdbeAddOp3(v, OP_IntegrityCk, 2, cnt, 1); 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); 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. */ for(x=sqliteHashFirst(pTbls); x && !isQuick; x=sqliteHashNext(x)){ |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. */ static void clearSelect(sqlite3 *db, Select *p){ sqlite3ExprListDelete(db, p->pEList); | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | ** ************************************************************************* ** 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. */ static void clearSelect(sqlite3 *db, Select *p){ sqlite3ExprListDelete(db, p->pEList); |
︙ | ︙ | |||
83 84 85 86 87 88 89 | pNew->selFlags = selFlags; pNew->op = TK_SELECT; pNew->pLimit = pLimit; pNew->pOffset = pOffset; assert( pOffset==0 || pLimit!=0 ); pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; | < | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | pNew->selFlags = selFlags; pNew->op = TK_SELECT; pNew->pLimit = pLimit; pNew->pOffset = pOffset; assert( pOffset==0 || pLimit!=0 ); pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; if( db->mallocFailed ) { clearSelect(db, pNew); if( pNew!=&standin ) sqlite3DbFree(db, pNew); pNew = 0; }else{ assert( pNew->pSrc!=0 || pParse->nErr>0 ); } |
︙ | ︙ | |||
415 416 417 418 419 420 421 422 | isOuter, &p->pWhere); } } } return 0; } /* | > > > > > > > > | | | | > | | | > > > > > > | > > > > > > > > > > > > > > > > > > > > | > | | > | | | 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 | isOuter, &p->pWhere); } } } 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 in register regData ** into the sorter. */ static void pushOntoSorter( Parse *pParse, /* Parser context */ 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 = pSort->pOrderBy->nExpr; int regBase = sqlite3GetTempRange(pParse, nExpr+2); int regRecord = sqlite3GetTempReg(pParse); int nOBSat = pSort->nOBSat; int op; sqlite3ExprCacheClear(pParse); 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+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, 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; }else{ iLimit = pSelect->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, pSort->iECursor); sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor); sqlite3VdbeJumpHere(v, addr2); } } /* ** Add code to implement the OFFSET */ |
︙ | ︙ | |||
530 531 532 533 534 535 536 | return 1; }else{ return 0; } } #endif | < < < < < < < < < < < < < | > | | 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 | return 1; }else{ return 0; } } #endif /* ** This routine generates the code for the inside of the inner loop ** of a SELECT. ** ** 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 */ 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 */ ){ Vdbe *v = pParse->pVdbe; int i; int hasDistinct; /* True if the DISTINCT keyword is present */ int regResult; /* Start of memory holding result set */ int eDest = pDest->eDest; /* How to dispose of results */ int iParm = pDest->iSDParm; /* First argument to disposal method */ int nResultCol; /* Number of result columns */ assert( v ); assert( pEList!=0 ); hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP; if( pSort && pSort->pOrderBy==0 ) pSort = 0; if( pSort==0 && !hasDistinct ){ assert( iContinue!=0 ); codeOffset(v, p->iOffset, iContinue); } /* Pull the requested columns. */ nResultCol = pEList->nExpr; |
︙ | ︙ | |||
665 666 667 668 669 670 671 | default: { assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED ); codeDistinct(pParse, pDistinct->tabTnct, iContinue, nResultCol, regResult); break; } } | | | 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 | default: { assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED ); codeDistinct(pParse, pDistinct->tabTnct, iContinue, nResultCol, regResult); break; } } if( pSort==0 ){ codeOffset(v, p->iOffset, iContinue); } } switch( eDest ){ /* In this mode, write each query result to the key of the temporary ** table iParm. |
︙ | ︙ | |||
714 715 716 717 718 719 720 | ** 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); | | | | | | | 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 | ** 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); sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); sqlite3ReleaseTempReg(pParse, r2); } sqlite3ReleaseTempReg(pParse, r1); break; } #ifndef SQLITE_OMIT_SUBQUERY /* If we are creating a set for an "expr IN (SELECT ...)" construct, ** then there should be a single item on the stack. Write this ** item into the set table with bogus data. */ case SRT_Set: { assert( nResultCol==1 ); pDest->affSdst = sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst); 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, 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); sqlite3ReleaseTempReg(pParse, r1); } |
︙ | ︙ | |||
769 770 771 772 773 774 775 | /* If this is a scalar select that is part of an expression, then ** store the results in the appropriate memory cell and break out ** of the scan loop. */ case SRT_Mem: { assert( nResultCol==1 ); | | | | | | 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 | /* If this is a scalar select that is part of an expression, then ** store the results in the appropriate memory cell and break out ** of the scan loop. */ case SRT_Mem: { 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 */ } break; } #endif /* #ifndef SQLITE_OMIT_SUBQUERY */ case SRT_Coroutine: /* Send data to a co-routine */ case SRT_Output: { /* Return the results */ testcase( eDest==SRT_Coroutine ); testcase( eDest==SRT_Output ); if( pSort ){ int r1 = sqlite3GetTempReg(pParse); 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, nResultCol); sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol); } |
︙ | ︙ | |||
863 864 865 866 867 868 869 | #endif } /* 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. */ | | | 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 | #endif } /* 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( 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. |
︙ | ︙ | |||
934 935 936 937 938 939 940 | ** then the KeyInfo structure is appropriate for initializing a virtual ** index to implement a DISTINCT test. ** ** Space to hold the KeyInfo structure is obtain from malloc. The calling ** function is responsible for seeing that this structure is eventually ** freed. */ | | > > > > > | | | | | 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 | ** then the KeyInfo structure is appropriate for initializing a virtual ** index to implement a DISTINCT test. ** ** 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, /* 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-iStart, 1); if( pInfo ){ 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-iStart] = pColl; pInfo->aSortOrder[i-iStart] = pItem->sortOrder; } } return pInfo; } #ifndef SQLITE_OMIT_COMPOUND_SELECT /* |
︙ | ︙ | |||
1052 1053 1054 1055 1056 1057 1058 | ** then the results were placed in a sorter. After the loop is terminated ** we need to run the sorter and output the results. The following ** routine generates the code needed to do that. */ static void generateSortTail( Parse *pParse, /* Parsing context */ Select *p, /* The SELECT statement */ | | > > | < < > > > > > > > | > | | > | > | | 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 | ** then the results were placed in a sorter. After the loop is terminated ** we need to run the sorter and output the results. The following ** routine generates the code needed to do that. */ static void generateSortTail( Parse *pParse, /* Parsing context */ Select *p, /* The SELECT statement */ 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 = pSort->pOrderBy; int eDest = pDest->eDest; int iParm = pDest->iSDParm; int regRow; int regRowid; int nKey; 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); } nKey = pOrderBy->nExpr - pSort->nOBSat; if( pSort->sortFlags & SORTFLAG_UseSorter ){ int regSortOut = ++pParse->nMem; int ptab2 = pParse->nTab++; 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, 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, nKey+1, regRow); } switch( eDest ){ case SRT_Table: case SRT_EphemTab: { testcase( eDest==SRT_Table ); testcase( eDest==SRT_EphemTab ); sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid); |
︙ | ︙ | |||
1146 1147 1148 1149 1150 1151 1152 | } sqlite3ReleaseTempReg(pParse, regRow); sqlite3ReleaseTempReg(pParse, regRowid); /* The bottom of the loop */ sqlite3VdbeResolveLabel(v, addrContinue); | | > | 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 | } sqlite3ReleaseTempReg(pParse, regRow); sqlite3ReleaseTempReg(pParse, regRowid); /* The bottom of the loop */ sqlite3VdbeResolveLabel(v, addrContinue); 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); } } /* |
︙ | ︙ | |||
4308 4309 4310 4311 4312 4313 4314 | Expr *pE = pFunc->pExpr; assert( !ExprHasProperty(pE, EP_xIsSelect) ); if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){ sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one " "argument"); pFunc->iDistinct = -1; }else{ | | | 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 | Expr *pE = pFunc->pExpr; assert( !ExprHasProperty(pE, EP_xIsSelect) ); 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, 0); sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0, (char*)pKeyInfo, P4_KEYINFO); } } } } |
︙ | ︙ | |||
4463 4464 4465 4466 4467 4468 4469 | int i, j; /* Loop counters */ WhereInfo *pWInfo; /* Return from sqlite3WhereBegin() */ 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 */ | < < > | 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 4543 4544 4545 4546 4547 4548 4549 | int i, j; /* Loop counters */ WhereInfo *pWInfo; /* Return from sqlite3WhereBegin() */ 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 *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 */ 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 int iRestoreSelectId = pParse->iSelectId; pParse->iSelectId = pParse->iNextSelectId++; |
︙ | ︙ | |||
4501 4502 4503 4504 4505 4506 4507 | /* If ORDER BY makes no difference in the output then neither does ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; p->selFlags &= ~SF_Distinct; } sqlite3SelectPrep(pParse, p, 0); | > | | 4568 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 | /* If ORDER BY makes no difference in the output then neither does ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; p->selFlags &= ~SF_Distinct; } sqlite3SelectPrep(pParse, p, 0); memset(&sSort, 0, sizeof(sSort)); sSort.pOrderBy = p->pOrderBy; pTabList = p->pSrc; pEList = p->pEList; if( pParse->nErr || db->mallocFailed ){ goto select_end; } isAgg = (p->selFlags & SF_Aggregate)!=0; assert( pEList!=0 ); |
︙ | ︙ | |||
4623 4624 4625 4626 4627 4628 4629 | } if( /*pParse->nErr ||*/ db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ | | | 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 | } if( /*pParse->nErr ||*/ db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); pTabList = p->pSrc; if( !IgnorableOrderby(pDest) ){ sSort.pOrderBy = p->pOrderBy; } } pEList = p->pEList; #endif pWhere = p->pWhere; pGroupBy = p->pGroupBy; pHaving = p->pHaving; |
︙ | ︙ | |||
4650 4651 4652 4653 4654 4655 4656 | /* 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. */ | | | | | | | | | | | | | | | | | > | > > > > | | < | | 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 4785 4786 4787 4788 4789 4790 4791 4792 4793 4794 4795 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 4838 4839 4840 4841 4842 4843 4844 | /* 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, sSort.pOrderBy, -1)==0 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){ 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: ** ** SELECT DISTINCT xyz FROM ... ORDER BY xyz ** ** is transformed to: ** ** SELECT xyz FROM ... GROUP BY xyz ** ** The second form is preferred as a single index (or temp-table) may be ** 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(sSort.pOrderBy, p->pEList, -1)==0 ){ p->selFlags &= ~SF_Distinct; p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0); pGroupBy = p->pGroupBy; 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 ); } /* If there is an ORDER BY clause, then this sorting ** index might end up being unused if the data can be ** 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( sSort.pOrderBy ){ KeyInfo *pKeyInfo; pKeyInfo = keyInfoFromExprList(pParse, sSort.pOrderBy, 0, 0); sSort.iECursor = pParse->nTab++; sSort.addrSortIndex = sqlite3VdbeAddOp4(v, OP_OpenEphemeral, sSort.iECursor, sSort.pOrderBy->nExpr+2, 0, (char*)pKeyInfo, P4_KEYINFO); }else{ sSort.addrSortIndex = -1; } /* If the output is destined for a temporary table, open that table. */ if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr); } /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); p->nSelectRow = LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); 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,0), P4_KEYINFO); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED; }else{ sDistinct.eTnctType = WHERE_DISTINCT_NOOP; } 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, 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( 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( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex); } /* Use the standard inner loop. */ selectInnerLoop(pParse, p, pEList, -1, &sSort, &sDistinct, pDest, sqlite3WhereContinueLabel(pWInfo), sqlite3WhereBreakLabel(pWInfo)); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); }else{ |
︙ | ︙ | |||
4814 4815 4816 4817 4818 4819 4820 | 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); | | | 4886 4887 4888 4889 4890 4891 4892 4893 4894 4895 4896 4897 4898 4899 4900 | 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, sSort.pOrderBy); if( pHaving ){ sqlite3ExprAnalyzeAggregates(&sNC, pHaving); } sAggInfo.nAccumulator = sAggInfo.nColumn; for(i=0; i<sAggInfo.nFunc; i++){ assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) ); sNC.ncFlags |= NC_InAggFunc; |
︙ | ︙ | |||
4848 4849 4850 4851 4852 4853 4854 | /* If there is a GROUP BY clause we might need a sorting index to ** implement it. Allocate that sorting index now. If it turns out ** that we do not need it after all, the OP_SorterOpen instruction ** will be converted into a Noop. */ sAggInfo.sortingIdx = pParse->nTab++; | | | 4920 4921 4922 4923 4924 4925 4926 4927 4928 4929 4930 4931 4932 4933 4934 | /* If there is a GROUP BY clause we might need a sorting index to ** implement it. Allocate that sorting index now. If it turns out ** that we do not need it after all, the OP_SorterOpen instruction ** will be converted into a Noop. */ sAggInfo.sortingIdx = pParse->nTab++; 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 */ iUseFlag = ++pParse->nMem; |
︙ | ︙ | |||
4877 4878 4879 4880 4881 4882 4883 | /* Begin a loop that will extract all source rows in GROUP BY order. ** 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); | | | | 4949 4950 4951 4952 4953 4954 4955 4956 4957 4958 4959 4960 4961 4962 4963 4964 4965 4966 | /* Begin a loop that will extract all source rows in GROUP BY order. ** 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, WHERE_GROUPBY, 0); if( pWInfo==0 ) goto select_end; 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; }else{ /* Rows are coming out in undetermined order. We have to push |
︙ | ︙ | |||
5031 5032 5033 5034 5035 5036 5037 | sqlite3VdbeResolveLabel(v, addrOutputRow); addrOutputRow = sqlite3VdbeCurrentAddr(v); 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); | | | 5103 5104 5105 5106 5107 5108 5109 5110 5111 5112 5113 5114 5115 5116 5117 | sqlite3VdbeResolveLabel(v, addrOutputRow); addrOutputRow = sqlite3VdbeCurrentAddr(v); 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, &sSort, &sDistinct, pDest, addrOutputRow+1, addrSetAbort); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); VdbeComment((v, "end groupby result generator")); /* Generate a subroutine that will reset the group-by accumulator */ |
︙ | ︙ | |||
5163 5164 5165 5166 5167 5168 5169 | pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); | | | | | | | 5235 5236 5237 5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250 5251 5252 5253 5254 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 | pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); assert( pMinMax==0 || pMinMax->nExpr==1 ); 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); } sSort.pOrderBy = 0; sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL); selectInnerLoop(pParse, p, p->pEList, -1, 0, 0, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } sqlite3VdbeResolveLabel(v, addrEnd); } /* endif aggregate query */ if( sDistinct.eTnctType==WHERE_DISTINCT_UNORDERED ){ explainTempTable(pParse, "DISTINCT"); } /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ 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); /* The SELECT was successfully coded. Set the return code to 0 |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1954 1955 1956 1957 1958 1959 1960 | ** column expression as it exists in a SELECT statement. However, if ** the bSpanIsTab flag is set, then zSpan is overloaded to mean the name ** 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 */ | < | 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 | ** column expression as it exists in a SELECT statement. However, if ** the bSpanIsTab flag is set, then zSpan is overloaded to mean the name ** 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 */ 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 */ unsigned done :1; /* A flag to indicate when processing is finished */ unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */ |
︙ | ︙ | |||
2178 2179 2180 2181 2182 2183 2184 | ** sequences for the ORDER BY clause. */ 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 */ | | | 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 | ** sequences for the ORDER BY clause. */ 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[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 */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ |
︙ | ︙ | |||
2202 2203 2204 2205 2206 2207 2208 | */ #define SF_Distinct 0x0001 /* Output should be DISTINCT */ #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 */ | | | | 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 | */ #define SF_Distinct 0x0001 /* Output should be DISTINCT */ #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 */ /* 0x0040 NOT USED */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ /* 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 */ /* |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
1076 1077 1078 1079 1080 1081 1082 | UPDATE_MAX_BLOBSIZE(pOut); break; } /* Opcode: Move P1 P2 P3 * * ** Synopsis: r[P2@P3]=r[P1@P3] ** | | | | > | | 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 | UPDATE_MAX_BLOBSIZE(pOut); break; } /* Opcode: Move P1 P2 P3 * * ** Synopsis: r[P2@P3]=r[P1@P3] ** ** 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-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 */ int p2; /* Register to copy to */ n = pOp->p3; p1 = pOp->p1; p2 = pOp->p2; assert( n>0 && p1>0 && p2>0 ); assert( p1+n<=p2 || p2+n<=p1 ); pIn1 = &aMem[p1]; pOut = &aMem[p2]; do{ assert( pOut<=&aMem[(p->nMem-p->nCursor)] ); assert( pIn1<=&aMem[(p->nMem-p->nCursor)] ); |
︙ | ︙ | |||
1114 1115 1116 1117 1118 1119 1120 | #endif pIn1->flags = MEM_Undefined; pIn1->xDel = 0; pIn1->zMalloc = zMalloc; REGISTER_TRACE(p2++, pOut); pIn1++; pOut++; | | | 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 | #endif pIn1->flags = MEM_Undefined; pIn1->xDel = 0; pIn1->zMalloc = zMalloc; REGISTER_TRACE(p2++, pOut); pIn1++; pOut++; }while( --n ); break; } /* Opcode: Copy P1 P2 P3 * * ** Synopsis: r[P2@P3+1]=r[P1@P3+1] ** ** Make a copy of registers P1..P1+P3 into registers P2..P2+P3. |
︙ | ︙ | |||
1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 | assert( pOp->p4type==P4_INTARRAY ); assert( pOp->p4.ai ); aPermute = pOp->p4.ai; break; } /* Opcode: Compare P1 P2 P3 P4 P5 ** ** 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. ** ** If P5 has the OPFLAG_PERMUTE bit set, then the order of comparison is ** determined by the most recent OP_Permutation operator. If the | > | 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 | assert( pOp->p4type==P4_INTARRAY ); assert( pOp->p4.ai ); 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. ** ** If P5 has the OPFLAG_PERMUTE bit set, then the order of comparison is ** determined by the most recent OP_Permutation operator. If the |
︙ | ︙ | |||
3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 | SQLITE_OPEN_DELETEONCLOSE | SQLITE_OPEN_TRANSIENT_DB; 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; 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); } if( rc==SQLITE_OK ){ /* If a transient index is required, create it by calling | > | 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 3340 3341 3342 | SQLITE_OPEN_DELETEONCLOSE | SQLITE_OPEN_TRANSIENT_DB; 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); } if( rc==SQLITE_OK ){ /* If a transient index is required, create it by calling |
︙ | ︙ | |||
3816 3817 3818 3819 3820 3821 3822 | assert( pC->rowidIsValid==0 ); } pC->seekResult = res; break; } /* Opcode: Sequence P1 P2 * * * | | | 3819 3820 3821 3822 3823 3824 3825 3826 3827 3828 3829 3830 3831 3832 3833 | assert( pC->rowidIsValid==0 ); } pC->seekResult = res; break; } /* Opcode: Sequence P1 P2 * * * ** 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. */ case OP_Sequence: { /* out2-prerelease */ |
︙ | ︙ | |||
4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 | assert( memIsValid(&aMem[pOp->p3]) ); memAboutToChange(p, &aMem[pOp->p3]); aMem[pOp->p3].u.i += nChange; } } 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 ** auxiliary database file if P1==1 or in an attached database if ** P1>1. Write the root page number of the new table into | > > > > > > > > > > > > > > > > > > > > > > > | 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 4888 4889 4890 4891 4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 | assert( memIsValid(&aMem[pOp->p3]) ); memAboutToChange(p, &aMem[pOp->p3]); 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->p1<p->nCursor ); 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 ** auxiliary database file if P1==1 or in an attached database if ** P1>1. Write the root page number of the new table into |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
68 69 70 71 72 73 74 75 76 77 78 79 80 81 | int pseudoTableReg; /* Register holding pseudotable content. */ i16 nField; /* Number of fields in the header */ 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 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 */ i64 movetoTarget; /* Argument to the deferred sqlite3BtreeMoveto() */ i64 lastRowid; /* Rowid being deleted by OP_Delete */ | > | 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | int pseudoTableReg; /* Register holding pseudotable content. */ i16 nField; /* Number of fields in the header */ 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 */ i64 movetoTarget; /* Argument to the deferred sqlite3BtreeMoveto() */ i64 lastRowid; /* Rowid being deleted by OP_Delete */ |
︙ | ︙ | |||
433 434 435 436 437 438 439 440 441 442 443 444 445 446 | int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve); int sqlite3VdbeCloseStatement(Vdbe *, int); void sqlite3VdbeFrameDelete(VdbeFrame*); int sqlite3VdbeFrameRestore(VdbeFrame *); int sqlite3VdbeTransferError(Vdbe *p); int sqlite3VdbeSorterInit(sqlite3 *, VdbeCursor *); 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 *); int sqlite3VdbeSorterCompare(const VdbeCursor *, Mem *, int, int *); | > | 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 | int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve); int sqlite3VdbeCloseStatement(Vdbe *, int); 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 *); int sqlite3VdbeSorterCompare(const VdbeCursor *, Mem *, int, int *); |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
779 780 781 782 783 784 785 | } assert( p->nOp>0 ); assert( addr<p->nOp ); if( addr<0 ){ addr = p->nOp - 1; } pOp = &p->aOp[addr]; | | > > | 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 | } assert( p->nOp>0 ); assert( addr<p->nOp ); if( addr<0 ){ addr = p->nOp - 1; } pOp = &p->aOp[addr]; 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 *). */ pOp->p4.i = SQLITE_PTR_TO_INT(zP4); pOp->p4type = P4_INT32; |
︙ | ︙ |
Changes to src/vdbesort.c.
︙ | ︙ | |||
499 500 501 502 503 504 505 506 507 508 509 510 511 512 | SorterRecord *p; SorterRecord *pNext; for(p=pRecord; p; p=pNext){ pNext = p->pNext; sqlite3DbFree(db, p); } } /* ** Free any cursor components allocated by sqlite3VdbeSorterXXX routines. */ void sqlite3VdbeSorterClose(sqlite3 *db, VdbeCursor *pCsr){ VdbeSorter *pSorter = pCsr->pSorter; if( pSorter ){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < | < < < < < | 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 | SorterRecord *p; SorterRecord *pNext; 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; i<pSorter->nTree; 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 ){ sqlite3VdbeSorterReset(db, pSorter); sqlite3DbFree(db, pSorter->pUnpacked); sqlite3DbFree(db, pSorter); pCsr->pSorter = 0; } } /* |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
35 36 37 38 39 40 41 | } /* ** 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){ | | | 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | } /* ** 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->nOBSat; } /* ** Return the VDBE address or label to jump to in order to continue ** immediately with the next row of a WHERE clause. */ int sqlite3WhereContinueLabel(WhereInfo *pWInfo){ |
︙ | ︙ | |||
3033 3034 3035 3036 3037 3038 3039 3040 | ** was passed to this function to implement a "SELECT min(x) ..." ** query, then the caller will only allow the loop to run for ** 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. */ if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0 | > > > | | 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 | ** was passed to this function to implement a "SELECT min(x) ..." ** query, then the caller will only allow the loop to run for ** 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->nOBSat>0 && (pIdx->nKeyCol>nEq) ){ assert( pLoop->u.btree.nSkip==0 ); bSeekPastNull = 1; nExtraReg = 1; } |
︙ | ︙ | |||
4504 4505 4506 4507 4508 4509 4510 | if( i>=nConstraint ){ pNew->nLTerm = mxTerm+1; 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; | | | | 4507 4508 4509 4510 4511 4512 4513 4514 4515 4516 4517 4518 4519 4520 4521 4522 | if( i>=nConstraint ){ pNew->nLTerm = mxTerm+1; 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 = (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 ){ sqlite3_free(pNew->u.vtab.idxStr); pNew->u.vtab.needFree = 0; |
︙ | ︙ | |||
4666 4667 4668 4669 4670 4671 4672 | whereLoopClear(db, pNew); return rc; } /* ** 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 | | | | | | | 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 | whereLoopClear(db, pNew); return rc; } /* ** 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 N: ** ** 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 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[] */ WhereLoop *pLast, /* Add this WhereLoop to the end of pPath->aLoop[] */ Bitmask *pRevMask /* OUT: Mask of WhereLoops to run in reverse order */ |
︙ | ︙ | |||
4913 4914 4915 4916 4917 4918 4919 | if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue; if( (mTerm&~orderDistinctMask)==0 ){ obSat |= MASKBIT(i); } } } } /* End the loop over all WhereLoops from outer-most down to inner-most */ | | | > > > > > > | 4916 4917 4918 4919 4920 4921 4922 4923 4924 4925 4926 4927 4928 4929 4930 4931 4932 4933 4934 4935 4936 4937 | if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue; if( (mTerm&~orderDistinctMask)==0 ){ obSat |= MASKBIT(i); } } } } /* End the loop over all WhereLoops from outer-most down to inner-most */ 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: */ static const char *wherePathName(WherePath *pPath, int nLoop, WhereLoop *pLast){ static char zName[65]; |
︙ | ︙ | |||
4951 4952 4953 4954 4955 4956 4957 4958 4959 4960 4961 | int mxChoice; /* Maximum number of simultaneous paths tracked */ int nLoop; /* Number of terms in the join */ 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 */ 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 */ | > < | 4960 4961 4962 4963 4964 4965 4966 4967 4968 4969 4970 4971 4972 4973 4974 4975 4976 4977 4978 | int mxChoice; /* Maximum number of simultaneous paths tracked */ int nLoop; /* Number of terms in the join */ 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 */ 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 */ WhereLoop *pWLoop; /* One of the WhereLoop objects */ WhereLoop **pX; /* Used to divy up the pSpace memory */ |
︙ | ︙ | |||
4997 4998 4999 5000 5001 5002 5003 | ** of computing an automatic index is not paid back within the first 25 ** rows, then do not use the automatic index. */ 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 */ | < | > | | < < < < < | | | | < | > | | < | > > > > > > > > > | > | < < < | | | | | | | < | 5006 5007 5008 5009 5010 5011 5012 5013 5014 5015 5016 5017 5018 5019 5020 5021 5022 5023 5024 5025 5026 5027 5028 5029 5030 5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 5049 5050 5051 5052 5053 5054 5055 5056 5057 5058 5059 5060 5061 5062 5063 5064 5065 5066 5067 5068 5069 5070 5071 5072 5073 5074 5075 5076 5077 5078 5079 5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 5107 5108 5109 5110 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 5128 5129 5130 5131 5132 5133 5134 5135 5136 5137 5138 5139 5140 5141 5142 5143 | ** of computing an automatic index is not paid back within the first 25 ** rows, then do not use the automatic index. */ 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 */ if( pWInfo->pOrderBy==0 || nRowEst==0 ){ aFrom[0].isOrdered = 0; nOrderBy = 0; }else{ 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 */ for(iLoop=0; iLoop<nLoop; iLoop++){ nTo = 0; for(ii=0, pFrom=aFrom; ii<nFrom; ii++, pFrom++){ for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){ Bitmask maskNew; Bitmask revMask = 0; 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( isOrdered<0 ){ isOrdered = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags, iLoop, pWLoop, &revMask); if( isOrdered>=0 && isOrdered<nOrderBy ){ /* TUNING: Estimated cost of sorting cost as roughly N*log(N). ** If some but not all of the columns are in sorted order, then ** scale down the log(N) term. */ LogEst rScale = sqlite3LogEst((nOrderBy-isOrdered)*100/nOrderBy); LogEst rSortCost = nRowEst + estLog(nRowEst) + rScale - 66; /* TUNING: The cost of implementing DISTINCT using a B-TREE is ** also N*log(N) but it has a larger constant of proportionality. ** Multiply by 3.0. */ if( pWInfo->wctrlFlags & 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; jj<nTo; jj++, pTo++){ if( pTo->maskLoop==maskNew && ((pTo->isOrdered^isOrdered)&80)==0 && ((pTo->rCost<=rCost && pTo->nRow<=nOut) || (pTo->rCost>=rCost && pTo->nRow>=nOut)) ){ testcase( jj==nTo-1 ); break; } } if( jj>=nTo ){ 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, isOrdered>=0 ? isOrdered+'0' : '?'); } #endif continue; } /* Add a new Path to the aTo[] set */ if( nTo<mxChoice ){ /* Increase the size of the aTo set by one */ jj = nTo++; }else{ /* New path replaces the prior worst to keep count below mxChoice */ jj = mxI; } 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, 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, isOrdered>=0 ? isOrdered+'0' : '?'); sqlite3DebugPrintf(" vs %s cost=%-3d,%d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?'); } #endif testcase( pTo->rCost==rCost ); continue; } testcase( pTo->rCost==rCost+1 ); /* A new and better score for a previously created equivalent path */ #ifdef WHERETRACE_ENABLED /* 0x4 */ if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( "Update %s cost=%-3d,%3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, isOrdered>=0 ? isOrdered+'0' : '?'); sqlite3DebugPrintf(" was %s cost=%-3d,%3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, 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->isOrdered = isOrdered; memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop); pTo->aLoop[iLoop] = pWLoop; if( nTo>=mxChoice ){ mxI = 0; mxCost = aTo[0].rCost; mxOut = aTo[0].nRow; |
︙ | ︙ | |||
5145 5146 5147 5148 5149 5150 5151 | #ifdef WHERETRACE_ENABLED /* >=2 */ if( sqlite3WhereTrace>=2 ){ sqlite3DebugPrintf("---- after round %d ----\n", iLoop); for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, | | | | 5154 5155 5156 5157 5158 5159 5160 5161 5162 5163 5164 5165 5166 5167 5168 5169 | #ifdef WHERETRACE_ENABLED /* >=2 */ if( sqlite3WhereTrace>=2 ){ sqlite3DebugPrintf("---- after round %d ----\n", iLoop); for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?'); if( pTo->isOrdered>0 ){ sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); }else{ sqlite3DebugPrintf("\n"); } } } #endif |
︙ | ︙ | |||
5189 5190 5191 5192 5193 5194 5195 | && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 && pWInfo->eDistinct==WHERE_DISTINCT_NOOP && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pResultSet, pFrom, WHERE_DISTINCTBY, nLoop-1, pFrom->aLoop[nLoop-1], ¬Used); | > | | > | > | > | > | 5198 5199 5200 5201 5202 5203 5204 5205 5206 5207 5208 5209 5210 5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 | && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 && pWInfo->eDistinct==WHERE_DISTINCT_NOOP && nRowEst ){ Bitmask notUsed; int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pResultSet, pFrom, WHERE_DISTINCTBY, nLoop-1, pFrom->aLoop[nLoop-1], ¬Used); if( rc==pWInfo->pResultSet->nExpr ){ pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } } if( pWInfo->pOrderBy ){ if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){ if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){ pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } }else{ pWInfo->nOBSat = pFrom->isOrdered; if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0; pWInfo->revMask = pFrom->revLoop; } } pWInfo->nRowOut = pFrom->nRow; /* Free temporary memory and return success */ sqlite3DbFree(db, pSpace); |
︙ | ︙ | |||
5280 5281 5282 5283 5284 5285 5286 | } if( pLoop->wsFlags ){ pLoop->nOut = (LogEst)1; pWInfo->a[0].pWLoop = pLoop; pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur); pWInfo->a[0].iTabCur = iCur; pWInfo->nRowOut = 1; | | | 5294 5295 5296 5297 5298 5299 5300 5301 5302 5303 5304 5305 5306 5307 5308 | } if( pLoop->wsFlags ){ 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->nOBSat = pWInfo->pOrderBy->nExpr; if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } #ifdef SQLITE_DEBUG pLoop->cId = '0'; #endif return 1; |
︙ | ︙ | |||
5384 5385 5386 5387 5388 5389 5390 | ** be used to compute the appropriate cursor depending on which index is ** used. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* FROM clause: A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ | | | 5398 5399 5400 5401 5402 5403 5404 5405 5406 5407 5408 5409 5410 5411 5412 | ** be used to compute the appropriate cursor depending on which index is ** used. */ 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 (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 */ int nTabList; /* Number of elements in pTabList */ WhereInfo *pWInfo; /* Will become the return value of this function */ |
︙ | ︙ | |||
5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 | sqlite3 *db; /* Database connection */ int rc; /* Return code */ /* Variable initialization */ db = pParse->db; memset(&sWLB, 0, sizeof(sWLB)); sWLB.pOrderBy = pOrderBy; /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ if( OptimizationDisabled(db, SQLITE_DistinctOpt) ){ wctrlFlags &= ~WHERE_WANT_DISTINCT; } | > > > > | 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 | sqlite3 *db; /* Database connection */ int rc; /* Return code */ /* 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) ){ wctrlFlags &= ~WHERE_WANT_DISTINCT; } |
︙ | ︙ | |||
5484 5485 5486 5487 5488 5489 5490 | sWLB.pWC->a[ii].wtFlags |= TERM_CODED; } } /* Special case: No FROM clause */ if( nTabList==0 ){ | | | 5502 5503 5504 5505 5506 5507 5508 5509 5510 5511 5512 5513 5514 5515 5516 | sWLB.pWC->a[ii].wtFlags |= TERM_CODED; } } /* Special case: No FROM clause */ if( nTabList==0 ){ if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr; if( wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } } /* Assign a bit from the bitmask to every term in the FROM clause. ** |
︙ | ︙ | |||
5595 5596 5597 5598 5599 5600 5601 | if( pParse->nErr || NEVER(db->mallocFailed) ){ goto whereBeginError; } #ifdef WHERETRACE_ENABLED /* !=0 */ if( sqlite3WhereTrace ){ int ii; sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut); | | | | 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 5628 | if( pParse->nErr || NEVER(db->mallocFailed) ){ goto whereBeginError; } #ifdef WHERETRACE_ENABLED /* !=0 */ if( sqlite3WhereTrace ){ int ii; sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut); if( pWInfo->nOBSat>0 ){ sqlite3DebugPrintf(" ORDERBY=%d,0x%llx", pWInfo->nOBSat, pWInfo->revMask); } switch( pWInfo->eDistinct ){ case WHERE_DISTINCT_UNIQUE: { sqlite3DebugPrintf(" DISTINCT=unique"); break; } case WHERE_DISTINCT_ORDERED: { |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
117 118 119 120 121 122 123 | u16 nEq; /* Number of equality constraints */ u16 nSkip; /* Number of initial index columns to skip */ 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 */ | | | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | u16 nEq; /* Number of equality constraints */ u16 nSkip; /* Number of initial index columns to skip */ 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 */ 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 */ u16 nLTerm; /* Number of entries in aLTerm[] */ /**** whereLoopXfer() copies fields above ***********************/ |
︙ | ︙ | |||
179 180 181 182 183 184 185 | ** at the end is the choosen query plan. */ 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 */ | | < | 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | ** at the end is the choosen query plan. */ 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 */ 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 ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by AND operators, |
︙ | ︙ | |||
394 395 396 397 398 399 400 | SrcList *pTabList; /* List of tables in the join */ ExprList *pOrderBy; /* The ORDER BY clause or NULL */ 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() */ | | | 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 | SrcList *pTabList; /* List of tables in the join */ ExprList *pOrderBy; /* The ORDER BY clause or NULL */ 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() */ 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 */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ |
︙ | ︙ |
Changes to test/distinct.test.
︙ | ︙ | |||
158 159 160 161 162 163 164 | INSERT INTO t1 VALUES('a', 'b', 'c'); INSERT INTO t1 VALUES('A', 'B', 'C'); } foreach {tn sql temptables res} { 1 "a, b FROM t1" {} {A B a b} 2 "b, a FROM t1" {} {B A b a} | | | 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | INSERT INTO t1 VALUES('a', 'b', 'c'); INSERT INTO t1 VALUES('A', 'B', 'C'); } 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} 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} 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {b} } { |
︙ | ︙ |
Changes to test/orderby5.test.
︙ | ︙ | |||
60 61 62 63 64 65 66 | EXPLAIN QUERY PLAN SELECT DISTINCT c, b, a FROM t1 WHERE a=0; } {~/B-TREE/} do_execsql_test 1.7 { EXPLAIN QUERY PLAN SELECT DISTINCT c, b, a FROM t1 WHERE +a=0; } {/B-TREE/} | > > > > > | > > > > > > > > > > > | > > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | EXPLAIN QUERY PLAN SELECT DISTINCT c, b, a FROM t1 WHERE a=0; } {~/B-TREE/} do_execsql_test 1.7 { EXPLAIN QUERY PLAN SELECT DISTINCT c, b, a FROM t1 WHERE +a=0; } {/B-TREE/} # 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/} 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 { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c; |
︙ | ︙ |
Added test/orderby6.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 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 |
Changes to test/whereG.test.
︙ | ︙ | |||
91 92 93 94 95 96 97 | do_eqp_test whereG-1.5 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; | | | | 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | do_eqp_test whereG-1.5 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; } {/.*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 AND album.aid=track.aid; } {{Mass in B Minor, BWV 232}} do_eqp_test whereG-1.7 { 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|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) AND unlikely(album.aid=track.aid); } {{Mass in B Minor, BWV 232}} |
︙ | ︙ |