SQLite

Check-in [d7a25cc797]
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
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.306
Context
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)
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: ca839723a2 user: drh tags: omit-join-table-opt)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
5790
5791
5792
5793
5794
5795
5796

5797
5798
5799
5800



5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813

#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( ((wctrlFlags & WHERE_WANT_DISTINCT)!=0
           || (pLoop->wsFlags & WHERE_ONEROW)!=0)
       && (tabUsed & pLoop->maskSelf)==0
      ){



        WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
        pWInfo->nLevel--;
        nTabList--;
      }else{
        break;
      }
    }
  }
  WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;

  /* If the caller is an UPDATE or DELETE statement that is requesting
  ** to use a one-pass algorithm, determine if this is appropriate.








>
|
|
<

>
>
>
|
|
|
<
<
<








5790
5791
5792
5793
5794
5795
5796
5797
5798
5799

5800
5801
5802
5803
5804
5805
5806



5807
5808
5809
5810
5811
5812
5813

#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

      ){
        break;
      }
      if( (tabUsed & pLoop->maskSelf)!=0 ) break;
      WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
      pWInfo->nLevel--;
      nTabList--;



    }
  }
  WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
  pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;

  /* If the caller is an UPDATE or DELETE statement that is requesting
  ** to use a one-pass algorithm, determine if this is appropriate.
︙︙