SQLite

Check-in [fb0a23b6]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The fix at [cab9b4cccd13bf0a] was incomplete, as demonstrated by forum post 57bdf2217d. This check-in should complete the fix.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fb0a23b6789da8e934562ce9ebd9d58ea13a10fd10dee5cbfc7ac8f394e1aeec
User & Date: drh 2022-06-20 12:42:28
Context
2022-06-20
17:04
Do not allow an ON clause to references tables to its right if there is a RIGHT or FULL join anywhere in the query. Other RDBMSes prohibit this always, but SQLite must allow ON clauses to reference tables to their right for legacy compatibility, unless there is a RIGHT or FULL join someplace in the query, in which case there is no legacy to support. (check-in: e615dbe0 user: drh tags: trunk)
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)
2022-06-19
16:55
Follow-up to check-in [0057bbb508e7662b] - ensure that the database page has been initialized prior to continuing with the optimization. If the page is not initialized, that indicates that the database is corrupt. dbsqlfuzz 09ee46becd5e6d1b2a55c9f8ad767335a90aadb0. (check-in: 11162446 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree1.test.

728
729
730
731
732
733
734














735
736
  INSERT INTO rt0(a,b,c) VALUES(0,0.0,0.0);
  CREATE VIEW v0(x) AS SELECT DISTINCT rt0.b FROM rt0;
  SELECT v0.x FROM v0, rt0;
} {0.0}
do_execsql_test 19.1 {
  SELECT v0.x FROM v0, rt0 WHERE v0.x = rt0.b;
} {0.0}















finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>


728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
  INSERT INTO rt0(a,b,c) VALUES(0,0.0,0.0);
  CREATE VIEW v0(x) AS SELECT DISTINCT rt0.b FROM rt0;
  SELECT v0.x FROM v0, rt0;
} {0.0}
do_execsql_test 19.1 {
  SELECT v0.x FROM v0, rt0 WHERE v0.x = rt0.b;
} {0.0}

# 2022-06-20 https://sqlite.org/forum/forumpost/57bdf2217d
#
reset_db
do_execsql_test 20.0 {
  CREATE VIRTUAL TABLE rt0 USING rtree(id, x0, x1);
  CREATE TABLE t0(a INT);
  CREATE TABLE t1(b INT);
  INSERT INTO rt0 VALUES(0, 0, 0);
  SELECT * FROM t1 JOIN t0 ON x0>a RIGHT JOIN rt0 ON true WHERE +x0 = 0;
} {}
do_execsql_test 20.1 {
  SELECT * FROM t1 JOIN t0 ON x0>a RIGHT JOIN rt0 ON true WHERE x0 = 0;
} {}

finish_test

Changes to src/select.c.

4662
4663
4664
4665
4666
4667
4668


4669
4670
4671
4672
4673
4674
4675
typedef struct WhereConst WhereConst;
struct WhereConst {
  Parse *pParse;   /* Parsing context */
  u8 *pOomFault;   /* Pointer to pParse->db->mallocFailed */
  int nConst;      /* Number for COLUMN=CONSTANT terms */
  int nChng;       /* Number of times a constant is propagated */
  int bHasAffBlob; /* At least one column in apExpr[] as affinity BLOB */


  Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
};

/*
** Add a new entry to the pConst object.  Except, do not add duplicate
** pColumn entires.  Also, do not add if doing so would not be appropriate.
**







>
>







4662
4663
4664
4665
4666
4667
4668
4669
4670
4671
4672
4673
4674
4675
4676
4677
typedef struct WhereConst WhereConst;
struct WhereConst {
  Parse *pParse;   /* Parsing context */
  u8 *pOomFault;   /* Pointer to pParse->db->mallocFailed */
  int nConst;      /* Number for COLUMN=CONSTANT terms */
  int nChng;       /* Number of times a constant is propagated */
  int bHasAffBlob; /* At least one column in apExpr[] as affinity BLOB */
  u32 mExcludeOn;  /* Which ON expressions to exclude from considertion.
                   ** Either EP_OuterON or EP_InnerON|EP_OuterON */
  Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
};

/*
** Add a new entry to the pConst object.  Except, do not add duplicate
** pColumn entires.  Also, do not add if doing so would not be appropriate.
**
4724
4725
4726
4727
4728
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738
** 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);







|







4726
4727
4728
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738
4739
4740
** 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, pConst->mExcludeOn) ){
    testcase( ExprHasProperty(pExpr, EP_OuterON) );
    testcase( ExprHasProperty(pExpr, EP_InnerON) );
    return;
  }
  if( pExpr->op==TK_AND ){
    findConstInWhere(pConst, pExpr->pRight);
    findConstInWhere(pConst, pExpr->pLeft);
4764
4765
4766
4767
4768
4769
4770
4771
4772
4773

4774
4775
4776
4777
4778
4779
4780
  WhereConst *pConst,
  Expr *pExpr, 
  int bIgnoreAffBlob
){
  int i;
  if( pConst->pOomFault[0] ) return WRC_Prune;
  if( pExpr->op!=TK_COLUMN ) return WRC_Continue;
  if( ExprHasProperty(pExpr, EP_FixedCol|EP_OuterON) ){
    testcase( ExprHasProperty(pExpr, EP_FixedCol) );
    testcase( ExprHasProperty(pExpr, EP_OuterON) );

    return WRC_Continue;
  }
  for(i=0; i<pConst->nConst; i++){
    Expr *pColumn = pConst->apExpr[i*2];
    if( pColumn==pExpr ) continue;
    if( pColumn->iTable!=pExpr->iTable ) continue;
    if( pColumn->iColumn!=pExpr->iColumn ) continue;







|


>







4766
4767
4768
4769
4770
4771
4772
4773
4774
4775
4776
4777
4778
4779
4780
4781
4782
4783
  WhereConst *pConst,
  Expr *pExpr, 
  int bIgnoreAffBlob
){
  int i;
  if( pConst->pOomFault[0] ) return WRC_Prune;
  if( pExpr->op!=TK_COLUMN ) return WRC_Continue;
  if( ExprHasProperty(pExpr, EP_FixedCol|pConst->mExcludeOn) ){
    testcase( ExprHasProperty(pExpr, EP_FixedCol) );
    testcase( ExprHasProperty(pExpr, EP_OuterON) );
    testcase( ExprHasProperty(pExpr, EP_InnerON) );
    return WRC_Continue;
  }
  for(i=0; i<pConst->nConst; i++){
    Expr *pColumn = pConst->apExpr[i*2];
    if( pColumn==pExpr ) continue;
    if( pColumn->iTable!=pExpr->iTable ) continue;
    if( pColumn->iColumn!=pExpr->iColumn ) continue;
4890
4891
4892
4893
4894
4895
4896











4897
4898
4899
4900
4901
4902
4903
  x.pParse = pParse;
  x.pOomFault = &pParse->db->mallocFailed;
  do{
    x.nConst = 0;
    x.nChng = 0;
    x.apExpr = 0;
    x.bHasAffBlob = 0;











    findConstInWhere(&x, p->pWhere);
    if( x.nConst ){
      memset(&w, 0, sizeof(w));
      w.pParse = pParse;
      w.xExprCallback = propagateConstantExprRewrite;
      w.xSelectCallback = sqlite3SelectWalkNoop;
      w.xSelectCallback2 = 0;







>
>
>
>
>
>
>
>
>
>
>







4893
4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904
4905
4906
4907
4908
4909
4910
4911
4912
4913
4914
4915
4916
4917
  x.pParse = pParse;
  x.pOomFault = &pParse->db->mallocFailed;
  do{
    x.nConst = 0;
    x.nChng = 0;
    x.apExpr = 0;
    x.bHasAffBlob = 0;
    if( ALWAYS(p->pSrc!=0)
     && p->pSrc->nSrc>0
     && (p->pSrc->a[0].fg.jointype & JT_LTORJ)!=0
    ){
      /* Do not propagate constants on any ON clause if there is a
      ** RIGHT JOIN anywhere in the query */
      x.mExcludeOn = EP_InnerON | EP_OuterON;
    }else{
      /* Do not propagate constants through the ON clause of a LEFT JOIN */
      x.mExcludeOn = EP_OuterON;
    }
    findConstInWhere(&x, p->pWhere);
    if( x.nConst ){
      memset(&w, 0, sizeof(w));
      w.pParse = pParse;
      w.xExprCallback = propagateConstantExprRewrite;
      w.xSelectCallback = sqlite3SelectWalkNoop;
      w.xSelectCallback2 = 0;

Changes to test/join8.test.

657
658
659
660
661
662
663

664
665
666
667
668
669
670
671
672
673
674
675
676















677
678
679
680
681
682
683
    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);
} {}
















# 2022-06-10
# https://sqlite.org/forum/forumpost/51e6959f61
#
# Restrictions on the usage of WHERE clause constraints by joins that are
# involved with a RIGHT JOIN must also be applied to automatic indexes.
#







>













>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
    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.
# (Updated 2022-06-20) See also https://sqlite.org/forum/forumpost/57bdf2217d
#
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);
} {}
do_execsql_test join8-22030 {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE t3;
  CREATE TABLE t1(a INT);
  CREATE TABLE t2(b INT);
  CREATE TABLE t3(c INTEGER PRIMARY KEY, d INT);
  CREATE INDEX t3d ON t3(d);
  INSERT INTO t3 VALUES(0, 0);
  SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE +d = 0;
} {}
do_execsql_test join8-22040 {
  SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE d = 0;
} {}


# 2022-06-10
# https://sqlite.org/forum/forumpost/51e6959f61
#
# Restrictions on the usage of WHERE clause constraints by joins that are
# involved with a RIGHT JOIN must also be applied to automatic indexes.
#