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 | 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 | | | > | 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 | ** 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. ** | | | | | | 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 | 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{ | | | 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 | #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 */ | | | 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 | #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 */ | | | 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 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 | SELECT x FROM u2 WHERE x=a AND f('two') ) AND f('three')=123 } set L } {three} # 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f | | | | 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 | `--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 | | | 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 | 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 | | | 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; |
︙ | ︙ |