/ Check-in [998095ab]
Login

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

Overview
Comment:Include WHERE terms in the cursor-hint passed to a cursor opened for the rhs of a LEFT JOIN iff we can be sure that those terms will not evaluate to true if the LEFT JOIN generates a row of NULLs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: 998095aba01b75f685ed981b377e1dfe650d9bbf
User & Date: dan 2016-06-17 19:27:13
Context
2016-06-20
17:22
Allow LIKE operators that appear in a WHERE clause to be included in the cursor-hint for a cursor on the rhs of a LEFT JOIN. Closed-Leaf check-in: 7455d932 user: dan tags: cursor-hints
2016-06-17
19:27
Include WHERE terms in the cursor-hint passed to a cursor opened for the rhs of a LEFT JOIN iff we can be sure that those terms will not evaluate to true if the LEFT JOIN generates a row of NULLs. check-in: 998095ab user: dan tags: cursor-hints
14:59
Fix a duplicate test name in cursorhint2.test. check-in: fcd12b69 user: dan tags: cursor-hints
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   622    622      && sqlite3ColumnOfIndex(pHint->pIdx, pExpr->iColumn)<0
   623    623     ){
   624    624       pWalker->eCode = 1;
   625    625     }
   626    626     return WRC_Continue;
   627    627   }
   628    628   
          629  +/*
          630  +** Test whether or not expression pExpr, which was part of a WHERE clause,
          631  +** should be included in the cursor-hint for a table that is on the rhs
          632  +** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the 
          633  +** expression is not suitable.
          634  +**
          635  +** An expression is unsuitable if it might evaluate to non NULL even if
          636  +** a TK_COLUMN node that does affect the value of the expression is set
          637  +** to NULL. For example:
          638  +**
          639  +**   col IS NULL
          640  +**   col IS NOT NULL
          641  +**   coalesce(col, 1)
          642  +**   CASE WHEN col THEN 0 ELSE 1 END
          643  +*/
          644  +static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){
          645  +  if( pExpr->op==TK_IS || pExpr->op==TK_FUNCTION 
          646  +   || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT 
          647  +   || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE 
          648  +  ){
          649  +    pWalker->eCode = 1;
          650  +  }
          651  +  return WRC_Continue;
          652  +}
          653  +
   629    654   
   630    655   /*
   631    656   ** This function is called on every node of an expression tree used as an
   632    657   ** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
   633    658   ** that accesses any table other than the one identified by
   634    659   ** CCurHint.iTabCur, then do the following:
   635    660   **
................................................................................
   710    735       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
   711    736       if( pTerm->prereqAll & pLevel->notReady ) continue;
   712    737   
   713    738       /* Any terms specified as part of the ON(...) clause for any LEFT 
   714    739       ** JOIN for which the current table is not the rhs are omitted
   715    740       ** from the cursor-hint. 
   716    741       **
   717         -    ** If this table is the rhs of a LEFT JOIN, only terms that were
   718         -    ** specified as part of the ON(...) clause may be included in the 
   719         -    ** hint. This is to address the following:
          742  +    ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms 
          743  +    ** that were specified as part of the WHERE clause must be excluded.
          744  +    ** This is to address the following:
   720    745       **
   721    746       **   SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
   722    747       **
   723         -    ** If the (t2.c IS NULL) constraint is pushed down to the cursor, it
   724         -    ** might filter out all rows that match (t1.a=t2.b), causing SQLite
   725         -    ** to add a row of NULL values to the output that should not be
   726         -    ** present (since the ON clause does actually match rows within t2).
          748  +    ** Say there is a single row in t2 that matches (t1.a=t2.b), but its
          749  +    ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is 
          750  +    ** pushed down to the cursor, this row is filtered out, causing
          751  +    ** SQLite to synthesize a row of NULL values. Which does match the
          752  +    ** WHERE clause, and so the query returns a row. Which is incorrect.
          753  +    **
          754  +    ** For the same reason, WHERE terms such as:
          755  +    **
          756  +    **   WHERE 1 = (t2.c IS NULL)
          757  +    **
          758  +    ** are also excluded. See codeCursorHintIsOrFunction() for details.
   727    759       */
   728    760       if( pTabItem->fg.jointype & JT_LEFT ){
   729         -      if( !ExprHasProperty(pTerm->pExpr, EP_FromJoin) 
   730         -       || pTerm->pExpr->iRightJoinTable!=pTabItem->iCursor
          761  +      Expr *pExpr = pTerm->pExpr;
          762  +      if( !ExprHasProperty(pExpr, EP_FromJoin) 
          763  +       || pExpr->iRightJoinTable!=pTabItem->iCursor
   731    764         ){
   732         -        continue;
          765  +        sWalker.eCode = 0;
          766  +        sWalker.xExprCallback = codeCursorHintIsOrFunction;
          767  +        sqlite3WalkExpr(&sWalker, pTerm->pExpr);
          768  +        if( sWalker.eCode ) continue;
   733    769         }
   734    770       }else{
   735    771         if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;
   736    772       }
   737    773   
   738    774       /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
   739    775       ** the cursor.  These terms are not needed as hints for a pure range

Changes to test/cursorhint2.test.

    90     90   } {
    91     91     t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
    92     92   }
    93     93   
    94     94   do_extract_hints_test 1.7 {
    95     95     SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
    96     96   } {
    97         -  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
           97  +  t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))}
           98  +}
           99  +
          100  +#-------------------------------------------------------------------------
          101  +#
          102  +do_execsql_test 2.0 {
          103  +  CREATE TABLE x1(x, y);
          104  +  CREATE TABLE x2(a, b);
          105  +}
          106  +
          107  +do_extract_hints_test 2.1 {
          108  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL;
          109  +} {
          110  +  x2 {EQ(c0,r[2])}
          111  +}
          112  +
          113  +do_extract_hints_test 2.2 {
          114  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL;
          115  +} {
          116  +  x2 {EQ(c0,r[2])}
          117  +}
          118  +
          119  +do_extract_hints_test 2.3 {
          120  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL)
          121  +} {
          122  +  x2 {EQ(c0,r[2])}
          123  +}
          124  +
          125  +do_extract_hints_test 2.4 {
          126  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
          127  +} {
          128  +  x2 {EQ(c0,r[2])}
          129  +}
          130  +
          131  +do_extract_hints_test 2.5 {
          132  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
          133  +} {
          134  +  x2 {EQ(c0,r[2])}
          135  +}
          136  +
          137  +do_extract_hints_test 2.6 {
          138  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
          139  +} {
          140  +  x2 {EQ(c0,r[2])}
          141  +}
          142  +
          143  +do_extract_hints_test 2.7 {
          144  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
          145  +} {
          146  +  x2 {EQ(c0,r[2])}
          147  +}
          148  +
          149  +do_extract_hints_test 2.8 {
          150  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
          151  +} {
          152  +  x2 {EQ(c0,r[2])}
          153  +}
          154  +
          155  +do_extract_hints_test 2.9 {
          156  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
          157  +} {
          158  +  x2 {EQ(c0,r[2])}
          159  +}
          160  +
          161  +do_extract_hints_test 2.10 {
          162  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
          163  +} {
          164  +  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
    98    165   }
    99    166   
   100    167   finish_test
   101    168