/ Check-in [998095ab]
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: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 Unified Diffs Show Whitespace Changes Patch

Changes to src/wherecode.c.

622
623
624
625
626
627
628

























629
630
631
632
633
634
635
...
710
711
712
713
714
715
716
717
718
719
720
721
722

723
724
725
726







727
728

729
730
731



732
733
734
735
736
737
738
739
   && sqlite3ColumnOfIndex(pHint->pIdx, pExpr->iColumn)<0
  ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}



























/*
** This function is called on every node of an expression tree used as an
** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
** that accesses any table other than the one identified by
** CCurHint.iTabCur, then do the following:
**
................................................................................
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( pTerm->prereqAll & pLevel->notReady ) continue;

    /* Any terms specified as part of the ON(...) clause for any LEFT 
    ** JOIN for which the current table is not the rhs are omitted
    ** from the cursor-hint. 
    **
    ** If this table is the rhs of a LEFT JOIN, only terms that were
    ** specified as part of the ON(...) clause may be included in the 
    ** hint. This is to address the following:
    **
    **   SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
    **

    ** If the (t2.c IS NULL) constraint is pushed down to the cursor, it
    ** might filter out all rows that match (t1.a=t2.b), causing SQLite
    ** to add a row of NULL values to the output that should not be
    ** present (since the ON clause does actually match rows within t2).







    */
    if( pTabItem->fg.jointype & JT_LEFT ){

      if( !ExprHasProperty(pTerm->pExpr, EP_FromJoin) 
       || pTerm->pExpr->iRightJoinTable!=pTabItem->iCursor
      ){



        continue;
      }
    }else{
      if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;
    }

    /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
    ** the cursor.  These terms are not needed as hints for a pure range







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







 







|
|
|



>
|
<
|
|
>
>
>
>
>
>
>


>
|
|

>
>
>
|







622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
...
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749

750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
   && sqlite3ColumnOfIndex(pHint->pIdx, pExpr->iColumn)<0
  ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}

/*
** Test whether or not expression pExpr, which was part of a WHERE clause,
** should be included in the cursor-hint for a table that is on the rhs
** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the 
** expression is not suitable.
**
** An expression is unsuitable if it might evaluate to non NULL even if
** a TK_COLUMN node that does affect the value of the expression is set
** to NULL. For example:
**
**   col IS NULL
**   col IS NOT NULL
**   coalesce(col, 1)
**   CASE WHEN col THEN 0 ELSE 1 END
*/
static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_IS || pExpr->op==TK_FUNCTION 
   || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT 
   || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE 
  ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}


/*
** This function is called on every node of an expression tree used as an
** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
** that accesses any table other than the one identified by
** CCurHint.iTabCur, then do the following:
**
................................................................................
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( pTerm->prereqAll & pLevel->notReady ) continue;

    /* Any terms specified as part of the ON(...) clause for any LEFT 
    ** JOIN for which the current table is not the rhs are omitted
    ** from the cursor-hint. 
    **
    ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms 
    ** that were specified as part of the WHERE clause must be excluded.
    ** This is to address the following:
    **
    **   SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
    **
    ** Say there is a single row in t2 that matches (t1.a=t2.b), but its
    ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is 

    ** pushed down to the cursor, this row is filtered out, causing
    ** SQLite to synthesize a row of NULL values. Which does match the
    ** WHERE clause, and so the query returns a row. Which is incorrect.
    **
    ** For the same reason, WHERE terms such as:
    **
    **   WHERE 1 = (t2.c IS NULL)
    **
    ** are also excluded. See codeCursorHintIsOrFunction() for details.
    */
    if( pTabItem->fg.jointype & JT_LEFT ){
      Expr *pExpr = pTerm->pExpr;
      if( !ExprHasProperty(pExpr, EP_FromJoin) 
       || pExpr->iRightJoinTable!=pTabItem->iCursor
      ){
        sWalker.eCode = 0;
        sWalker.xExprCallback = codeCursorHintIsOrFunction;
        sqlite3WalkExpr(&sWalker, pTerm->pExpr);
        if( sWalker.eCode ) continue;
      }
    }else{
      if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;
    }

    /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
    ** the cursor.  These terms are not needed as hints for a pure range

Changes to test/cursorhint2.test.

90
91
92
93
94
95
96
97
98
99



































































100
101
} {
  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
}

do_extract_hints_test 1.7 {
  SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
} {
  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
}




































































finish_test








|


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


90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
} {
  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
}

do_extract_hints_test 1.7 {
  SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
} {
  t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))}
}

#-------------------------------------------------------------------------
#
do_execsql_test 2.0 {
  CREATE TABLE x1(x, y);
  CREATE TABLE x2(a, b);
}

do_extract_hints_test 2.1 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.2 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.3 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.4 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.5 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.6 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.7 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.8 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.9 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
} {
  x2 {EQ(c0,r[2])}
}

do_extract_hints_test 2.10 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
} {
  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
}

finish_test