SQLite

Check-in [d929df9b1b]
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
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.893
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: 6505e2ab02 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: d929df9b1b 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: d7a25cc797 user: drh tags: omit-join-table-opt)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/sqliteInt.h.
1020
1021
1022
1023
1024
1025
1026

1027
1028
1029
1030
1031
1032
1033
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_IdxRealAsInt   0x0010   /* Store REAL as INT in indices */
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */

#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#ifndef SQLITE_OMIT_BUILTIN_TEST
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)







>







1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_IdxRealAsInt   0x0010   /* Store REAL as INT in indices */
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#ifndef SQLITE_OMIT_BUILTIN_TEST
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
Changes to src/where.c.
5785
5786
5787
5788
5789
5790
5791
5792



5793
5794
5795
5796
5797
5798
5799
    sqlite3DebugPrintf("\n");
    for(ii=0; ii<pWInfo->nLevel; ii++){
      whereLoopPrint(pWInfo->a[ii].pWLoop, pTabList);
    }
  }
#endif
  /* Attempt to omit tables from the join that do not effect the result */
  if( pResultSet!=0 && pWInfo->nLevel>=2 ){



    Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
    if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
    while( pWInfo->nLevel>=2 ){
      pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
      if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
      if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
       && (pLoop->wsFlags & WHERE_ONEROW)==0







|
>
>
>







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