Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add more bits to the bit vector that is used to disable optimizations for built-in test. Add specific bit patterns to disable ORDER BY using an index in general and for joins. Use macros to test for bits in the disabled-optimization bit vector, in order to make the code clearer. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | qp-enhancements |
| Files: | files | file ages | folders |
| SHA1: |
d2fcba1e143beca8c45724d210887065 |
| User & Date: | drh 2012-09-27 15:05:54 |
Context
|
2012-09-27
| ||
| 17:31 | Test cases and bug fixes applied to the ORDER BY optimization for joins. Some test cases fail, but except for the new orderby1.test failures, all failures appear to be issues with the tests, not with the core code. check-in: 75cda864 user: drh tags: qp-enhancements | |
| 15:05 | Add more bits to the bit vector that is used to disable optimizations for built-in test. Add specific bit patterns to disable ORDER BY using an index in general and for joins. Use macros to test for bits in the disabled-optimization bit vector, in order to make the code clearer. check-in: d2fcba1e user: drh tags: qp-enhancements | |
| 14:11 | Enable ORDER BY clauses that span joins to be optimized out. check-in: c29538f9 user: drh tags: qp-enhancements | |
Changes
Changes to src/delete.c.
634 634 }else{ 635 635 sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j); 636 636 sqlite3ColumnDefault(v, pTab, idx, -1); 637 637 } 638 638 } 639 639 if( doMakeRec ){ 640 640 const char *zAff; 641 - if( pTab->pSelect || (pParse->db->flags & SQLITE_IdxRealAsInt)!=0 ){ 641 + if( pTab->pSelect 642 + || OptimizationDisabled(pParse->db, SQLITE_IdxRealAsInt) 643 + ){ 642 644 zAff = 0; 643 645 }else{ 644 646 zAff = sqlite3IndexAffinityStr(v, pIdx); 645 647 } 646 648 sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut); 647 649 sqlite3VdbeChangeP4(v, -1, zAff, P4_TRANSIENT); 648 650 } 649 651 sqlite3ReleaseTempRange(pParse, regBase, nCol+1); 650 652 return regBase; 651 653 }
Changes to src/expr.c.
2062 2062 assert( iReg>0 ); /* Register numbers are always positive */ 2063 2063 assert( iCol>=-1 && iCol<32768 ); /* Finite column numbers */ 2064 2064 2065 2065 /* The SQLITE_ColumnCache flag disables the column cache. This is used 2066 2066 ** for testing only - to verify that SQLite always gets the same answer 2067 2067 ** with and without the column cache. 2068 2068 */ 2069 - if( pParse->db->flags & SQLITE_ColumnCache ) return; 2069 + if( OptimizationDisabled(pParse->db, SQLITE_ColumnCache) ) return; 2070 2070 2071 2071 /* First replace any existing entry. 2072 2072 ** 2073 2073 ** Actually, the way the column cache is currently used, we are guaranteed 2074 2074 ** that the object will never already be in cache. Verify this guarantee. 2075 2075 */ 2076 2076 #ifndef NDEBUG ................................................................................ 3378 3378 ** interface. This allows test logic to verify that the same answer is 3379 3379 ** obtained for queries regardless of whether or not constants are 3380 3380 ** precomputed into registers or if they are inserted in-line. 3381 3381 */ 3382 3382 void sqlite3ExprCodeConstants(Parse *pParse, Expr *pExpr){ 3383 3383 Walker w; 3384 3384 if( pParse->cookieGoto ) return; 3385 - if( (pParse->db->flags & SQLITE_FactorOutConst)!=0 ) return; 3385 + if( OptimizationDisabled(pParse->db, SQLITE_FactorOutConst) ) return; 3386 3386 w.xExprCallback = evalConstExpr; 3387 3387 w.xSelectCallback = 0; 3388 3388 w.pParse = pParse; 3389 3389 sqlite3WalkExpr(&w, pExpr); 3390 3390 } 3391 3391 3392 3392
Changes to src/main.c.
3014 3014 ** operation N should be 0. The idea is that a test program (like the 3015 3015 ** SQL Logic Test or SLT test module) can run the same SQL multiple times 3016 3016 ** with various optimizations disabled to verify that the same answer 3017 3017 ** is obtained in every case. 3018 3018 */ 3019 3019 case SQLITE_TESTCTRL_OPTIMIZATIONS: { 3020 3020 sqlite3 *db = va_arg(ap, sqlite3*); 3021 - int x = va_arg(ap,int); 3022 - db->flags = (x & SQLITE_OptMask) | (db->flags & ~SQLITE_OptMask); 3021 + db->dbOptFlags = (u16)(va_arg(ap, int) & 0xffff); 3023 3022 break; 3024 3023 } 3025 3024 3026 3025 #ifdef SQLITE_N_KEYWORD 3027 3026 /* sqlite3_test_control(SQLITE_TESTCTRL_ISKEYWORD, const char *zWord) 3028 3027 ** 3029 3028 ** If zWord is a keyword recognized by the parser, then return the
Changes to src/select.c.
2805 2805 struct SrcList_item *pSubitem; /* The subquery */ 2806 2806 sqlite3 *db = pParse->db; 2807 2807 2808 2808 /* Check to see if flattening is permitted. Return 0 if not. 2809 2809 */ 2810 2810 assert( p!=0 ); 2811 2811 assert( p->pPrior==0 ); /* Unable to flatten compound queries */ 2812 - if( db->flags & SQLITE_QueryFlattener ) return 0; 2812 + if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0; 2813 2813 pSrc = p->pSrc; 2814 2814 assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); 2815 2815 pSubitem = &pSrc->a[iFrom]; 2816 2816 iParent = pSubitem->iCursor; 2817 2817 pSub = pSubitem->pSelect; 2818 2818 assert( pSub!=0 ); 2819 2819 if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ ................................................................................ 4008 4008 ** identical, then disable the ORDER BY clause since the GROUP BY 4009 4009 ** will cause elements to come out in the correct order. This is 4010 4010 ** an optimization - the correct answer should result regardless. 4011 4011 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER 4012 4012 ** to disable this optimization for testing purposes. 4013 4013 */ 4014 4014 if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0 4015 - && (db->flags & SQLITE_GroupByOrder)==0 ){ 4015 + && OptimizationEnabled(db, SQLITE_GroupByOrder) ){ 4016 4016 pOrderBy = 0; 4017 4017 } 4018 4018 4019 4019 /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 4020 4020 ** if the select-list is the same as the ORDER BY list, then this query 4021 4021 ** can be rewritten as a GROUP BY. In other words, this: 4022 4022 **
Changes to src/sqliteInt.h.
823 823 Db *aDb; /* All backends */ 824 824 int nDb; /* Number of backends currently in use */ 825 825 int flags; /* Miscellaneous flags. See below */ 826 826 i64 lastRowid; /* ROWID of most recent insert (see above) */ 827 827 unsigned int openFlags; /* Flags passed to sqlite3_vfs.xOpen() */ 828 828 int errCode; /* Most recent error code (SQLITE_*) */ 829 829 int errMask; /* & result codes with this before returning */ 830 + u16 dbOptFlags; /* Flags to enable/disable optimizations */ 830 831 u8 autoCommit; /* The auto-commit flag. */ 831 832 u8 temp_store; /* 1: file 2: memory 0: default */ 832 833 u8 mallocFailed; /* True if we have seen a malloc failure */ 833 834 u8 dfltLockMode; /* Default locking-mode for attached dbs */ 834 835 signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ 835 836 u8 suppressErr; /* Do not issue error messages if true */ 836 837 u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ ................................................................................ 927 928 ** A macro to discover the encoding of a database. 928 929 */ 929 930 #define ENC(db) ((db)->aDb[0].pSchema->enc) 930 931 931 932 /* 932 933 ** Possible values for the sqlite3.flags. 933 934 */ 934 -#define SQLITE_VdbeTrace 0x00000100 /* True to trace VDBE execution */ 935 -#define SQLITE_InternChanges 0x00000200 /* Uncommitted Hash table changes */ 936 -#define SQLITE_FullColNames 0x00000400 /* Show full column names on SELECT */ 937 -#define SQLITE_ShortColNames 0x00000800 /* Show short columns names */ 938 -#define SQLITE_CountRows 0x00001000 /* Count rows changed by INSERT, */ 935 +#define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ 936 +#define SQLITE_InternChanges 0x00000002 /* Uncommitted Hash table changes */ 937 +#define SQLITE_FullColNames 0x00000004 /* Show full column names on SELECT */ 938 +#define SQLITE_ShortColNames 0x00000008 /* Show short columns names */ 939 +#define SQLITE_CountRows 0x00000010 /* Count rows changed by INSERT, */ 939 940 /* DELETE, or UPDATE and return */ 940 941 /* the count using a callback. */ 941 -#define SQLITE_NullCallback 0x00002000 /* Invoke the callback once if the */ 942 +#define SQLITE_NullCallback 0x00000020 /* Invoke the callback once if the */ 942 943 /* result set is empty */ 943 -#define SQLITE_SqlTrace 0x00004000 /* Debug print SQL as it executes */ 944 -#define SQLITE_VdbeListing 0x00008000 /* Debug listings of VDBE programs */ 945 -#define SQLITE_WriteSchema 0x00010000 /* OK to update SQLITE_MASTER */ 946 - /* 0x00020000 Unused */ 947 -#define SQLITE_IgnoreChecks 0x00040000 /* Do not enforce check constraints */ 948 -#define SQLITE_ReadUncommitted 0x0080000 /* For shared-cache mode */ 949 -#define SQLITE_LegacyFileFmt 0x00100000 /* Create new databases in format 1 */ 950 -#define SQLITE_FullFSync 0x00200000 /* Use full fsync on the backend */ 951 -#define SQLITE_CkptFullFSync 0x00400000 /* Use full fsync for checkpoint */ 952 -#define SQLITE_RecoveryMode 0x00800000 /* Ignore schema errors */ 953 -#define SQLITE_ReverseOrder 0x01000000 /* Reverse unordered SELECTs */ 954 -#define SQLITE_RecTriggers 0x02000000 /* Enable recursive triggers */ 955 -#define SQLITE_ForeignKeys 0x04000000 /* Enforce foreign key constraints */ 956 -#define SQLITE_AutoIndex 0x08000000 /* Enable automatic indexes */ 957 -#define SQLITE_PreferBuiltin 0x10000000 /* Preference to built-in funcs */ 958 -#define SQLITE_LoadExtension 0x20000000 /* Enable load_extension */ 959 -#define SQLITE_EnableTrigger 0x40000000 /* True to enable triggers */ 960 - 961 -/* 962 -** Bits of the sqlite3.flags field that are used by the 963 -** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface. 964 -** These must be the low-order bits of the flags field. 965 -*/ 966 -#define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ 967 -#define SQLITE_ColumnCache 0x02 /* Disable the column cache */ 968 -#define SQLITE_GroupByOrder 0x04 /* Disable GROUPBY cover of ORDERBY */ 969 -#define SQLITE_FactorOutConst 0x08 /* Disable factoring out constants */ 970 -#define SQLITE_IdxRealAsInt 0x10 /* Store REAL as INT in indices */ 971 -#define SQLITE_DistinctOpt 0x20 /* DISTINCT using indexes */ 972 -#define SQLITE_CoverIdxScan 0x40 /* Disable covering index scans */ 973 -#define SQLITE_OptMask 0xff /* Mask of all disablable opts */ 944 +#define SQLITE_SqlTrace 0x00000040 /* Debug print SQL as it executes */ 945 +#define SQLITE_VdbeListing 0x00000080 /* Debug listings of VDBE programs */ 946 +#define SQLITE_WriteSchema 0x00000100 /* OK to update SQLITE_MASTER */ 947 + /* 0x00000200 Unused */ 948 +#define SQLITE_IgnoreChecks 0x00000400 /* Do not enforce check constraints */ 949 +#define SQLITE_ReadUncommitted 0x0000800 /* For shared-cache mode */ 950 +#define SQLITE_LegacyFileFmt 0x00001000 /* Create new databases in format 1 */ 951 +#define SQLITE_FullFSync 0x00002000 /* Use full fsync on the backend */ 952 +#define SQLITE_CkptFullFSync 0x00004000 /* Use full fsync for checkpoint */ 953 +#define SQLITE_RecoveryMode 0x00008000 /* Ignore schema errors */ 954 +#define SQLITE_ReverseOrder 0x00010000 /* Reverse unordered SELECTs */ 955 +#define SQLITE_RecTriggers 0x00020000 /* Enable recursive triggers */ 956 +#define SQLITE_ForeignKeys 0x00040000 /* Enforce foreign key constraints */ 957 +#define SQLITE_AutoIndex 0x00080000 /* Enable automatic indexes */ 958 +#define SQLITE_PreferBuiltin 0x00100000 /* Preference to built-in funcs */ 959 +#define SQLITE_LoadExtension 0x00200000 /* Enable load_extension */ 960 +#define SQLITE_EnableTrigger 0x00400000 /* True to enable triggers */ 961 + 962 +/* 963 +** Bits of the sqlite3.dbOptFlags field that are used by the 964 +** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to 965 +** selectively disable various optimizations. 966 +*/ 967 +#define SQLITE_QueryFlattener 0x0001 /* Query flattening */ 968 +#define SQLITE_ColumnCache 0x0002 /* Column cache */ 969 +#define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */ 970 +#define SQLITE_FactorOutConst 0x0008 /* Constant factoring */ 971 +#define SQLITE_IdxRealAsInt 0x0010 /* Store REAL as INT in indices */ 972 +#define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ 973 +#define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ 974 +#define SQLITE_OrderByIdx 0x0180 /* ORDER BY using indices */ 975 +#define SQLITE_OrderByIdxJoin 0x0100 /* ORDER BY of joins via index */ 976 +#define SQLITE_AllOpts 0x01ff /* All optimizations */ 977 + 978 +/* 979 +** Macros for testing whether or not optimizations are enabled or disabled. 980 +*/ 981 +#ifndef SQLITE_OMIT_BUILTIN_TEST 982 +#define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) 983 +#define OptimizationEnabled(db, mask) (((db)->dbOptFlags&(mask))==0) 984 +#else 985 +#define OptimizationDisabled(db, mask) 0 986 +#define OptimizationEnabled(db, mask) 1 987 +#endif 974 988 975 989 /* 976 990 ** Possible values for the sqlite.magic field. 977 991 ** The numbers are obtained at random and have no special meaning, other 978 992 ** than being distinct from one another. 979 993 */ 980 994 #define SQLITE_MAGIC_OPEN 0xa029a697 /* Database is open */
Changes to src/test1.c.
5929 5929 const char *zOpt; 5930 5930 int onoff; 5931 5931 int mask = 0; 5932 5932 static const struct { 5933 5933 const char *zOptName; 5934 5934 int mask; 5935 5935 } aOpt[] = { 5936 - { "all", SQLITE_OptMask }, 5936 + { "all", SQLITE_AllOpts }, 5937 5937 { "query-flattener", SQLITE_QueryFlattener }, 5938 5938 { "column-cache", SQLITE_ColumnCache }, 5939 5939 { "groupby-order", SQLITE_GroupByOrder }, 5940 5940 { "factor-constants", SQLITE_FactorOutConst }, 5941 5941 { "real-as-int", SQLITE_IdxRealAsInt }, 5942 5942 { "distinct-opt", SQLITE_DistinctOpt }, 5943 5943 { "cover-idx-scan", SQLITE_CoverIdxScan }, 5944 + { "order-by-idx", SQLITE_OrderByIdx }, 5945 + { "order-by-idx-join",SQLITE_OrderByIdxJoin }, 5944 5946 }; 5945 5947 5946 5948 if( objc!=4 ){ 5947 5949 Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); 5948 5950 return TCL_ERROR; 5949 5951 } 5950 5952 if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
Changes to src/where.c.
1642 1642 struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ 1643 1643 ExprList *pOrderBy; /* The ORDER BY clause */ 1644 1644 Parse *pParse = p->pParse; /* Parser context */ 1645 1645 sqlite3 *db = pParse->db; /* Database connection */ 1646 1646 int nPriorSat; /* ORDER BY terms satisfied by outer loops */ 1647 1647 int seenRowid = 0; /* True if an ORDER BY rowid term is seen */ 1648 1648 1649 + if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0; 1649 1650 if( p->i==0 ){ 1650 1651 nPriorSat = 0; 1651 1652 }else{ 1653 + if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0; 1652 1654 nPriorSat = p->aLevel[p->i-1].plan.nOBSat; 1653 1655 } 1654 1656 if( p->i>0 && nEqCol==0 /*&& !allOuterLoopsUnique(p)*/ ) return nPriorSat; 1655 1657 pOrderBy = p->pOrderBy; 1656 1658 if( !pOrderBy ) return nPriorSat; 1657 1659 if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat; 1658 1660 if( pIdx->bUnordered ) return nPriorSat; ................................................................................ 2875 2877 static int isOrderedColumn(WhereBestIdx *p, int iTab, int iCol, int *pbRev){ 2876 2878 int i, j; 2877 2879 WhereLevel *pLevel = &p->aLevel[p->i-1]; 2878 2880 Index *pIdx; 2879 2881 u8 sortOrder; 2880 2882 for(i=p->i-1; i>=0; i--, pLevel--){ 2881 2883 if( pLevel->iTabCur!=iTab ) continue; 2882 - if( (pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE))!=0 ){ 2883 - if( iCol!=(-1) ) return 0; 2884 - sortOrder = 0; 2885 - }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ 2884 + if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ 2886 2885 pIdx = pLevel->plan.u.pIdx; 2887 2886 for(j=0; j<pIdx->nColumn; j++){ 2888 2887 if( iCol==pIdx->aiColumn[j] ) break; 2889 2888 } 2890 2889 if( j>=pIdx->nColumn ) return 0; 2891 2890 sortOrder = pIdx->aSortOrder[j]; 2892 2891 }else{ ................................................................................ 3285 3284 ** not give us data on the relative sizes of table and index records. 3286 3285 ** So this computation assumes table records are about twice as big 3287 3286 ** as index records 3288 3287 */ 3289 3288 if( wsFlags==WHERE_IDX_ONLY 3290 3289 && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 3291 3290 && sqlite3GlobalConfig.bUseCis 3292 -#ifndef SQLITE_OMIT_BUILTIN_TEST 3293 - && (pParse->db->flags & SQLITE_CoverIdxScan)==0 3294 -#endif 3291 + && OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan) 3295 3292 ){ 3296 3293 /* This index is not useful for indexing, but it is a covering index. 3297 3294 ** A full-scan of the index might be a little faster than a full-scan 3298 3295 ** of the table, so give this case a cost slightly less than a table 3299 3296 ** scan. */ 3300 3297 cost = aiRowEst[0]*3 + pProbe->nColumn; 3301 3298 wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE; ................................................................................ 4837 4834 pWInfo->wctrlFlags = wctrlFlags; 4838 4835 pWInfo->savedNQueryLoop = pParse->nQueryLoop; 4839 4836 pMaskSet = (WhereMaskSet*)&sWBI.pWC[1]; 4840 4837 sWBI.aLevel = pWInfo->a; 4841 4838 4842 4839 /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via 4843 4840 ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ 4844 - if( db->flags & SQLITE_DistinctOpt ) pDistinct = 0; 4841 + if( OptimizationDisabled(db, SQLITE_DistinctOpt) ) pDistinct = 0; 4845 4842 4846 4843 /* Split the WHERE clause into separate subexpressions where each 4847 4844 ** subexpression is separated by an AND operator. 4848 4845 */ 4849 4846 initMaskSet(pMaskSet); 4850 4847 whereClauseInit(sWBI.pWC, pParse, pMaskSet, wctrlFlags); 4851 4848 sqlite3ExprCodeConstants(pParse, pWhere);