Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Bug fix in the LEFT JOIN strength reduction optimization of check-in [dd568c27b1d76563]. The sqlite3ExprImpliesNotNull() routine was mistakenly assuming that a CASE expression must always be NULL if contained any reference to a variable that was NULL. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
cf171abe954a5f25262161dd69f2e8ce |
| User & Date: | drh 2018-03-24 13:24:02 |
Context
|
2018-03-24
| ||
| 15:08 | Fix a test script problem causing shell1.test to fail with -DSQLITE_OMIT_VIRTUAL_TABLE builds. (check-in: 2e06906e user: dan tags: trunk) | |
| 13:24 | Bug fix in the LEFT JOIN strength reduction optimization of check-in [dd568c27b1d76563]. The sqlite3ExprImpliesNotNull() routine was mistakenly assuming that a CASE expression must always be NULL if contained any reference to a variable that was NULL. (check-in: cf171abe user: drh tags: trunk) | |
| 00:19 | Prepend linkage macros to the sqlite3rebaser interfaces. (check-in: c64e8f37 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
5008 5009 5010 5011 5012 5013 5014 5015 5016 5017 5018 5019 5020 5021 |
*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
switch( pExpr->op ){
case TK_ISNULL:
case TK_IS:
case TK_OR:
case TK_FUNCTION:
case TK_AGG_FUNCTION:
return WRC_Prune;
case TK_COLUMN:
case TK_AGG_COLUMN:
if( pWalker->u.iCur==pExpr->iTable ){
pWalker->eCode = 1;
|
> |
5008 5009 5010 5011 5012 5013 5014 5015 5016 5017 5018 5019 5020 5021 5022 |
*/
static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
switch( pExpr->op ){
case TK_ISNULL:
case TK_IS:
case TK_OR:
case TK_CASE:
case TK_FUNCTION:
case TK_AGG_FUNCTION:
return WRC_Prune;
case TK_COLUMN:
case TK_AGG_COLUMN:
if( pWalker->u.iCur==pExpr->iTable ){
pWalker->eCode = 1;
|
Changes to test/join.test.
776 777 778 779 780 781 782 783 |
INSERT INTO t3(id) VALUES(1),(2);
SELECT t1.id, x2.id, x3.id
FROM t1
LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
} {456 {} {}}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 |
INSERT INTO t3(id) VALUES(1),(2);
SELECT t1.id, x2.id, x3.id
FROM t1
LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
} {456 {} {}}
# 2018-03-24.
# E.Pasma discovered that the LEFT JOIN strength reduction optimization
# was misbehaving. The problem turned out to be that the
# sqlite3ExprImpliesNotNull() routine was saying that CASE expressions
# like
#
# CASE WHEN true THEN true ELSE x=0 END
#
# could never be true if x is NULL. The following test cases verify
# that this error has been resolved.
#
db close
sqlite3 db :memory:
do_execsql_test join-15.100 {
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(1,2),(3,4);
CREATE TABLE t2(x INT, y INT);
SELECT *, 'x'
FROM t1 LEFT JOIN t2
WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.110 {
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x INTEGER PRIMARY KEY);
INSERT INTO t2(x) VALUES(0),(1);
SELECT a1, a2, a3, a4, a5
FROM (SELECT a AS a1 FROM t1 WHERE b=0)
JOIN (SELECT x AS x1 FROM t2)
LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
ON x1 IS TRUE AND b2=a1
JOIN (SELECT x AS x2 FROM t2)
ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
ON x2 IS TRUE AND b3=a2
JOIN (SELECT x AS x3 FROM t2)
ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
ON x3 IS TRUE AND b4=a3
JOIN (SELECT x AS x4 FROM t2)
ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
ON x4 IS TRUE AND b5=a4
ORDER BY a1, a2, a3, a4, a5;
} {1 {} {} {} {} 1 11 {} {} {} 1 12 {} {} {} 1 12 121 {} {} 1 13 {} {} {}}
finish_test
|