Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a false-postive in the sqlite3ExprImpliesNonNullRow() decision routine, that resulted in an incorrect LEFT JOIN strength reduction when the WHERE clause contained a row-value comparison. Ticket [02aa2bd02f97d0f2] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
ea20068e6d97c9349ebcc7d0a01e99eb |
| User & Date: | drh 2019-11-04 02:05:52 |
Context
|
2019-11-04
| ||
| 12:49 | Changes an unreachable testcase() into an assert(). (check-in: 5710845b user: drh tags: trunk) | |
| 02:05 | Fix a false-postive in the sqlite3ExprImpliesNonNullRow() decision routine, that resulted in an incorrect LEFT JOIN strength reduction when the WHERE clause contained a row-value comparison. Ticket [02aa2bd02f97d0f2] (check-in: ea20068e user: drh tags: trunk) | |
|
2019-11-03
| ||
| 00:07 | The optimization of check-in [9b2879629c34fc0a] is incorrectly reasoned. The WHERE clause of the partial index might not be true if the table of the partial index is the right table of a left join. So disable the optimization in that case. Ticket [623eff57e76d45f6] (check-in: 3be19e11 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
| ︙ | ︙ | |||
5208 5209 5210 5211 5212 5213 5214 |
){
return 1;
}
return 0;
}
/*
| | > > | 5208 5209 5210 5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 5237 5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250 |
){
return 1;
}
return 0;
}
/*
** This is the Expr node callback for sqlite3ExprImpliesNonNullRow().
** If the expression node requires that the table at pWalker->iCur
** have one or more non-NULL column, then set pWalker->eCode to 1 and abort.
**
** This routine controls an optimization. False positives (setting
** pWalker->eCode to 1 when it should not be) are deadly, but false-negatives
** (never setting pWalker->eCode) is a harmless missed optimization.
*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
testcase( pExpr->op==TK_AGG_COLUMN );
testcase( pExpr->op==TK_AGG_FUNCTION );
if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
switch( pExpr->op ){
case TK_ISNOT:
case TK_ISNULL:
case TK_NOTNULL:
case TK_IS:
case TK_OR:
case TK_VECTOR:
case TK_CASE:
case TK_IN:
case TK_FUNCTION:
case TK_TRUTH:
testcase( pExpr->op==TK_ISNOT );
testcase( pExpr->op==TK_ISNULL );
testcase( pExpr->op==TK_NOTNULL );
testcase( pExpr->op==TK_IS );
testcase( pExpr->op==TK_OR );
testcase( pExpr->op==TK_VECTOR );
testcase( pExpr->op==TK_CASE );
testcase( pExpr->op==TK_IN );
testcase( pExpr->op==TK_FUNCTION );
testcase( pExpr->op==TK_TRUTH );
return WRC_Prune;
case TK_COLUMN:
if( pWalker->u.iCur==pExpr->iTable ){
|
| ︙ | ︙ |
Changes to test/rowvalue.test.
| ︙ | ︙ | |||
611 612 613 614 615 616 617 618 619 620 |
SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0;
SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0);
} {1 2}
do_execsql_test 25.40 {
SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0;
SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0);
} {1 2}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > | 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 |
SELECT ('B', 0)> (t0.c0 COLLATE nocase, 0) FROM t0;
SELECT 2 FROM t0 WHERE ('B', 0)> (t0.c0 COLLATE nocase, 0);
} {1 2}
do_execsql_test 25.40 {
SELECT (t0.c0 COLLATE nocase, 0) < ('B', 0) FROM t0;
SELECT 2 FROM t0 WHERE (t0.c0 COLLATE nocase, 0) < ('B', 0);
} {1 2}
# 2019-11-04 Ticket 02aa2bd02f97d0f2
# The TK_VECTOR operator messes up sqlite3ExprImpliesNonNull() which
# causes incorrect LEFT JOIN strength reduction. TK_VECTOR should be
# treated the same as TK_OR.
#
db close
sqlite3 db :memory:
do_execsql_test 26.10 {
CREATE TABLE t0(c0);
CREATE TABLE t1(c1);
INSERT INTO t1(c1) VALUES (0);
SELECT (c0, x'') != (NULL, 0) FROM t1 LEFT JOIN t0;
} {1}
do_execsql_test 26.20 {
SELECT 2 FROM t1 LEFT JOIN t0 ON (c0, x'') != (NULL, 0);
} {2}
do_execsql_test 26.30 {
SELECT 3 FROM t1 LEFT JOIN t0 WHERE (c0, x'') != (NULL, 0);
} {3}
finish_test
|