Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not allow constant propagation between WHERE-clause terms and ON-clause terms as this can confuse RIGHT JOIN. Fix for the problem reported by forum post 8e4c352937e82929. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
cab9b4cccd13bf0ab2bc38dc9a9c04dd |
| User & Date: | drh 2022-06-10 11:28:52 |
References
|
2022-06-20
| ||
| 12:42 | The fix at [cab9b4cccd13bf0a] was incomplete, as demonstrated by forum post 57bdf2217d. This check-in should complete the fix. (check-in: fb0a23b6 user: drh tags: trunk) | |
Context
|
2022-06-10
| ||
| 15:43 | fiddle: minor style tweaks, including using swapped colors for the input/output fields to help (hopefully) reduce the "which field is which?" dissonance. (check-in: e25dad86 user: stephan tags: trunk) | |
| 11:28 | Do not allow constant propagation between WHERE-clause terms and ON-clause terms as this can confuse RIGHT JOIN. Fix for the problem reported by forum post 8e4c352937e82929. (check-in: cab9b4cc user: drh tags: trunk) | |
| 10:10 | Fix a faulty assert() statement identified by forum post 0b91a75039. (check-in: 1f132bb0 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
4718 4719 4720 4721 4722 4723 4724 |
** is a constant expression and where the term must be true because it
** is part of the AND-connected terms of the expression. For each term
** found, add it to the pConst structure.
*/
static void findConstInWhere(WhereConst *pConst, Expr *pExpr){
Expr *pRight, *pLeft;
if( NEVER(pExpr==0) ) return;
| | > > > > | 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 |
** is a constant expression and where the term must be true because it
** is part of the AND-connected terms of the expression. For each term
** found, add it to the pConst structure.
*/
static void findConstInWhere(WhereConst *pConst, Expr *pExpr){
Expr *pRight, *pLeft;
if( NEVER(pExpr==0) ) return;
if( ExprHasProperty(pExpr, EP_OuterON|EP_InnerON) ){
testcase( ExprHasProperty(pExpr, EP_OuterON) );
testcase( ExprHasProperty(pExpr, EP_InnerON) );
return;
}
if( pExpr->op==TK_AND ){
findConstInWhere(pConst, pExpr->pRight);
findConstInWhere(pConst, pExpr->pLeft);
return;
}
if( pExpr->op!=TK_EQ ) return;
pRight = pExpr->pRight;
|
| ︙ | ︙ |
Changes to test/join8.test.
| ︙ | ︙ | |||
652 653 654 655 656 657 658 659 660 |
SELECT count(*)
FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'') AS "t3";
} {1}
do_execsql_test join8-20060 {
SELECT count(*)
FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'' ORDER BY z) AS "t3";
} {1}
finish_test
| > > > > > > > > > > > > > > > > > > > | 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 |
SELECT count(*)
FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'') AS "t3";
} {1}
do_execsql_test join8-20060 {
SELECT count(*)
FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'' ORDER BY z) AS "t3";
} {1}
# 2022-06-10
# https://sqlite.org/forum/forumpost/8e4c352937e82929
#
# Do not allow constant propagation between ON and WHERE clause terms.
#
reset_db
do_execsql_test join8-21000 {
CREATE TABLE t1(a INT,b BOOLEAN);
CREATE TABLE t2(c INT); INSERT INTO t2 VALUES(NULL);
CREATE TABLE t3(d INT);
}
do_execsql_test join8-21010 {
SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
} {0}
do_execsql_test join8-22020 {
SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
} {}
finish_test
|