Debian Codegraph @

test/with6.test

raw
1 # 2021-02-22
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 # This file implements regression tests for SQLite library. The
12 # focus of this file is the MATERIALIZED hint to common table expressions
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix with6
18
19 ifcapable {!cte} {
20 finish_test
21 return
22 }
23
24 do_execsql_test 100 {
25 WITH c(x) AS (VALUES(0),(1))
26 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
27 } {000 001 010 011 100 101 110 111}
28 do_eqp_test 101 {
29 WITH c(x) AS (VALUES(0),(1))
30 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
31 } {
32 QUERY PLAN
33 |--MATERIALIZE c
34 | `--SCAN 2 CONSTANT ROWS
35 |--SCAN c1
36 |--SCAN c2
37 `--SCAN c3
38 }
39
40 do_execsql_test 110 {
41 WITH c(x) AS MATERIALIZED (VALUES(0),(1))
42 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
43 } {000 001 010 011 100 101 110 111}
44 do_eqp_test 111 {
45 WITH c(x) AS MATERIALIZED (VALUES(0),(1))
46 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
47 } {
48 QUERY PLAN
49 |--MATERIALIZE c
50 | `--SCAN 2 CONSTANT ROWS
51 |--SCAN c1
52 |--SCAN c2
53 `--SCAN c3
54 }
55
56 # Even though the CTE is not materialized, the self-join optimization
57 # kicks in and does the materialization for us.
58 #
59 do_execsql_test 120 {
60 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
61 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
62 } {000 001 010 011 100 101 110 111}
63 do_eqp_test 121 {
64 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
65 SELECT c1.x||c2.x||c3.x FROM c c1, c c2, c c3;
66 } {
67 QUERY PLAN
68 |--MATERIALIZE c
69 | `--SCAN 2 CONSTANT ROWS
70 |--SCAN c1
71 |--SCAN c2
72 `--SCAN c3
73 }
74
75 do_execsql_test 130 {
76 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
77 SELECT c1.x||c2.x||c3.x
78 FROM (SELECT x FROM c LIMIT 5) AS c1,
79 (SELECT x FROM c LIMIT 5) AS c2,
80 (SELECT x FROM c LIMIT 5) AS c3;
81 } {000 001 010 011 100 101 110 111}
82 do_eqp_test 131 {
83 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
84 SELECT c1.x||c2.x||c3.x
85 FROM (SELECT x FROM c LIMIT 5) AS c1,
86 (SELECT x FROM c LIMIT 5) AS c2,
87 (SELECT x FROM c LIMIT 5) AS c3;
88 } {
89 QUERY PLAN
90 |--CO-ROUTINE c1
91 | |--CO-ROUTINE c
92 | | `--SCAN 2 CONSTANT ROWS
93 | `--SCAN c
94 |--MATERIALIZE c2
95 | |--CO-ROUTINE c
96 | | `--SCAN 2 CONSTANT ROWS
97 | `--SCAN c
98 |--MATERIALIZE c3
99 | |--CO-ROUTINE c
100 | | `--SCAN 2 CONSTANT ROWS
101 | `--SCAN c
102 |--SCAN c1
103 |--SCAN c2
104 `--SCAN c3
105 }
106
107 # The (SELECT x FROM c LIMIT N) subqueries get materialized once each.
108 # Show multiple materializations are shown. But there is only one
109 # materialization for c, shown by the "SCAN 2 CONSTANT ROWS" line.
110 #
111 do_execsql_test 140 {
112 WITH c(x) AS MATERIALIZED (VALUES(0),(1))
113 SELECT c1.x||c2.x||c3.x
114 FROM (SELECT x FROM c LIMIT 5) AS c1,
115 (SELECT x FROM c LIMIT 6) AS c2,
116 (SELECT x FROM c LIMIT 7) AS c3;
117 } {000 001 010 011 100 101 110 111}
118 do_eqp_test 141 {
119 WITH c(x) AS MATERIALIZED (VALUES(0),(1))
120 SELECT c1.x||c2.x||c3.x
121 FROM (SELECT x FROM c LIMIT 5) AS c1,
122 (SELECT x FROM c LIMIT 6) AS c2,
123 (SELECT x FROM c LIMIT 7) AS c3;
124 } {
125 QUERY PLAN
126 |--CO-ROUTINE c1
127 | |--MATERIALIZE c
128 | | `--SCAN 2 CONSTANT ROWS
129 | `--SCAN c
130 |--MATERIALIZE c2
131 | `--SCAN c
132 |--MATERIALIZE c3
133 | `--SCAN c
134 |--SCAN c1
135 |--SCAN c2
136 `--SCAN c3
137 }
138
139 do_execsql_test 150 {
140 WITH c(x) AS (VALUES(0),(1))
141 SELECT c1.x||c2.x||c3.x
142 FROM (SELECT x FROM c LIMIT 5) AS c1,
143 (SELECT x FROM c LIMIT 6) AS c2,
144 (SELECT x FROM c LIMIT 7) AS c3;
145 } {000 001 010 011 100 101 110 111}
146 do_eqp_test 151 {
147 WITH c(x) AS (VALUES(0),(1))
148 SELECT c1.x||c2.x||c3.x
149 FROM (SELECT x FROM c LIMIT 5) AS c1,
150 (SELECT x FROM c LIMIT 6) AS c2,
151 (SELECT x FROM c LIMIT 7) AS c3;
152 } {
153 QUERY PLAN
154 |--CO-ROUTINE c1
155 | |--MATERIALIZE c
156 | | `--SCAN 2 CONSTANT ROWS
157 | `--SCAN c
158 |--MATERIALIZE c2
159 | `--SCAN c
160 |--MATERIALIZE c3
161 | `--SCAN c
162 |--SCAN c1
163 |--SCAN c2
164 `--SCAN c3
165 }
166
167 do_execsql_test 160 {
168 WITH c(x) AS (VALUES(0),(1))
169 SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x)
170 FROM c AS c2 WHERE c2.x<10;
171 } {100 301}
172 do_eqp_test 161 {
173 WITH c(x) AS (VALUES(0),(1))
174 SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x)
175 FROM c AS c2 WHERE c2.x<10;
176 } {
177 QUERY PLAN
178 |--MATERIALIZE c
179 | `--SCAN 2 CONSTANT ROWS
180 |--SCAN c2
181 `--CORRELATED SCALAR SUBQUERY xxxxxx
182 `--SCAN c
183 }
184
185 do_execsql_test 170 {
186 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
187 SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x)
188 FROM c AS c2 WHERE c2.x<10;
189 } {100 301}
190 do_eqp_test 171 {
191 WITH c(x) AS NOT MATERIALIZED (VALUES(0),(1))
192 SELECT c2.x + 100*(SELECT sum(x+1) FROM c WHERE c.x<=c2.x)
193 FROM c AS c2 WHERE c2.x<10;
194 } {
195 QUERY PLAN
196 |--CO-ROUTINE c
197 | `--SCAN 2 CONSTANT ROWS
198 |--SCAN c2
199 `--CORRELATED SCALAR SUBQUERY xxxxxx
200 |--CO-ROUTINE c
201 | `--SCAN 2 CONSTANT ROWS
202 `--SCAN c
203 }
204
205
206 do_execsql_test 200 {
207 CREATE TABLE t1(x);
208 INSERT INTO t1(x) VALUES(4);
209 CREATE VIEW t2(y) AS
210 WITH c(z) AS (VALUES(4),(5),(6))
211 SELECT c1.z+c2.z*100+t1.x*10000
212 FROM t1,
213 (SELECT z FROM c LIMIT 5) AS c1,
214 (SELECT z FROM c LIMIT 5) AS c2;
215 SELECT y FROM t2 ORDER BY y;
216 } {40404 40405 40406 40504 40505 40506 40604 40605 40606}
217 do_execsql_test 210 {
218 DROP VIEW t2;
219 CREATE VIEW t2(y) AS
220 WITH c(z) AS NOT MATERIALIZED (VALUES(4),(5),(6))
221 SELECT c1.z+c2.z*100+t1.x*10000
222 FROM t1,
223 (SELECT z FROM c LIMIT 5) AS c1,
224 (SELECT z FROM c LIMIT 5) AS c2;
225 SELECT y FROM t2 ORDER BY y;
226 } {40404 40405 40406 40504 40505 40506 40604 40605 40606}
227 do_eqp_test 211 {
228 SELECT y FROM t2 ORDER BY y;
229 } {
230 QUERY PLAN
231 |--CO-ROUTINE c1
232 | |--CO-ROUTINE c
233 | | `--SCAN 3 CONSTANT ROWS
234 | `--SCAN c
235 |--MATERIALIZE c2
236 | |--CO-ROUTINE c
237 | | `--SCAN 3 CONSTANT ROWS
238 | `--SCAN c
239 |--SCAN c1
240 |--SCAN c2
241 |--SCAN t1
242 `--USE TEMP B-TREE FOR ORDER BY
243 }
244 do_execsql_test 220 {
245 DROP VIEW t2;
246 CREATE VIEW t2(y) AS
247 WITH c(z) AS MATERIALIZED (VALUES(4),(5),(6))
248 SELECT c1.z+c2.z*100+t1.x*10000
249 FROM t1,
250 (SELECT z FROM c LIMIT 5) AS c1,
251 (SELECT z FROM c LIMIT 5) AS c2;
252 SELECT y FROM t2 ORDER BY y;
253 } {40404 40405 40406 40504 40505 40506 40604 40605 40606}
254
255 # 2022-04-22: Do not allow flattening of a MATERIALIZED CTE into
256 # an outer query.
257 #
258 reset_db
259 db null -
260 do_execsql_test 300 {
261 CREATE TABLE t2(a INT,b INT,d INT); INSERT INTO t2 VALUES(4,5,6),(7,8,9);
262 CREATE TABLE t3(a INT,b INT,e INT); INSERT INTO t3 VALUES(3,3,3),(8,8,8);
263 } {}
264 do_execsql_test 310 {
265 WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
266 SELECT * FROM t23;
267 } {
268 4 5 6 - -
269 7 8 9 8 8
270 - 3 - 3 3
271 }
272 do_eqp_test 311 {
273 WITH t23 AS MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
274 SELECT * FROM t23;
275 } {
276 QUERY PLAN
277 |--MATERIALIZE t23
278 | |--SCAN t2
279 | |--SCAN t3 LEFT-JOIN
280 | `--RIGHT-JOIN t3
281 | `--SCAN t3
282 `--SCAN t23
283 }
284 do_execsql_test 320 {
285 WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
286 SELECT * FROM t23;
287 } {
288 4 5 6 - -
289 7 8 9 8 8
290 - 3 - 3 3
291 }
292 do_eqp_test 321 {
293 WITH t23 AS NOT MATERIALIZED (SELECT * FROM t2 FULL JOIN t3 USING(b))
294 SELECT * FROM t23;
295 } {
296 QUERY PLAN
297 |--SCAN t2
298 |--SCAN t3 LEFT-JOIN
299 `--RIGHT-JOIN t3
300 `--SCAN t3
301 }
302 do_execsql_test 330 {
303 WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b))
304 SELECT * FROM t23;
305 } {
306 4 5 6 - -
307 7 8 9 8 8
308 - 3 - 3 3
309 }
310 do_eqp_test 331 {
311 WITH t23 AS (SELECT * FROM t2 FULL JOIN t3 USING(b))
312 SELECT * FROM t23;
313 } {
314 QUERY PLAN
315 |--SCAN t2
316 |--SCAN t3 LEFT-JOIN
317 `--RIGHT-JOIN t3
318 `--SCAN t3
319 }
320
321 # 2023-02-01
322 # https://sqlite.org/forum/forumpost/1d571c02963355ed
323 #
324 # Just because a CTE is used more than once, does not mean it should be
325 # marked with M10d_Yes and hence prohibited from participating in the
326 # query flattening optimization.
327 #
328 # Updated 2025-01-02.
329 # https://sqlite.org/forum/forumpost/8f38fc9878a92aa9
330 #
331 # The same optimization that made Grunthos's query fast made
332 # Jean-Noël Mayor's query slow. Bummer.
333 #
334 reset_db
335 db eval {
336 CREATE TABLE raw(country,date,total,delta, UNIQUE(country,date));
337 }
338 do_eqp_test 400 {
339 with recursive
340 init(country, date, fin) AS (SELECT country, min(date), max(date) FROM raw WHERE total > 0 GROUP BY country),
341 src(country, date) AS (SELECT raw.country, raw.date
342 FROM raw JOIN init i on raw.country = i.country AND raw.date > i.date
343 ORDER BY raw.country, raw.date),
344 vals(country, date, x, y) AS (SELECT src.country, src.date, julianday(raw.date) - julianday(src.date), log(delta+1)
345 FROM src JOIN raw on raw.country = src.country AND raw.date > date(src.date,'-7 days') AND raw.date <= src.date AND delta >= 0),
346 sums(country, date, x2, x, n, xy, y) AS (SELECT country, date, sum(x*x*1.0), sum(x*1.0), sum(1.0), sum(x*y*1.0), sum(y*1.0) FROM vals GROUP BY 1, 2),
347 mult(country, date, m) AS (SELECT country, date, 1.0/(x2 * n - x * x) FROM sums),
348 inv(country, date, a,b,c,d) AS (SELECT mult.country, mult.date, n * m, -x * m, -x * m, x2 * m
349 FROM mult JOIN sums on sums.country=mult.country AND mult.date=sums.date),
350 fit(country, date, a, b) AS (SELECT inv.country, inv.date, a * xy + b * y, c * xy + d * y
351 FROM inv
352 JOIN mult on mult.country = inv.country AND mult.date = inv.date
353 JOIN sums on sums.country = mult.country AND sums.date = mult.date
354 )
355 SELECT *, nFin/nPrev - 1 AS growth, log(2)/log(nFin/nPrev) AS doubling
356 FROM (SELECT f.*, exp(b) - 1 AS nFin, exp(a* (-1) + b) - 1 AS nPrev
357 FROM fit f JOIN init i on i.country = f.country AND f.date <= date(i.fin,'-3 days'))
358 WHERE nPrev > 0 AND nFin > 0;
359 } {
360 QUERY PLAN
361 |--MATERIALIZE sums
362 | |--MATERIALIZE src
363 | | |--MATERIALIZE init
364 | | | `--SCAN raw USING INDEX sqlite_autoindex_raw_1
365 | | |--SCAN i
366 | | |--SEARCH raw USING COVERING INDEX sqlite_autoindex_raw_1 (country=? AND date>?)
367 | | `--USE TEMP B-TREE FOR ORDER BY
368 | |--SCAN src
369 | `--SEARCH raw USING INDEX sqlite_autoindex_raw_1 (country=? AND date>? AND date<?)
370 |--SCAN sums
371 |--BLOOM FILTER ON sums (country=? AND date=?)
372 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
373 |--BLOOM FILTER ON sums (country=? AND date=?)
374 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
375 |--BLOOM FILTER ON sums (country=? AND date=?)
376 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
377 |--BLOOM FILTER ON i (country=?)
378 `--SEARCH i USING AUTOMATIC COVERING INDEX (country=?)
379 }
380 optimization_control db order-by-subquery off
381 db cache flush
382 do_eqp_test 410 {
383 with recursive
384 init(country, date, fin) AS (SELECT country, min(date), max(date) FROM raw WHERE total > 0 GROUP BY country),
385 src(country, date) AS (SELECT raw.country, raw.date
386 FROM raw JOIN init i on raw.country = i.country AND raw.date > i.date
387 ORDER BY raw.country, raw.date),
388 vals(country, date, x, y) AS (SELECT src.country, src.date, julianday(raw.date) - julianday(src.date), log(delta+1)
389 FROM src JOIN raw on raw.country = src.country AND raw.date > date(src.date,'-7 days') AND raw.date <= src.date AND delta >= 0),
390 sums(country, date, x2, x, n, xy, y) AS (SELECT country, date, sum(x*x*1.0), sum(x*1.0), sum(1.0), sum(x*y*1.0), sum(y*1.0) FROM vals GROUP BY 1, 2),
391 mult(country, date, m) AS (SELECT country, date, 1.0/(x2 * n - x * x) FROM sums),
392 inv(country, date, a,b,c,d) AS (SELECT mult.country, mult.date, n * m, -x * m, -x * m, x2 * m
393 FROM mult JOIN sums on sums.country=mult.country AND mult.date=sums.date),
394 fit(country, date, a, b) AS (SELECT inv.country, inv.date, a * xy + b * y, c * xy + d * y
395 FROM inv
396 JOIN mult on mult.country = inv.country AND mult.date = inv.date
397 JOIN sums on sums.country = mult.country AND sums.date = mult.date
398 )
399 SELECT *, nFin/nPrev - 1 AS growth, log(2)/log(nFin/nPrev) AS doubling
400 FROM (SELECT f.*, exp(b) - 1 AS nFin, exp(a* (-1) + b) - 1 AS nPrev
401 FROM fit f JOIN init i on i.country = f.country AND f.date <= date(i.fin,'-3 days'))
402 WHERE nPrev > 0 AND nFin > 0;
403 } {
404 QUERY PLAN
405 |--MATERIALIZE sums
406 | |--MATERIALIZE src
407 | | |--MATERIALIZE init
408 | | | `--SCAN raw USING INDEX sqlite_autoindex_raw_1
409 | | |--SCAN i
410 | | |--SEARCH raw USING COVERING INDEX sqlite_autoindex_raw_1 (country=? AND date>?)
411 | | `--USE TEMP B-TREE FOR ORDER BY
412 | |--SCAN src
413 | |--SEARCH raw USING INDEX sqlite_autoindex_raw_1 (country=? AND date>? AND date<?)
414 | `--USE TEMP B-TREE FOR GROUP BY
415 |--SCAN sums
416 |--BLOOM FILTER ON sums (country=? AND date=?)
417 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
418 |--BLOOM FILTER ON sums (country=? AND date=?)
419 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
420 |--BLOOM FILTER ON sums (country=? AND date=?)
421 |--SEARCH sums USING AUTOMATIC COVERING INDEX (country=? AND date=?)
422 |--BLOOM FILTER ON i (country=?)
423 `--SEARCH i USING AUTOMATIC COVERING INDEX (country=?)
424 }
425
426
427 finish_test