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 |
Timelines: | family | ancestors | descendants | both | cursor-hints |
Files: | files | file ages | folders |
SHA1: |
998095aba01b75f685ed981b377e1dfe |
User & Date: | dan 2016-06-17 19:27:13.114 |
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: 7455d932f5 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: 998095aba0 user: dan tags: cursor-hints) | |
14:59 | Fix a duplicate test name in cursorhint2.test. (check-in: fcd12b69ce user: dan tags: cursor-hints) | |
Changes
Changes to src/wherecode.c.
︙ | ︙ | |||
622 623 624 625 626 627 628 629 630 631 632 633 634 635 | && 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: ** | > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | && 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: ** |
︙ | ︙ | |||
710 711 712 713 714 715 716 | 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. ** | | | | > | < > | < > > > > > > > > | | > > > | | 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 | 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 | } { 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); } { | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |