/ Check-in [1d6fb43a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Prevent partial indexes on the table on the left hand side of a LEFT JOIN from being incorrectly qualified by a constraint in the ON clause of the join. This relaxes the rule introduced by the previous commit (as the partial indexes on the table on the rhs of the LEFT JOIN may now be qualified by terms within the ON clause).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1d6fb43a576d335d2717e94d28385178c23c81a1
User & Date: dan 2015-02-24 20:10:49
Context
2015-02-25
00:24
Remove an always-false conditional from constructAutomaticIndex(). Put an assert() in its place to prove that the conditional is always false. check-in: 3af300bf user: drh tags: trunk
2015-02-24
20:10
Prevent partial indexes on the table on the left hand side of a LEFT JOIN from being incorrectly qualified by a constraint in the ON clause of the join. This relaxes the rule introduced by the previous commit (as the partial indexes on the table on the rhs of the LEFT JOIN may now be qualified by terms within the ON clause). check-in: 1d6fb43a user: dan tags: trunk
20:04
Make sure partial indexes are not qualified incorrectly by a constraint that is inside the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead33]. check-in: 491cfe9b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1610   1610     pTable = pSrc->pTab;
  1611   1611     pWCEnd = &pWC->a[pWC->nTerm];
  1612   1612     pLoop = pLevel->pWLoop;
  1613   1613     idxCols = 0;
  1614   1614     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1615   1615       if( pLoop->prereq==0
  1616   1616        && (pTerm->wtFlags & TERM_VIRTUAL)==0
  1617         -     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
         1617  +     && (!ExprHasProperty(pTerm->pExpr, EP_FromJoin)
         1618  +      || pTerm->pExpr->iRightJoinTable==pSrc->iCursor)
  1618   1619        && sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
  1619   1620         pPartial = sqlite3ExprAnd(pParse->db, pPartial,
  1620   1621                                   sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
  1621   1622       }
  1622   1623       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
  1623   1624         int iCol = pTerm->u.leftColumn;
  1624   1625         Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
  4691   4692   /* Check to see if a partial index with pPartIndexWhere can be used
  4692   4693   ** in the current query.  Return true if it can be and false if not.
  4693   4694   */
  4694   4695   static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  4695   4696     int i;
  4696   4697     WhereTerm *pTerm;
  4697   4698     for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  4698         -    if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab)
  4699         -     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
         4699  +    Expr *pExpr = pTerm->pExpr;
         4700  +    if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab) 
         4701  +     && (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
  4700   4702       ){
  4701   4703         return 1;
  4702   4704       }
  4703   4705     }
  4704   4706     return 0;
  4705   4707   }
  4706   4708   

Changes to test/index6.test.

   292    292     SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
   293    293   } {99 2}
   294    294   do_execsql_test index6-7.4 {
   295    295     EXPLAIN QUERY PLAN
   296    296     SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
   297    297   } {/USING COVERING INDEX t7ax/}
   298    298   
          299  +
          300  +do_execsql_test index6-8.0 {
          301  +  CREATE TABLE t8a(a,b);
          302  +  CREATE TABLE t8b(x,y);
          303  +  CREATE INDEX i8c ON t8b(y) WHERE x = 'value';
          304  +
          305  +  INSERT INTO t8a VALUES(1, 'one');
          306  +  INSERT INTO t8a VALUES(2, 'two');
          307  +  INSERT INTO t8a VALUES(3, 'three');
          308  +
          309  +  INSERT INTO t8b VALUES('value', 1);
          310  +  INSERT INTO t8b VALUES('dummy', 2);
          311  +  INSERT INTO t8b VALUES('value', 3);
          312  +  INSERT INTO t8b VALUES('dummy', 4);
          313  +} {}
          314  +
          315  +do_eqp_test index6-8.1 {
          316  +  SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
          317  +} {
          318  +  0 0 0 {SCAN TABLE t8a} 
          319  +  0 1 1 {SEARCH TABLE t8b USING INDEX i8c (y=?)}
          320  +}
          321  +
          322  +do_execsql_test index6-8.2 {
          323  +  SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
          324  +} {
          325  +  1 one value 1 
          326  +  2 two {} {} 
          327  +  3 three value 3
          328  +}
          329  +
   299    330   finish_test