/ Check-in [0156f10e]
Login

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

Overview
Comment:When analyzing the right-hand side of IN operators to see if the IN operator can work with an index, be sure to decend into nested subqueries. Fix for ticket [1a1308d2538d7]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0156f10e23daca0e2504cd293a67ed0233d254d0
User & Date: drh 2011-09-16 17:43:06
Context
2011-09-16
19:04
Remove unreachable branches from the previous change. Add additional test cases. check-in: cf51ef8a user: drh tags: trunk
17:43
When analyzing the right-hand side of IN operators to see if the IN operator can work with an index, be sure to decend into nested subqueries. Fix for ticket [1a1308d2538d7] check-in: 0156f10e user: drh tags: trunk
16:00
Further streamlining of the subquery materializer. New test cases for ticket [002caede898a] check-in: ff8b76b2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   463    463       }
   464    464     }
   465    465     return mask;
   466    466   }
   467    467   static Bitmask exprSelectTableUsage(WhereMaskSet *pMaskSet, Select *pS){
   468    468     Bitmask mask = 0;
   469    469     while( pS ){
          470  +    SrcList *pSrc;
   470    471       mask |= exprListTableUsage(pMaskSet, pS->pEList);
   471    472       mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
   472    473       mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
   473    474       mask |= exprTableUsage(pMaskSet, pS->pWhere);
   474    475       mask |= exprTableUsage(pMaskSet, pS->pHaving);
          476  +    if( (pSrc = pS->pSrc)!=0 ){
          477  +      int i;
          478  +      for(i=0; i<pSrc->nSrc; i++){
          479  +        mask |= exprSelectTableUsage(pMaskSet, pSrc->a[i].pSelect);
          480  +        mask |= exprTableUsage(pMaskSet, pSrc->a[i].pOn);
          481  +      }
          482  +    }
   475    483       pS = pS->pPrior;
   476    484     }
   477    485     return mask;
   478    486   }
   479    487   
   480    488   /*
   481    489   ** Return TRUE if the given operator is one of the operators that is

Changes to test/tkt-31338dca7e.test.

   158    158        WHERE (a=1 AND h=4)
   159    159            OR (b IN (
   160    160                  SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
   161    161                  GROUP BY e
   162    162               ));
   163    163     }    
   164    164   } {4 2 4 3 4 {} 4 99 101 3 4 {}}
          165  +
          166  +do_test tkt-31338-3.7 {
          167  +  db eval {
          168  +    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
          169  +     WHERE (a=1 AND h=4)
          170  +         OR (b IN (
          171  +               SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2)
          172  +               GROUP BY e
          173  +            ));
          174  +  }    
          175  +} {4 2 4 3 4 {} 4 99 101 3 4 {}}
          176  +
   165    177   
   166    178   finish_test