/ Check-in [98b63371]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix some corner case behavior in the new ORDER BY optimization logic. Remove the SQLITE_OrderByIdx bit from the SQLITE_TESTCTRL_OPTIMIZATIONS mask, since enabling it caused many TH3 tests to fail when the NO_OPT configuration parameter was engaged, and since there really isn't any need to turn that optimization off. The SQLITE_OrderByIdxJoin bit remains.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: 98b633717a1c9a08f6a1d00bc6bc891564ae7e9b
User & Date: drh 2012-09-27 23:27:23
Context
2012-09-28
00:44
Query planner enhancements to be more agressive about optimizing out ORDER BY clauses - in particular the query planner now has the ability to omit ORDER BY clauses that span multiple tables in a join. check-in: 1e874629 user: drh tags: trunk
2012-09-27
23:27
Fix some corner case behavior in the new ORDER BY optimization logic. Remove the SQLITE_OrderByIdx bit from the SQLITE_TESTCTRL_OPTIMIZATIONS mask, since enabling it caused many TH3 tests to fail when the NO_OPT configuration parameter was engaged, and since there really isn't any need to turn that optimization off. The SQLITE_OrderByIdxJoin bit remains. Closed-Leaf check-in: 98b63371 user: drh tags: qp-enhancements
19:53
More test cases an bug fixes for the ORDER BY optimization of joins. All veryquick tests now pass. check-in: 0d573320 user: drh tags: qp-enhancements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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  +  u8 dbOptFlags;                /* Flags to enable/disable optimizations */
   831    831     u8 autoCommit;                /* The auto-commit flag. */
   832    832     u8 temp_store;                /* 1: file 2: memory 0: default */
   833    833     u8 mallocFailed;              /* True if we have seen a malloc failure */
   834    834     u8 dfltLockMode;              /* Default locking-mode for attached dbs */
   835    835     signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
   836    836     u8 suppressErr;               /* Do not issue error messages if true */
   837    837     u8 vtabOnConflict;            /* Value to return for s3_vtab_on_conflict() */
................................................................................
   967    967   #define SQLITE_QueryFlattener 0x0001   /* Query flattening */
   968    968   #define SQLITE_ColumnCache    0x0002   /* Column cache */
   969    969   #define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
   970    970   #define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
   971    971   #define SQLITE_IdxRealAsInt   0x0010   /* Store REAL as INT in indices */
   972    972   #define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
   973    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 */
          974  +#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
          975  +#define SQLITE_AllOpts        0x00ff   /* All optimizations */
   977    976   
   978    977   /*
   979    978   ** Macros for testing whether or not optimizations are enabled or disabled.
   980    979   */
   981    980   #ifndef SQLITE_OMIT_BUILTIN_TEST
   982    981   #define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
   983    982   #define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

Changes to src/test1.c.

  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   5944       { "order-by-idx-join",SQLITE_OrderByIdxJoin },
  5946   5945     };
  5947   5946   
  5948   5947     if( objc!=4 ){
  5949   5948       Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
  5950   5949       return TCL_ERROR;
  5951   5950     }

Changes to src/where.c.

  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     int nEqOneRow;                /* Idx columns that ref unique values */
  1649   1649   
  1650         -  if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0;
  1651   1650     if( p->i==0 ){
  1652   1651       nPriorSat = 0;
  1653   1652       nEqOneRow = nEqCol;
  1654   1653     }else{
  1655   1654       if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0;
  1656   1655       nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
  1657   1656       sortOrder = bOuterRev;
................................................................................
  3150   3149             bInEst = 1;
  3151   3150           }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  3152   3151             /* "x IN (value, value, ...)" */
  3153   3152             nInMul *= pExpr->x.pList->nExpr;
  3154   3153           }
  3155   3154         }else if( pTerm->eOperator & WO_ISNULL ){
  3156   3155           wsFlags |= WHERE_COLUMN_NULL;
         3156  +        if( nEq==nOrdered ) nOrdered++;
  3157   3157         }else if( bSort && nEq==nOrdered && isOrderedTerm(p, pTerm, &bRev) ){
  3158   3158           nOrdered++;
  3159   3159         }
  3160   3160   #ifdef SQLITE_ENABLE_STAT3
  3161   3161         if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm;
  3162   3162   #endif
  3163   3163         used |= pTerm->prereqRight;
................................................................................
  3212   3212         testcase( bRev==2 );
  3213   3213         nOBSat = isSortingIndex(p, pProbe, iCur, nOrdered,
  3214   3214                                 wsFlags, bRev&1, &bRev);
  3215   3215         if( nOrderBy==nOBSat ){
  3216   3216           bSort = 0;
  3217   3217           wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY;
  3218   3218         }
  3219         -      if( bRev ) wsFlags |= WHERE_REVERSE;
         3219  +      if( bRev & 1 ) wsFlags |= WHERE_REVERSE;
  3220   3220       }
  3221   3221   
  3222   3222       /* If there is a DISTINCT qualifier and this index will scan rows in
  3223   3223       ** order of the DISTINCT expressions, clear bDist and set the appropriate
  3224   3224       ** flags in wsFlags. */
  3225   3225       if( bDist
  3226   3226        && isDistinctIndex(pParse, pWC, pProbe, iCur, p->pDistinct, nEq)

Changes to test/collate4.test.

   385    385         DROP INDEX collate4i1;
   386    386         CREATE INDEX collate4i1 ON collate4t1(a);
   387    387       }
   388    388       count {
   389    389         SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
   390    390          ORDER BY rowid
   391    391       }
   392         -  } {a A 5}
          392  +  } {a A 6}
   393    393     do_test collate4-2.1.8 {
   394    394       count {
   395    395         SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
   396    396       }
   397    397     } {a A 5}
   398    398     do_test collate4-2.1.9 {
   399    399       execsql {

Changes to test/where.test.

   379    379         SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
   380    380       }
   381    381     } {1 0 4 2 1 9 3 1 16 102}
   382    382     do_test where-5.3 {
   383    383       count {
   384    384         SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
   385    385       }
   386         -  } {1 0 4 2 1 9 3 1 16 13}
          386  +  } {1 0 4 2 1 9 3 1 16 14}
   387    387     do_test where-5.4 {
   388    388       count {
   389    389         SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
   390    390       }
   391    391     } {1 0 4 2 1 9 3 1 16 102}
   392    392     do_test where-5.5 {
   393    393       count {