diff options
Diffstat (limited to 'test/pushdown.test')
| -rw-r--r-- | test/pushdown.test | 85 |
1 files changed, 85 insertions, 0 deletions
diff --git a/test/pushdown.test b/test/pushdown.test index 1fbe6f34c..036fe2e2d 100644 --- a/test/pushdown.test +++ b/test/pushdown.test @@ -227,4 +227,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 |