/ Check-in [d2fcba1e]
Login

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: d2fcba1e143beca8c45724d2108870657c269e17
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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);