/ Check-in [d929df9b]
Login

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

Overview
Comment:Add the ability to disable the omit-join-table optimization for testing purposes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-join-table-opt
Files: files | file ages | folders
SHA1: d929df9b1ba214c27d8c437099a53ee9b07aa169
User & Date: drh 2013-06-22 15:44:26
Context
2013-06-26
11:49
Omit tables from the FROM clause of a join if their presence makes no difference in the final output. check-in: 6505e2ab user: drh tags: trunk
2013-06-22
15:44
Add the ability to disable the omit-join-table optimization for testing purposes. Closed-Leaf check-in: d929df9b user: drh tags: omit-join-table-opt
2013-06-21
02:15
Only eliminate inner loops of a JOIN if they are the RHS of a LEFT JOIN and if they give no more than a single result. This appears to give correct answers in all cases. check-in: d7a25cc7 user: drh tags: omit-join-table-opt
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  1020   1020   #define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
  1021   1021   #define SQLITE_IdxRealAsInt   0x0010   /* Store REAL as INT in indices */
  1022   1022   #define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
  1023   1023   #define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
  1024   1024   #define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
  1025   1025   #define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
  1026   1026   #define SQLITE_Transitive     0x0200   /* Transitive constraints */
         1027  +#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
  1027   1028   #define SQLITE_AllOpts        0xffff   /* All optimizations */
  1028   1029   
  1029   1030   /*
  1030   1031   ** Macros for testing whether or not optimizations are enabled or disabled.
  1031   1032   */
  1032   1033   #ifndef SQLITE_OMIT_BUILTIN_TEST
  1033   1034   #define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)

Changes to src/where.c.

  5785   5785       sqlite3DebugPrintf("\n");
  5786   5786       for(ii=0; ii<pWInfo->nLevel; ii++){
  5787   5787         whereLoopPrint(pWInfo->a[ii].pWLoop, pTabList);
  5788   5788       }
  5789   5789     }
  5790   5790   #endif
  5791   5791     /* Attempt to omit tables from the join that do not effect the result */
  5792         -  if( pResultSet!=0 && pWInfo->nLevel>=2 ){
         5792  +  if( pWInfo->nLevel>=2
         5793  +   && pResultSet!=0
         5794  +   && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
         5795  +  ){
  5793   5796       Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
  5794   5797       if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
  5795   5798       while( pWInfo->nLevel>=2 ){
  5796   5799         pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
  5797   5800         if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
  5798   5801         if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
  5799   5802          && (pLoop->wsFlags & WHERE_ONEROW)==0