/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/where.c.

1610
1611
1612
1613
1614
1615
1616
1617

1618
1619
1620
1621
1622
1623
1624
....
4691
4692
4693
4694
4695
4696
4697
4698
4699

4700
4701
4702
4703
4704
4705
4706
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  pLoop = pLevel->pWLoop;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( pLoop->prereq==0
     && (pTerm->wtFlags & TERM_VIRTUAL)==0
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)

     && sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
      pPartial = sqlite3ExprAnd(pParse->db, pPartial,
                                sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
    }
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  int i;
  WhereTerm *pTerm;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab)
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)

    ){
      return 1;
    }
  }
  return 0;
}








|
>







 







|
|
>







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

Changes to test/index6.test.

292
293
294
295
296
297
298































299
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
  EXPLAIN QUERY PLAN
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}
































finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
  EXPLAIN QUERY PLAN
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}


do_execsql_test index6-8.0 {
  CREATE TABLE t8a(a,b);
  CREATE TABLE t8b(x,y);
  CREATE INDEX i8c ON t8b(y) WHERE x = 'value';

  INSERT INTO t8a VALUES(1, 'one');
  INSERT INTO t8a VALUES(2, 'two');
  INSERT INTO t8a VALUES(3, 'three');

  INSERT INTO t8b VALUES('value', 1);
  INSERT INTO t8b VALUES('dummy', 2);
  INSERT INTO t8b VALUES('value', 3);
  INSERT INTO t8b VALUES('dummy', 4);
} {}

do_eqp_test index6-8.1 {
  SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
} {
  0 0 0 {SCAN TABLE t8a} 
  0 1 1 {SEARCH TABLE t8b USING INDEX i8c (y=?)}
}

do_execsql_test index6-8.2 {
  SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
} {
  1 one value 1 
  2 two {} {} 
  3 three value 3
}

finish_test