Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -5066,12 +5066,13 @@ } 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. + ** 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; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -5057,10 +5057,22 @@ ** 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 @@ -5123,14 +5135,14 @@ ** (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(). +** 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 ** @@ -7687,11 +7699,11 @@ sqlite3TreeViewSelect(0, p, 0); } #endif assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 ); }else{ - TREETRACE(0x4000,pParse,p,("Push-down not possible\n")); + 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. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1904,11 +1904,11 @@ #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_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 */ @@ -3578,11 +3578,11 @@ #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_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 */ Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -2472,10 +2472,16 @@ ** 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++){ Index: test/pushdown.test ================================================================== --- test/pushdown.test +++ test/pushdown.test @@ -1,15 +1,35 @@ -# 2017 April 29 +# 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 @@ -85,12 +105,12 @@ } 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. +# 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); @@ -183,11 +203,11 @@ # ^^^^^^^^^^^^^^^^^^^^ # 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. +# Restriction (9) on the WHERE-clause push-down optimization. # reset_db db null - do_execsql_test 4.1 { CREATE TABLE t1(a INT); @@ -227,11 +247,11 @@ WHERE e>0; } {- - 3 4 5} # 2024-04-05 -# Allow push-down of operators of the form "expr IN table". +# 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);