Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | 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. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | cursor-hints |
| Files: | files | file ages | folders |
| SHA1: |
7455d932f5079ffe40462a8c119fc22b |
| User & Date: | dan 2016-06-20 17:22:06 |
Context
|
2016-06-20
| ||
| 17:25 | For a table on the rhs of a LEFT JOIN operator, do not include terms like "IS NULL" from the WHERE clause in the cursor-hint. These may be false for rows that the cursor would otherwise visit, but true for a row of all NULL values generated by the LEFT JOIN. (check-in: 913e5956 user: dan tags: trunk) | |
| 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) | |
Changes
Changes to src/wherecode.c.
638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 |
**
** 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
|
| > > > > > | > > |
638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 |
**
** 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_ISNULL || pExpr->op==TK_ISNOT
|| pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE
){
pWalker->eCode = 1;
}else if( pExpr->op==TK_FUNCTION ){
int d1;
char d2[3];
if( 0==sqlite3IsLikeFunction(pWalker->pParse->db, pExpr, &d1, d2) ){
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
|
Changes to test/cursorhint2.test.
159 160 161 162 163 164 165 166 167 168 |
}
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
|
> > > > > > > > > > > > |
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
}
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]))}
}
do_extract_hints_test 2.11 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
} {
x2 {AND(expr,EQ(c0,r[2]))}
}
do_extract_hints_test 2.11 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
} {
x2 {EQ(c0,r[2])}
}
finish_test
|