SQLite

Changes On Branch wrong-branch
Login

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

Changes In Branch wrong-branch Excluding Merge-Ins

This is equivalent to a diff from 8682931f to 681dfe2d

2024-04-07
10:30
Merge trunk enhancements into the pushdown-subquery branch. (check-in: 27865e31 user: drh tags: pushdown-subquery)
10:27
Add comments to note the name abiguity between the MySQL push-down optimization and the WHERE-clause push-down optimization. (check-in: 3d5fb1ec user: drh tags: trunk)
2024-04-06
23:09
Add comments to note the name abiguity between the MySQL push-down optimization and the WHERE-clause push-down optimization. (Leaf check-in: 681dfe2d user: drh tags: wrong-branch)
18:30
Do not allow changes to sqlite3ExprIsTableConstant() that support pushdown of subqueries interfere with the hash-join logic. (check-in: 8682931f user: drh tags: pushdown-subquery)
17:42
Merge testing logic fixes on trunk into the pushdown-subquery branch. (check-in: a4e1f03d user: drh tags: pushdown-subquery)

Changes to src/expr.c.

5064
5065
5066
5067
5068
5069
5070
5071
5072

5073
5074
5075
5076
5077
5078
5079
      exprCodeBetween(pParse, pExpr, target, 0, 0);
      return target;
    }
    case TK_COLLATE: {
      if( !ExprHasProperty(pExpr, EP_Collate) ){
        /* A TK_COLLATE Expr node without the EP_Collate tag is a so-called
        ** "SOFT-COLLATE" that is added to constraints that are pushed down
        ** from outer queries into sub-queries by the push-down optimization.
        ** Clear subtypes as subtypes may not cross a subquery boundary.

        */
        assert( pExpr->pLeft );
        sqlite3ExprCode(pParse, pExpr->pLeft, target);
        sqlite3VdbeAddOp1(v, OP_ClrSubtype, target);
        return target;
      }else{
        pExpr = pExpr->pLeft;







|
|
>







5064
5065
5066
5067
5068
5069
5070
5071
5072
5073
5074
5075
5076
5077
5078
5079
5080
      exprCodeBetween(pParse, pExpr, target, 0, 0);
      return target;
    }
    case TK_COLLATE: {
      if( !ExprHasProperty(pExpr, EP_Collate) ){
        /* A TK_COLLATE Expr node without the EP_Collate tag is a so-called
        ** "SOFT-COLLATE" that is added to constraints that are pushed down
        ** from outer queries into sub-queries by the WHERE-clause push-down
        ** optimization. Clear subtypes as subtypes may not cross a subquery
        ** boundary.
        */
        assert( pExpr->pLeft );
        sqlite3ExprCode(pParse, pExpr->pLeft, target);
        sqlite3VdbeAddOp1(v, OP_ClrSubtype, target);
        return target;
      }else{
        pExpr = pExpr->pLeft;

Changes to src/select.c.

5055
5056
5057
5058
5059
5060
5061












5062
5063
5064
5065
5066
5067
5068
** Transformed into:
**
**    SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
**     WHERE x=5 AND y=10;
**
** The hope is that the terms added to the inner query will make it more
** efficient.












**
** Do not attempt this optimization if:
**
**   (1) (** This restriction was removed on 2017-09-29.  We used to
**           disallow this optimization for aggregate subqueries, but now
**           it is allowed by putting the extra terms on the HAVING clause.
**           The added HAVING clause is pointless if the subquery lacks







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







5055
5056
5057
5058
5059
5060
5061
5062
5063
5064
5065
5066
5067
5068
5069
5070
5071
5072
5073
5074
5075
5076
5077
5078
5079
5080
** Transformed into:
**
**    SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
**     WHERE x=5 AND y=10;
**
** The hope is that the terms added to the inner query will make it more
** efficient.
**
** NAME AMBIGUITY
**
** This optimization is called the "WHERE-clause push-down optimization".
**
** Do not confuse this optimization with another unrelated optimization
** with a similar name:  The "MySQL push-down optimization" causes WHERE
** clause terms that can be evaluated using only the index and without
** reference to the table are run first, so that if they are false,
** unnecessary table seeks are avoided.
**
** RULES
**
** Do not attempt this optimization if:
**
**   (1) (** This restriction was removed on 2017-09-29.  We used to
**           disallow this optimization for aggregate subqueries, but now
**           it is allowed by putting the extra terms on the HAVING clause.
**           The added HAVING clause is pointless if the subquery lacks
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
5138
**            of a join (either an INNER or an OUTER join), and
**
**       (9b) The subquery is to the right of the ON/USING clause
**
**       (9c) There is a RIGHT JOIN (or FULL JOIN) in between the ON/USING
**            clause and the subquery.
**
**       Without this restriction, the push-down optimization might move
**       the ON/USING filter expression from the left side of a RIGHT JOIN
**       over to the right side, which leads to incorrect answers.  See
**       also restriction (6) in sqlite3ExprIsSingleTableConstraint().
**
**  (10) The inner query is not the right-hand table of a RIGHT JOIN.
**
**  (11) The subquery is not a VALUES clause
**
**  (12) The WHERE clause is not "rowid ISNULL" or the equivalent.  This
**       case only comes up if SQLite is compiled using







|
|
|
|







5133
5134
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
**            of a join (either an INNER or an OUTER join), and
**
**       (9b) The subquery is to the right of the ON/USING clause
**
**       (9c) There is a RIGHT JOIN (or FULL JOIN) in between the ON/USING
**            clause and the subquery.
**
**       Without this restriction, the WHERE-clause push-down optimization
**       might move the ON/USING filter expression from the left side of a
**       RIGHT JOIN over to the right side, which leads to incorrect answers.
**       See also restriction (6) in sqlite3ExprIsSingleTableConstraint().
**
**  (10) The inner query is not the right-hand table of a RIGHT JOIN.
**
**  (11) The subquery is not a VALUES clause
**
**  (12) The WHERE clause is not "rowid ISNULL" or the equivalent.  This
**       case only comes up if SQLite is compiled using
7685
7686
7687
7688
7689
7690
7691
7692
7693
7694
7695
7696
7697
7698
7699
        TREETRACE(0x4000,pParse,p,
            ("After WHERE-clause push-down into subquery %d:\n", pSub->selId));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 );
    }else{
      TREETRACE(0x4000,pParse,p,("Push-down not possible\n"));
    }

    /* Convert unused result columns of the subquery into simple NULL
    ** expressions, to avoid unneeded searching and computation.
    */
    if( OptimizationEnabled(db, SQLITE_NullUnusedCols)
     && disableUnusedSubqueryResultColumns(pItem)







|







7697
7698
7699
7700
7701
7702
7703
7704
7705
7706
7707
7708
7709
7710
7711
        TREETRACE(0x4000,pParse,p,
            ("After WHERE-clause push-down into subquery %d:\n", pSub->selId));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 );
    }else{
      TREETRACE(0x4000,pParse,p,("WHERE-lcause push-down not possible\n"));
    }

    /* Convert unused result columns of the subquery into simple NULL
    ** expressions, to avoid unneeded searching and computation.
    */
    if( OptimizationEnabled(db, SQLITE_NullUnusedCols)
     && disableUnusedSubqueryResultColumns(pItem)

Changes to src/sqliteInt.h.

1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
#define SQLITE_OrderByIdxJoin 0x00000040 /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x00000080 /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x00000100 /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x00000200 /* The count-of-view optimization */
#define SQLITE_CursorHints    0x00000400 /* Add OP_CursorHint opcodes */
#define SQLITE_Stat4          0x00000800 /* Use STAT4 data */
   /* TH3 expects this value  ^^^^^^^^^^ to be 0x0000800. Don't change it */
#define SQLITE_PushDown       0x00001000 /* The push-down optimization */
#define SQLITE_SimplifyJoin   0x00002000 /* Convert LEFT JOIN to JOIN */
#define SQLITE_SkipScan       0x00004000 /* Skip-scans */
#define SQLITE_PropagateConst 0x00008000 /* The constant propagation opt */
#define SQLITE_MinMaxOpt      0x00010000 /* The min/max optimization */
#define SQLITE_SeekScan       0x00020000 /* The OP_SeekScan optimization */
#define SQLITE_OmitOrderBy    0x00040000 /* Omit pointless ORDER BY */
   /* TH3 expects this value  ^^^^^^^^^^ to be 0x40000. Coordinate any change */







|







1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
#define SQLITE_OrderByIdxJoin 0x00000040 /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x00000080 /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x00000100 /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x00000200 /* The count-of-view optimization */
#define SQLITE_CursorHints    0x00000400 /* Add OP_CursorHint opcodes */
#define SQLITE_Stat4          0x00000800 /* Use STAT4 data */
   /* TH3 expects this value  ^^^^^^^^^^ to be 0x0000800. Don't change it */
#define SQLITE_PushDown       0x00001000 /* WHERE-clause push-down opt */
#define SQLITE_SimplifyJoin   0x00002000 /* Convert LEFT JOIN to JOIN */
#define SQLITE_SkipScan       0x00004000 /* Skip-scans */
#define SQLITE_PropagateConst 0x00008000 /* The constant propagation opt */
#define SQLITE_MinMaxOpt      0x00010000 /* The min/max optimization */
#define SQLITE_SeekScan       0x00020000 /* The OP_SeekScan optimization */
#define SQLITE_OmitOrderBy    0x00040000 /* Omit pointless ORDER BY */
   /* TH3 expects this value  ^^^^^^^^^^ to be 0x40000. Coordinate any change */
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
#define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */
#define SF_ComplexResult 0x0040000 /* Result contains subquery or function */
#define SF_WhereBegin    0x0080000 /* Really a WhereBegin() call.  Debug Only */
#define SF_WinRewrite    0x0100000 /* Window function rewrite accomplished */
#define SF_View          0x0200000 /* SELECT statement is a view */
#define SF_NoopOrderBy   0x0400000 /* ORDER BY is ignored for this query */
#define SF_UFSrcCheck    0x0800000 /* Check pSrc as required by UPDATE...FROM */
#define SF_PushDown      0x1000000 /* SELECT has be modified by push-down opt */
#define SF_MultiPart     0x2000000 /* Has multiple incompatible PARTITIONs */
#define SF_CopyCte       0x4000000 /* SELECT statement is a copy of a CTE */
#define SF_OrderByReqd   0x8000000 /* The ORDER BY clause may not be omitted */
#define SF_UpdateFrom   0x10000000 /* Query originates with UPDATE FROM */
#define SF_Correlated   0x20000000 /* True if references the outer context */

/* True if S exists and has SF_NestedFrom */







|







3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
#define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */
#define SF_ComplexResult 0x0040000 /* Result contains subquery or function */
#define SF_WhereBegin    0x0080000 /* Really a WhereBegin() call.  Debug Only */
#define SF_WinRewrite    0x0100000 /* Window function rewrite accomplished */
#define SF_View          0x0200000 /* SELECT statement is a view */
#define SF_NoopOrderBy   0x0400000 /* ORDER BY is ignored for this query */
#define SF_UFSrcCheck    0x0800000 /* Check pSrc as required by UPDATE...FROM */
#define SF_PushDown      0x1000000 /* Modified by WHERE-clause push-down opt */
#define SF_MultiPart     0x2000000 /* Has multiple incompatible PARTITIONs */
#define SF_CopyCte       0x4000000 /* SELECT statement is a copy of a CTE */
#define SF_OrderByReqd   0x8000000 /* The ORDER BY clause may not be omitted */
#define SF_UpdateFrom   0x10000000 /* Query originates with UPDATE FROM */
#define SF_Correlated   0x20000000 /* True if references the outer context */

/* True if S exists and has SF_NestedFrom */

Changes to src/wherecode.c.

2470
2471
2472
2473
2474
2475
2476






2477
2478
2479
2480
2481
2482
2483
  **
  ** iLoop==1: Code only expressions that are entirely covered by pIdx.
  ** iLoop==2: Code remaining expressions that do not contain correlated
  **           sub-queries. 
  ** iLoop==3: Code all remaining expressions.
  **
  ** An effort is made to skip unnecessary iterations of the loop.






  */
  iLoop = (pIdx ? 1 : 2);
  do{
    int iNext = 0;                /* Next value for iLoop */
    for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
      Expr *pE;
      int skipLikeAddr = 0;







>
>
>
>
>
>







2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
  **
  ** iLoop==1: Code only expressions that are entirely covered by pIdx.
  ** iLoop==2: Code remaining expressions that do not contain correlated
  **           sub-queries. 
  ** iLoop==3: Code all remaining expressions.
  **
  ** An effort is made to skip unnecessary iterations of the loop.
  **
  ** This optimization of causing simple query restrictions to occur before
  ** more complex one is call the "push-down" optimization in MySQL.  Here
  ** in SQLite, the name is "MySQL push-down", since there is also another
  ** totally unrelated optimization called "WHERE-clause push-down".
  ** Sometimes the qualifier is omitted, resulting in an ambiguity, so beware.
  */
  iLoop = (pIdx ? 1 : 2);
  do{
    int iNext = 0;                /* Next value for iLoop */
    for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
      Expr *pE;
      int skipLikeAddr = 0;

Changes to test/pushdown.test.

1
2
3
4
5
6
7
8
9
10




















11
12
13
14
15
16
17
# 2017 April 29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************





















set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix pushdown

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
|









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







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 2017-04-29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for the push-down optimizations.
#
#
# There are two different meanings for "push-down optimization".
#
#   (1)  "MySQL push-down" means that WHERE clause terms that can be
#        evaluated using only the index and without reference to the
#        table are run first, so that if they are false, unnecessary table
#        seeks are avoided.  See https://sqlite.org/src/info/d7bb79ed3a40419d
#        from 2017-04-29.
#
#   (2)  "WHERE-clause pushdown" means to push WHERE clause terms in
#        outer queries down into subqueries.  See
#        https://sqlite.org/src/info/6df18e949d367629 from 2015-06-02.
#
# This module started out as tests for MySQL push-down only.  But because
# of naming ambiguity, it has picked up test cases for WHERE-clause push-down
# over the years.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix pushdown

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
      SELECT x FROM u2 WHERE x=a AND f('two')
    ) AND f('three')=123
  }
  set L
} {three}

# 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f
# Disable the push-down optimization for compound subqueries if any
# arm of the compound has an incompatible affinity.
#
reset_db
do_execsql_test 3.1 {
  CREATE TABLE t0(c0 INT);
  INSERT INTO t0 VALUES(0);
  CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1_a VALUES(1,'one');







|
|







103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
      SELECT x FROM u2 WHERE x=a AND f('two')
    ) AND f('three')=123
  }
  set L
} {three}

# 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f
# Disable the WHERE-clause push-down optimization for compound subqueries
# if any arm of the compound has an incompatible affinity.
#
reset_db
do_execsql_test 3.1 {
  CREATE TABLE t0(c0 INT);
  INSERT INTO t0 VALUES(0);
  CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1_a VALUES(1,'one');
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
     `--SCAN t2
}
# ^^^^^^^^^^^^^^^^^^^^
# The query should be converted into:
#   SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)

# 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6
# Restriction (9) on the push-down optimization.
# 
reset_db
db null -
do_execsql_test 4.1 {
  CREATE TABLE t1(a INT);
  CREATE TABLE t2(b INT);
  CREATE TABLE t3(c INT);







|







201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
     `--SCAN t2
}
# ^^^^^^^^^^^^^^^^^^^^
# The query should be converted into:
#   SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)

# 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6
# Restriction (9) on the WHERE-clause push-down optimization.
# 
reset_db
db null -
do_execsql_test 4.1 {
  CREATE TABLE t1(a INT);
  CREATE TABLE t2(b INT);
  CREATE TABLE t3(c INT);
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
    FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true
          LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
   WHERE e>0;
} {- - 3 4 5}


# 2024-04-05
# Allow push-down of operators of the form "expr IN table".
#
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t01(w,x,y,z);
  CREATE TABLE t02(w,x,y,z);
  CREATE VIEW t0(w,x,y,z) AS
    SELECT w,x,y,z FROM t01 UNION ALL SELECT w,x,y,z FROM t02;







|







245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
    FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true
          LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
   WHERE e>0;
} {- - 3 4 5}


# 2024-04-05
# Allow WHERE-clause push-down of operators of the form "expr IN table".
#
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t01(w,x,y,z);
  CREATE TABLE t02(w,x,y,z);
  CREATE VIEW t0(w,x,y,z) AS
    SELECT w,x,y,z FROM t01 UNION ALL SELECT w,x,y,z FROM t02;