SQLite

Check-in [cab9b4cc]
Login

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: cab9b4cccd13bf0ab2bc38dc9a9c04ddd34e29c65ab6aef07b6bb3c31a43bece
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

4718
4719
4720
4721
4722
4723
4724
4725




4726
4727
4728
4729
4730
4731
4732
** 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) ) return;




  if( pExpr->op==TK_AND ){
    findConstInWhere(pConst, pExpr->pRight);
    findConstInWhere(pConst, pExpr->pLeft);
    return;
  }
  if( pExpr->op!=TK_EQ ) return;
  pRight = pExpr->pRight;







|
>
>
>
>







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