/ Check-in [d7a25cc7]
Login

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

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-join-table-opt
Files: files | file ages | folders
SHA1: d7a25cc79794817504ca1a4262008a68b2a4dece
User & Date: drh 2013-06-21 02:15:48
Context
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
02:05
Attempt to disable inner loops of a join that do not generate output. This does not work, since the inner loops might run zero times and thus inhibit all output. Needs to be enhanced to work only for LEFT JOINs or when we know that the inner loop will always run at least once. check-in: ca839723 user: drh tags: omit-join-table-opt
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  5790   5790   #endif
  5791   5791     /* Attempt to omit tables from the join that do not effect the result */
  5792   5792     if( pResultSet!=0 && pWInfo->nLevel>=2 ){
  5793   5793       Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
  5794   5794       if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
  5795   5795       while( pWInfo->nLevel>=2 ){
  5796   5796         pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
  5797         -      if( ((wctrlFlags & WHERE_WANT_DISTINCT)!=0
  5798         -           || (pLoop->wsFlags & WHERE_ONEROW)!=0)
  5799         -       && (tabUsed & pLoop->maskSelf)==0
         5797  +      if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
         5798  +      if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
         5799  +       && (pLoop->wsFlags & WHERE_ONEROW)==0
  5800   5800         ){
  5801         -        WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
  5802         -        pWInfo->nLevel--;
  5803         -        nTabList--;
  5804         -      }else{
  5805   5801           break;
  5806   5802         }
         5803  +      if( (tabUsed & pLoop->maskSelf)!=0 ) break;
         5804  +      WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
         5805  +      pWInfo->nLevel--;
         5806  +      nTabList--;
  5807   5807       }
  5808   5808     }
  5809   5809     WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  5810   5810     pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
  5811   5811   
  5812   5812     /* If the caller is an UPDATE or DELETE statement that is requesting
  5813   5813     ** to use a one-pass algorithm, determine if this is appropriate.