AryaWu/sqlite
0
1# 2017-04-292#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# Test cases for the push-down optimizations.13#14#15# There are two different meanings for "push-down optimization".16#17# (1) "MySQL push-down" means that WHERE clause terms that can be18# evaluated using only the index and without reference to the19# table are run first, so that if they are false, unnecessary table20# seeks are avoided. See https://sqlite.org/src/info/d7bb79ed3a40419d21# from 2017-04-29.22#23# (2) "WHERE-clause pushdown" means to push WHERE clause terms in24# outer queries down into subqueries. See25# https://sqlite.org/src/info/6df18e949d367629 from 2015-06-02.26#27# This module started out as tests for MySQL push-down only. But because28# of naming ambiguity, it has picked up test cases for WHERE-clause push-down29# over the years.30#31 32set testdir [file dirname $argv0]33source $testdir/tester.tcl34set testprefix pushdown35 36do_execsql_test 1.0 {37 CREATE TABLE t1(a, b, c);38 INSERT INTO t1 VALUES(1, 'b1', 'c1');39 INSERT INTO t1 VALUES(2, 'b2', 'c2');40 INSERT INTO t1 VALUES(3, 'b3', 'c3');41 INSERT INTO t1 VALUES(4, 'b4', 'c4');42 CREATE INDEX i1 ON t1(a, c);43}44 45proc f {val} {46 lappend ::L $val47 return 048}49db func f f 50 51do_test 1.1 {52 set L [list]53 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }54 set L55} {c2}56 57do_test 1.2 {58 set L [list]59 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }60 set L61} {c3}62 63do_execsql_test 1.3 {64 DROP INDEX i1;65 CREATE INDEX i1 ON t1(a, b);66}67do_test 1.4 {68 set L [list]69 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }70 set L71} {b2}72 73do_test 1.5 {74 set L [list]75 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }76 set L77} {b3}78 79#-----------------------------------------------80 81do_execsql_test 2.0 {82 CREATE TABLE u1(a, b, c);83 CREATE TABLE u2(x, y, z);84 85 INSERT INTO u1 VALUES('a1', 'b1', 'c1');86 INSERT INTO u2 VALUES('a1', 'b1', 'c1');87}88 89do_test 2.1 {90 set L [list]91 execsql {92 SELECT * FROM u1 WHERE f('one')=123 AND 123=(93 SELECT x FROM u2 WHERE x=a AND f('two')94 )95 }96 set L97} {one}98 99do_test 2.2 {100 set L [list]101 execsql {102 SELECT * FROM u1 WHERE 123=(103 SELECT x FROM u2 WHERE x=a AND f('two')104 ) AND f('three')=123105 }106 set L107} {three}108 109# 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f110# Disable the WHERE-clause push-down optimization for compound subqueries111# if any arm of the compound has an incompatible affinity.112#113reset_db114do_execsql_test 3.1 {115 CREATE TABLE t0(c0 INT);116 INSERT INTO t0 VALUES(0);117 CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);118 INSERT INTO t1_a VALUES(1,'one');119 CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);120 INSERT INTO t1_b VALUES(2,'two');121 CREATE VIEW v0 AS SELECT CAST(t0.c0 AS INTEGER) AS c0 FROM t0;122 CREATE VIEW v1(a,b) AS SELECT a, b FROM t1_a UNION ALL SELECT c, 0 FROM t1_b;123 SELECT v1.a, quote(v1.b), t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0,v1;124} {125 1 'one' 0126 2 0 0127}128do_execsql_test 3.2 {129 SELECT a, quote(b), cd FROM (130 SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1131 ) WHERE a=2 AND b='0' AND cd=0;132} {}133do_execsql_test 3.3 {134 SELECT a, quote(b), cd FROM (135 SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1136 ) WHERE a=1 AND b='one' AND cd=0;137} {1 'one' 0}138do_execsql_test 3.4 {139 SELECT a, quote(b), cd FROM (140 SELECT v1.a, v1.b, t0.c0 AS cd FROM t0 LEFT JOIN v0 ON v0.c0!=0, v1141 ) WHERE a=2 AND b=0 AND cd=0;142} {143 2 0 0144}145 146# 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032147# Performance regression caused by check-in [1ad41840c5e0fa70] from 2022-11-25.148# That check-in added a new restriction on push-down. The new restriction is149# no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.150#151do_execsql_test 3.5 {152 DROP TABLE IF EXISTS t1;153 CREATE TABLE t1(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;154 INSERT INTO t1(a,b,c) VALUES155 (1,100,'abc'),156 (2,200,'def'),157 (3,300,'abc');158 DROP TABLE IF EXISTS t2;159 CREATE TABLE t2(a INT, b INT, c TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;160 INSERT INTO t2(a,b,c) VALUES161 (1,110,'efg'),162 (2,200,'hij'),163 (3,330,'klm');164 CREATE VIEW v3 AS165 SELECT a, b, c FROM t1166 UNION ALL167 SELECT a, b, 'xyz' FROM t2;168 SELECT * FROM v3 WHERE a=2 AND b=200;169} {2 200 def 2 200 xyz}170do_eqp_test 3.6 {171 SELECT * FROM v3 WHERE a=2 AND b=200;172} {173 QUERY PLAN174 |--CO-ROUTINE v3175 | `--COMPOUND QUERY176 | |--LEFT-MOST SUBQUERY177 | | `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)178 | `--UNION ALL179 | `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)180 `--SCAN v3181}182# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^183# We want both arms of the compound subquery to use the184# primary key.185 186# The following is a test of the count-of-view optimization. This does187# not have anything to do with push-down. It is here because this is a188# convenient place to put the test.189#190do_execsql_test 3.7 {191 SELECT count(*) FROM v3;192} 6193do_eqp_test 3.8 {194 SELECT count(*) FROM v3;195} {196 QUERY PLAN197 |--SCAN CONSTANT ROW198 |--SCALAR SUBQUERY xxxxxx199 | `--SCAN t1200 `--SCALAR SUBQUERY xxxxxx201 `--SCAN t2202}203# ^^^^^^^^^^^^^^^^^^^^204# The query should be converted into:205# SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)206 207# 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6208# Restriction (9) on the WHERE-clause push-down optimization.209# 210reset_db211db null -212do_execsql_test 4.1 {213 CREATE TABLE t1(a INT);214 CREATE TABLE t2(b INT);215 CREATE TABLE t3(c INT);216 INSERT INTO t3(c) VALUES(3);217 CREATE TABLE t4(d INT);218 CREATE TABLE t5(e INT);219 INSERT INTO t5(e) VALUES(5);220 CREATE VIEW v6(f,g) AS SELECT d, e FROM t4 RIGHT JOIN t5 ON true;221 SELECT * FROM t1 JOIN t2 ON false RIGHT JOIN t3 ON true CROSS JOIN v6;222} {- - 3 - 5}223do_execsql_test 4.2 {224 SELECT * FROM v6 JOIN t5 ON false RIGHT JOIN t3 ON true;225} {- - - 3}226do_execsql_test 4.3 {227 SELECT * FROM t1 JOIN t2 ON false JOIN v6 ON true RIGHT JOIN t3 ON true;228} {- - - - 3}229 230# 2023-05-15 https://sqlite.org/forum/forumpost/f3f546025a231# This is restriction (6) on sqlite3ExprIsSingleTableConstraint().232# That restriction (now) used to implement restriction (9) on push-down.233# It is used for other things too, so it is not purely a push-down234# restriction. But it seems convenient to put it here.235#236reset_db237db null -238do_execsql_test 5.0 {239 CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1);240 CREATE TABLE t2(b INT); INSERT INTO t2 VALUES(2);241 CREATE TABLE t3(c INT); INSERT INTO t3 VALUES(3);242 CREATE TABLE t4(d INT); INSERT INTO t4 VALUES(4);243 CREATE TABLE t5(e INT); INSERT INTO t5 VALUES(5);244 SELECT *245 FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true246 LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4247 WHERE e>0;248} {- - 3 4 5}249 250 251# 2024-04-05252# Allow push-down of operators of the form "expr IN table".253#254reset_db255do_execsql_test 6.0 {256 CREATE TABLE t01(w,x,y,z);257 CREATE TABLE t02(w,x,y,z);258 CREATE VIEW t0(w,x,y,z) AS259 SELECT w,x,y,z FROM t01 UNION ALL SELECT w,x,y,z FROM t02;260 CREATE INDEX t01x ON t01(w,x,y);261 CREATE INDEX t02x ON t02(w,x,y);262 CREATE VIEW v1(k) AS VALUES(77),(88),(99);263 CREATE TABLE k1(k);264 INSERT INTO k1 SELECT * FROM v1;265}266do_eqp_test 6.1 {267 WITH k(n) AS (VALUES(77),(88),(99))268 SELECT max(z) FROM t0 WHERE w=123 AND x IN k AND y BETWEEN 44 AND 55;269} {270 QUERY PLAN271 |--CO-ROUTINE t0272 | `--COMPOUND QUERY273 | |--LEFT-MOST SUBQUERY274 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)275 | | `--LIST SUBQUERY xxxxxx276 | | |--MATERIALIZE k277 | | | `--SCAN 3 CONSTANT ROWS278 | | |--SCAN k279 | | `--CREATE BLOOM FILTER280 | `--UNION ALL281 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)282 | `--REUSE LIST SUBQUERY xxxxxx283 |--SEARCH t0284 `--REUSE LIST SUBQUERY xxxxxx285}286# ^^^^--- The key feature above is that the SEARCH for each subquery287# uses all three fields of the index w, x, and y. Prior to the push-down288# of "expr IN table", only the w term of the index would be used. Similar289# for the following tests:290#291do_eqp_test 6.2 {292 SELECT max(z) FROM t0 WHERE w=123 AND x IN v1 AND y BETWEEN 44 AND 55;293} {294 QUERY PLAN295 |--CO-ROUTINE t0296 | `--COMPOUND QUERY297 | |--LEFT-MOST SUBQUERY298 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)299 | | `--LIST SUBQUERY xxxxxx300 | | |--CO-ROUTINE v1301 | | | `--SCAN 3 CONSTANT ROWS302 | | |--SCAN v1303 | | `--CREATE BLOOM FILTER304 | `--UNION ALL305 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)306 | `--REUSE LIST SUBQUERY xxxxxx307 |--SEARCH t0308 `--REUSE LIST SUBQUERY xxxxxx309}310do_eqp_test 6.3 {311 SELECT max(z) FROM t0 WHERE w=123 AND x IN k1 AND y BETWEEN 44 AND 55;312} {313 QUERY PLAN314 |--CO-ROUTINE t0315 | `--COMPOUND QUERY316 | |--LEFT-MOST SUBQUERY317 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)318 | | `--LIST SUBQUERY xxxxxx319 | | |--SCAN k1320 | | `--CREATE BLOOM FILTER321 | `--UNION ALL322 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)323 | `--REUSE LIST SUBQUERY xxxxxx324 |--SEARCH t0325 `--REUSE LIST SUBQUERY xxxxxx326}327 328#-------------------------------------------------------------------------329reset_db330do_execsql_test 7.0 {331 CREATE TABLE t0_1(a INT , b INT, c INT);332 CREATE TABLE t0_2(a INT , b INT, c INT);333 334 INSERT INTO t0_1 (a, b, c) VALUES (1, 0, 1);335 INSERT INTO t0_2 (a, b, c) VALUES (1, 0, 1);336 337 CREATE TABLE empty1(x);338 CREATE TABLE empty2(y);339}340 341do_execsql_test 7.1 {342 SELECT t0_2.c343 FROM (SELECT '0000' AS c0 FROM empty2 RIGHT JOIN t0_1 ON 1) AS v0 344 LEFT JOIN empty1 ON v0.c0, t0_2 345 RIGHT JOIN (346 SELECT 5678 AS col0 FROM (SELECT 0)347 ) AS sub1 ON 1;348} {1}349 350do_execsql_test 7.2 {351 SELECT t0_2.c352 FROM (SELECT '0000' AS c0 FROM empty2 RIGHT JOIN t0_1 ON 1) AS v0 353 LEFT JOIN empty1 ON v0.c0, t0_2 354 RIGHT JOIN (355 SELECT 5678 AS col0 FROM (SELECT 0)356 ) AS sub1 ON 1 WHERE +t0_2.c;357} {1}358 359finish_test360