diff options
Diffstat (limited to 'test/pushdown.test')
| -rw-r--r-- | test/pushdown.test | 113 |
1 files changed, 109 insertions, 4 deletions
diff --git a/test/pushdown.test b/test/pushdown.test index 1fbe6f3..5c3e818 100644 --- a/test/pushdown.test +++ b/test/pushdown.test @@ -1,4 +1,4 @@ -# 2017 April 29 +# 2017-04-29 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: @@ -8,6 +8,26 @@ # 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 @@ -87,8 +107,8 @@ do_test 2.2 { } {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 { @@ -185,7 +205,7 @@ do_eqp_test 3.8 { # 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 - @@ -227,4 +247,89 @@ do_execsql_test 5.0 { 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; + CREATE INDEX t01x ON t01(w,x,y); + CREATE INDEX t02x ON t02(w,x,y); + CREATE VIEW v1(k) AS VALUES(77),(88),(99); + CREATE TABLE k1(k); + INSERT INTO k1 SELECT * FROM v1; +} +do_eqp_test 6.1 { + WITH k(n) AS (VALUES(77),(88),(99)) + SELECT max(z) FROM t0 WHERE w=123 AND x IN k AND y BETWEEN 44 AND 55; +} { + QUERY PLAN + |--CO-ROUTINE t0 + | `--COMPOUND QUERY + | |--LEFT-MOST SUBQUERY + | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?) + | | `--LIST SUBQUERY xxxxxx + | | |--MATERIALIZE k + | | | `--SCAN 3 CONSTANT ROWS + | | `--SCAN k + | `--UNION ALL + | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?) + | `--LIST SUBQUERY xxxxxx + | `--SCAN k + |--SEARCH t0 + `--LIST SUBQUERY xxxxxx + `--SCAN k +} +# ^^^^--- The key feature above is that the SEARCH for each subquery +# uses all three fields of the index w, x, and y. Prior to the push-down +# of "expr IN table", only the w term of the index would be used. Similar +# for the following tests: +# +do_eqp_test 6.2 { + SELECT max(z) FROM t0 WHERE w=123 AND x IN v1 AND y BETWEEN 44 AND 55; +} { + QUERY PLAN + |--CO-ROUTINE t0 + | `--COMPOUND QUERY + | |--LEFT-MOST SUBQUERY + | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?) + | | `--LIST SUBQUERY xxxxxx + | | |--CO-ROUTINE v1 + | | | `--SCAN 3 CONSTANT ROWS + | | `--SCAN v1 + | `--UNION ALL + | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?) + | `--LIST SUBQUERY xxxxxx + | |--CO-ROUTINE v1 + | | `--SCAN 3 CONSTANT ROWS + | `--SCAN v1 + |--SEARCH t0 + `--LIST SUBQUERY xxxxxx + |--CO-ROUTINE v1 + | `--SCAN 3 CONSTANT ROWS + `--SCAN v1 +} +do_eqp_test 6.3 { + SELECT max(z) FROM t0 WHERE w=123 AND x IN k1 AND y BETWEEN 44 AND 55; +} { + QUERY PLAN + |--CO-ROUTINE t0 + | `--COMPOUND QUERY + | |--LEFT-MOST SUBQUERY + | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?) + | | `--LIST SUBQUERY xxxxxx + | | `--SCAN k1 + | `--UNION ALL + | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?) + | `--LIST SUBQUERY xxxxxx + | `--SCAN k1 + |--SEARCH t0 + `--LIST SUBQUERY xxxxxx + `--SCAN k1 +} + finish_test |