Debian Codegraph @

test/pushdown.test

raw
1 # 2017-04-29
2 #
3 # The author disclaims copyright to this source code. In place of
4 # 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 be
18 # evaluated using only the index and without reference to the
19 # table are run first, so that if they are false, unnecessary table
20 # seeks are avoided. See https://sqlite.org/src/info/d7bb79ed3a40419d
21 # from 2017-04-29.
22 #
23 # (2) "WHERE-clause pushdown" means to push WHERE clause terms in
24 # outer queries down into subqueries. See
25 # https://sqlite.org/src/info/6df18e949d367629 from 2015-06-02.
26 #
27 # This module started out as tests for MySQL push-down only. But because
28 # of naming ambiguity, it has picked up test cases for WHERE-clause push-down
29 # over the years.
30 #
31
32 set testdir [file dirname $argv0]
33 source $testdir/tester.tcl
34 set testprefix pushdown
35
36 do_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
45 proc f {val} {
46 lappend ::L $val
47 return 0
48 }
49 db func f f
50
51 do_test 1.1 {
52 set L [list]
53 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
54 set L
55 } {c2}
56
57 do_test 1.2 {
58 set L [list]
59 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
60 set L
61 } {c3}
62
63 do_execsql_test 1.3 {
64 DROP INDEX i1;
65 CREATE INDEX i1 ON t1(a, b);
66 }
67 do_test 1.4 {
68 set L [list]
69 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
70 set L
71 } {b2}
72
73 do_test 1.5 {
74 set L [list]
75 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
76 set L
77 } {b3}
78
79 #-----------------------------------------------
80
81 do_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
89 do_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 L
97 } {one}
98
99 do_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')=123
105 }
106 set L
107 } {three}
108
109 # 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f
110 # Disable the WHERE-clause push-down optimization for compound subqueries
111 # if any arm of the compound has an incompatible affinity.
112 #
113 reset_db
114 do_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' 0
126 2 0 0
127 }
128 do_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, v1
131 ) WHERE a=2 AND b='0' AND cd=0;
132 } {}
133 do_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, v1
136 ) WHERE a=1 AND b='one' AND cd=0;
137 } {1 'one' 0}
138 do_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, v1
141 ) WHERE a=2 AND b=0 AND cd=0;
142 } {
143 2 0 0
144 }
145
146 # 2023-02-22 https://sqlite.org/forum/forumpost/bcc4375032
147 # 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 is
149 # no longer necessary after check-in [27655c9353620aa5] from 2022-12-14.
150 #
151 do_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) VALUES
155 (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) VALUES
161 (1,110,'efg'),
162 (2,200,'hij'),
163 (3,330,'klm');
164 CREATE VIEW v3 AS
165 SELECT a, b, c FROM t1
166 UNION ALL
167 SELECT a, b, 'xyz' FROM t2;
168 SELECT * FROM v3 WHERE a=2 AND b=200;
169 } {2 200 def 2 200 xyz}
170 do_eqp_test 3.6 {
171 SELECT * FROM v3 WHERE a=2 AND b=200;
172 } {
173 QUERY PLAN
174 |--CO-ROUTINE v3
175 | `--COMPOUND QUERY
176 | |--LEFT-MOST SUBQUERY
177 | | `--SEARCH t1 USING PRIMARY KEY (a=? AND b=?)
178 | `--UNION ALL
179 | `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
180 `--SCAN v3
181 }
182 # ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
183 # We want both arms of the compound subquery to use the
184 # primary key.
185
186 # The following is a test of the count-of-view optimization. This does
187 # not have anything to do with push-down. It is here because this is a
188 # convenient place to put the test.
189 #
190 do_execsql_test 3.7 {
191 SELECT count(*) FROM v3;
192 } 6
193 do_eqp_test 3.8 {
194 SELECT count(*) FROM v3;
195 } {
196 QUERY PLAN
197 |--SCAN CONSTANT ROW
198 |--SCALAR SUBQUERY xxxxxx
199 | `--SCAN t1
200 `--SCALAR SUBQUERY xxxxxx
201 `--SCAN t2
202 }
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/a7d4be7fb6
208 # Restriction (9) on the WHERE-clause push-down optimization.
209 #
210 reset_db
211 db null -
212 do_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}
223 do_execsql_test 4.2 {
224 SELECT * FROM v6 JOIN t5 ON false RIGHT JOIN t3 ON true;
225 } {- - - 3}
226 do_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/f3f546025a
231 # 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-down
234 # restriction. But it seems convenient to put it here.
235 #
236 reset_db
237 db null -
238 do_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 true
246 LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4
247 WHERE e>0;
248 } {- - 3 4 5}
249
250
251 # 2024-04-05
252 # Allow push-down of operators of the form "expr IN table".
253 #
254 reset_db
255 do_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) AS
259 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 }
266 do_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 PLAN
271 |--CO-ROUTINE t0
272 | `--COMPOUND QUERY
273 | |--LEFT-MOST SUBQUERY
274 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
275 | | `--LIST SUBQUERY xxxxxx
276 | | |--MATERIALIZE k
277 | | | `--SCAN 3 CONSTANT ROWS
278 | | |--SCAN k
279 | | `--CREATE BLOOM FILTER
280 | `--UNION ALL
281 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
282 | `--REUSE LIST SUBQUERY xxxxxx
283 |--SEARCH t0
284 `--REUSE LIST SUBQUERY xxxxxx
285 }
286 # ^^^^--- The key feature above is that the SEARCH for each subquery
287 # uses all three fields of the index w, x, and y. Prior to the push-down
288 # of "expr IN table", only the w term of the index would be used. Similar
289 # for the following tests:
290 #
291 do_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 PLAN
295 |--CO-ROUTINE t0
296 | `--COMPOUND QUERY
297 | |--LEFT-MOST SUBQUERY
298 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
299 | | `--LIST SUBQUERY xxxxxx
300 | | |--CO-ROUTINE v1
301 | | | `--SCAN 3 CONSTANT ROWS
302 | | |--SCAN v1
303 | | `--CREATE BLOOM FILTER
304 | `--UNION ALL
305 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
306 | `--REUSE LIST SUBQUERY xxxxxx
307 |--SEARCH t0
308 `--REUSE LIST SUBQUERY xxxxxx
309 }
310 do_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 PLAN
314 |--CO-ROUTINE t0
315 | `--COMPOUND QUERY
316 | |--LEFT-MOST SUBQUERY
317 | | |--SEARCH t01 USING INDEX t01x (w=? AND x=? AND y>? AND y<?)
318 | | `--LIST SUBQUERY xxxxxx
319 | | |--SCAN k1
320 | | `--CREATE BLOOM FILTER
321 | `--UNION ALL
322 | |--SEARCH t02 USING INDEX t02x (w=? AND x=? AND y>? AND y<?)
323 | `--REUSE LIST SUBQUERY xxxxxx
324 |--SEARCH t0
325 `--REUSE LIST SUBQUERY xxxxxx
326 }
327
328 #-------------------------------------------------------------------------
329 reset_db
330 do_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
341 do_execsql_test 7.1 {
342 SELECT t0_2.c
343 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
350 do_execsql_test 7.2 {
351 SELECT t0_2.c
352 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
359 finish_test