Debian Codegraph @

test/subquery.test

raw
1 # 2005 January 19
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 script is testing correlated subqueries
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 ifcapable !subquery {
19 finish_test
20 return
21 }
22
23 do_test subquery-1.1 {
24 execsql {
25 BEGIN;
26 CREATE TABLE t1(a,b);
27 INSERT INTO t1 VALUES(1,2);
28 INSERT INTO t1 VALUES(3,4);
29 INSERT INTO t1 VALUES(5,6);
30 INSERT INTO t1 VALUES(7,8);
31 CREATE TABLE t2(x,y);
32 INSERT INTO t2 VALUES(1,1);
33 INSERT INTO t2 VALUES(3,9);
34 INSERT INTO t2 VALUES(5,25);
35 INSERT INTO t2 VALUES(7,49);
36 COMMIT;
37 }
38 execsql {
39 SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
40 }
41 } {1 1 3 9 5 25}
42 do_test subquery-1.2 {
43 execsql {
44 UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
45 SELECT * FROM t1;
46 }
47 } {1 3 3 13 5 31 7 57}
48
49 do_test subquery-1.3 {
50 execsql {
51 SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
52 }
53 } {3}
54 do_test subquery-1.4 {
55 execsql {
56 SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
57 }
58 } {13 31 57}
59
60 # Simple tests to make sure correlated subqueries in WHERE clauses
61 # are used by the query optimizer correctly.
62 do_test subquery-1.5 {
63 execsql {
64 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
65 }
66 } {1 1 3 3 5 5 7 7}
67 do_test subquery-1.6 {
68 execsql {
69 CREATE INDEX i1 ON t1(a);
70 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
71 }
72 } {1 1 3 3 5 5 7 7}
73 do_test subquery-1.7 {
74 execsql {
75 SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
76 }
77 } {1 1 3 3 5 5 7 7}
78
79 # Try an aggregate in both the subquery and the parent query.
80 do_test subquery-1.8 {
81 execsql {
82 SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
83 }
84 } {2}
85
86 # Test a correlated subquery disables the "only open the index" optimization.
87 do_test subquery-1.9.1 {
88 execsql {
89 SELECT (y*2)>b FROM t1, t2 WHERE a=x;
90 }
91 } {0 1 1 1}
92 do_test subquery-1.9.2 {
93 execsql {
94 SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
95 }
96 } {3 5 7}
97
98 # Test that the flattening optimization works with subquery expressions.
99 do_test subquery-1.10.1 {
100 execsql {
101 SELECT (SELECT a), b FROM t1;
102 }
103 } {1 3 3 13 5 31 7 57}
104 do_test subquery-1.10.2 {
105 execsql {
106 SELECT * FROM (SELECT (SELECT a), b FROM t1);
107 }
108 } {1 3 3 13 5 31 7 57}
109 do_test subquery-1.10.3 {
110 execsql {
111 SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
112 }
113 } {16}
114 do_test subquery-1.10.4 {
115 execsql {
116 CREATE TABLE t5 (val int, period text PRIMARY KEY);
117 INSERT INTO t5 VALUES(5, '2001-3');
118 INSERT INTO t5 VALUES(10, '2001-4');
119 INSERT INTO t5 VALUES(15, '2002-1');
120 INSERT INTO t5 VALUES(5, '2002-2');
121 INSERT INTO t5 VALUES(10, '2002-3');
122 INSERT INTO t5 VALUES(15, '2002-4');
123 INSERT INTO t5 VALUES(10, '2003-1');
124 INSERT INTO t5 VALUES(5, '2003-2');
125 INSERT INTO t5 VALUES(25, '2003-3');
126 INSERT INTO t5 VALUES(5, '2003-4');
127
128 SELECT period, vsum
129 FROM (SELECT
130 a.period,
131 (select sum(val) from t5 where period between a.period and '2002-4') vsum
132 FROM t5 a where a.period between '2002-1' and '2002-4')
133 WHERE vsum < 45 ;
134 }
135 } {2002-2 30 2002-3 25 2002-4 15}
136 do_test subquery-1.10.5 {
137 execsql {
138 SELECT period, vsum from
139 (select a.period,
140 (select sum(val) from t5 where period between a.period and '2002-4') vsum
141 FROM t5 a where a.period between '2002-1' and '2002-4')
142 WHERE vsum < 45 ;
143 }
144 } {2002-2 30 2002-3 25 2002-4 15}
145 do_test subquery-1.10.6 {
146 execsql {
147 DROP TABLE t5;
148 }
149 } {}
150
151
152
153 #------------------------------------------------------------------
154 # The following test cases - subquery-2.* - are not logically
155 # organized. They're here largely because they were failing during
156 # one stage of development of sub-queries.
157 #
158 do_test subquery-2.1 {
159 execsql {
160 SELECT (SELECT 10);
161 }
162 } {10}
163 do_test subquery-2.2.1 {
164 execsql {
165 CREATE TABLE t3(a PRIMARY KEY, b);
166 INSERT INTO t3 VALUES(1, 2);
167 INSERT INTO t3 VALUES(3, 1);
168 }
169 } {}
170 do_test subquery-2.2.2 {
171 execsql {
172 SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
173 }
174 } {1 2}
175 do_test subquery-2.2.3 {
176 execsql {
177 DROP TABLE t3;
178 }
179 } {}
180 do_test subquery-2.3.1 {
181 execsql {
182 CREATE TABLE t3(a TEXT);
183 INSERT INTO t3 VALUES('10');
184 }
185 } {}
186 do_test subquery-2.3.2 {
187 execsql {
188 SELECT a IN (10.0, 20) FROM t3;
189 }
190 } {0}
191 do_test subquery-2.3.3 {
192 execsql {
193 DROP TABLE t3;
194 }
195 } {}
196 do_test subquery-2.4.1 {
197 execsql {
198 CREATE TABLE t3(a TEXT);
199 INSERT INTO t3 VALUES('XX');
200 }
201 } {}
202 do_test subquery-2.4.2 {
203 execsql {
204 SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
205 }
206 } {1}
207 do_test subquery-2.4.3 {
208 execsql {
209 DROP TABLE t3;
210 }
211 } {}
212 do_test subquery-2.5.1 {
213 execsql {
214 CREATE TABLE t3(a INTEGER);
215 INSERT INTO t3 VALUES(10);
216
217 CREATE TABLE t4(x TEXT);
218 INSERT INTO t4 VALUES('10.0');
219 }
220 } {}
221 do_test subquery-2.5.2 {
222 # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
223 # has text affinity and the LHS has integer affinity. The rule is
224 # that we try to convert both sides to an integer before doing the
225 # comparision. Hence, the integer value 10 in t3 will compare equal
226 # to the string value '10.0' in t4 because the t4 value will be
227 # converted into an integer.
228 execsql {
229 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
230 }
231 } {10.0}
232 do_test subquery-2.5.3.1 {
233 # The t4i index cannot be used to resolve the "x IN (...)" constraint
234 # because the constraint has integer affinity but t4i has text affinity.
235 execsql {
236 CREATE INDEX t4i ON t4(x);
237 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
238 }
239 } {10.0}
240 do_test subquery-2.5.3.2 {
241 # Verify that the t4i index was not used in the previous query
242 execsql {
243 EXPLAIN QUERY PLAN
244 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
245 }
246 } {~/t4i/}
247 do_test subquery-2.5.4 {
248 execsql {
249 DROP TABLE t3;
250 DROP TABLE t4;
251 }
252 } {}
253
254 #------------------------------------------------------------------
255 # The following test cases - subquery-3.* - test tickets that
256 # were raised during development of correlated subqueries.
257 #
258
259 # Ticket 1083
260 ifcapable view {
261 do_test subquery-3.1 {
262 catchsql { DROP TABLE t1; }
263 catchsql { DROP TABLE t2; }
264 execsql {
265 CREATE TABLE t1(a,b);
266 INSERT INTO t1 VALUES(1,2);
267 CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
268 CREATE TABLE t2(p,q);
269 INSERT INTO t2 VALUES(2,9);
270 SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
271 }
272 } {2}
273 do_test subquery-3.1.1 {
274 execsql {
275 SELECT * FROM v1 WHERE EXISTS(SELECT 1);
276 }
277 } {2}
278 } else {
279 catchsql { DROP TABLE t1; }
280 catchsql { DROP TABLE t2; }
281 execsql {
282 CREATE TABLE t1(a,b);
283 INSERT INTO t1 VALUES(1,2);
284 CREATE TABLE t2(p,q);
285 INSERT INTO t2 VALUES(2,9);
286 }
287 }
288
289 # Ticket 1084
290 do_test subquery-3.2 {
291 catchsql {
292 CREATE TABLE t1(a,b);
293 INSERT INTO t1 VALUES(1,2);
294 }
295 execsql {
296 SELECT (SELECT t1.a) FROM t1;
297 }
298 } {1}
299
300 # Test Cases subquery-3.3.* test correlated subqueries where the
301 # parent query is an aggregate query. Ticket #1105 is an example
302 # of such a query.
303 #
304 do_test subquery-3.3.1 {
305 execsql {
306 SELECT a, (SELECT b) FROM t1 GROUP BY a;
307 }
308 } {1 2}
309 do_test subquery-3.3.2 {
310 catchsql {DROP TABLE t2}
311 execsql {
312 CREATE TABLE t2(c, d);
313 INSERT INTO t2 VALUES(1, 'one');
314 INSERT INTO t2 VALUES(2, 'two');
315 SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
316 }
317 } {1 one}
318 do_test subquery-3.3.3 {
319 execsql {
320 INSERT INTO t1 VALUES(2, 4);
321 SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
322 }
323 } {2 two}
324 do_test subquery-3.3.4 {
325 execsql {
326 SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
327 }
328 } {1 one 2 two}
329 do_test subquery-3.3.5 {
330 execsql {
331 SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
332 }
333 } {1 1 2 1}
334
335 # The following tests check for aggregate subqueries in an aggregate
336 # query.
337 #
338 do_test subquery-3.4.1 {
339 execsql {
340 CREATE TABLE t34(x,y);
341 INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
342 SELECT a.x, avg(a.y)
343 FROM t34 AS a
344 GROUP BY a.x
345 HAVING NOT EXISTS( SELECT b.x, avg(b.y)
346 FROM t34 AS b
347 GROUP BY b.x
348 HAVING avg(a.y) > avg(b.y));
349 }
350 } {107 4.0}
351 do_test subquery-3.4.2 {
352 execsql {
353 SELECT a.x, avg(a.y) AS avg1
354 FROM t34 AS a
355 GROUP BY a.x
356 HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2
357 FROM t34 AS b
358 GROUP BY b.x
359 HAVING avg1 > avg2);
360 }
361 } {107 4.0}
362 do_test subquery-3.4.3 {
363 execsql {
364 SELECT
365 a.x,
366 avg(a.y),
367 NOT EXISTS ( SELECT b.x, avg(b.y)
368 FROM t34 AS b
369 GROUP BY b.x
370 HAVING avg(a.y) > avg(b.y)),
371 EXISTS ( SELECT c.x, avg(c.y)
372 FROM t34 AS c
373 GROUP BY c.x
374 HAVING avg(a.y) > avg(c.y))
375 FROM t34 AS a
376 GROUP BY a.x
377 ORDER BY a.x;
378 }
379 } {106 4.5 0 1 107 4.0 1 0}
380
381 do_test subquery-3.5.1 {
382 execsql {
383 CREATE TABLE t35a(x); INSERT INTO t35a VALUES(1),(2),(3);
384 CREATE TABLE t35b(y); INSERT INTO t35b VALUES(98), (99);
385 SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
386 }
387 } {98.5}
388 do_test subquery-3.5.2 {
389 execsql {
390 SELECT max((SELECT count(y) FROM t35b)) FROM t35a;
391 }
392 } {2}
393 do_test subquery-3.5.3 {
394 execsql {
395 SELECT max((SELECT count() FROM t35b)) FROM t35a;
396 }
397 } {2}
398 do_test subquery-3.5.4 {
399 catchsql {
400 SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
401 }
402 } {1 {misuse of aggregate: count()}}
403 do_test subquery-3.5.5 {
404 catchsql {
405 SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
406 }
407 } {1 {misuse of aggregate: count()}}
408 do_test subquery-3.5.6 {
409 catchsql {
410 SELECT max((SELECT a FROM (SELECT count(x) AS a FROM t35b))) FROM t35a;
411 }
412 } {1 {misuse of aggregate: count()}}
413 do_test subquery-3.5.7 {
414 execsql {
415 SELECT max((SELECT a FROM (SELECT count(y) AS a FROM t35b))) FROM t35a;
416 }
417 } {2}
418
419
420 #------------------------------------------------------------------
421 # These tests - subquery-4.* - use the TCL statement cache to try
422 # and expose bugs to do with re-using statements that have been
423 # passed to sqlite3_reset().
424 #
425 # One problem was that VDBE memory cells were not being initialized
426 # to NULL on the second and subsequent executions.
427 #
428 do_test subquery-4.1.1 {
429 execsql {
430 SELECT (SELECT a FROM t1);
431 }
432 } {1}
433 do_test subquery-4.2 {
434 execsql {
435 DELETE FROM t1;
436 SELECT (SELECT a FROM t1);
437 }
438 } {{}}
439 do_test subquery-4.2.1 {
440 execsql {
441 CREATE TABLE t3(a PRIMARY KEY);
442 INSERT INTO t3 VALUES(10);
443 }
444 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
445 } {}
446 do_test subquery-4.2.2 {
447 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
448 } {}
449
450 #------------------------------------------------------------------
451 # The subquery-5.* tests make sure string literals in double-quotes
452 # are handled efficiently. Double-quote literals are first checked
453 # to see if they match any column names. If there is not column name
454 # match then those literals are used a string constants. When a
455 # double-quoted string appears, we want to make sure that the search
456 # for a matching column name did not cause an otherwise static subquery
457 # to become a dynamic (correlated) subquery.
458 #
459 do_test subquery-5.1 {
460 proc callcntproc {n} {
461 incr ::callcnt
462 return $n
463 }
464 set callcnt 0
465 db function callcnt callcntproc
466 execsql {
467 CREATE TABLE t4(x,y);
468 INSERT INTO t4 VALUES('one',1);
469 INSERT INTO t4 VALUES('two',2);
470 INSERT INTO t4 VALUES('three',3);
471 INSERT INTO t4 VALUES('four',4);
472 CREATE TABLE t5(a,b);
473 INSERT INTO t5 VALUES(1,11);
474 INSERT INTO t5 VALUES(2,22);
475 INSERT INTO t5 VALUES(3,33);
476 INSERT INTO t5 VALUES(4,44);
477 SELECT b FROM t5 WHERE a IN
478 (SELECT callcnt(y)+0 FROM t4 WHERE x='two')
479 }
480 } {22}
481 do_test subquery-5.2 {
482 # This is the key test. The subquery should have only run once. If
483 # The double-quoted identifier "two" were causing the subquery to be
484 # processed as a correlated subquery, then it would have run 4 times.
485 set callcnt
486 } {1}
487
488
489 # Ticket #1380. Make sure correlated subqueries on an IN clause work
490 # correctly when the left-hand side of the IN operator is constant.
491 #
492 do_test subquery-6.1 {
493 set callcnt 0
494 execsql {
495 SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
496 }
497 } {one two three four}
498 do_test subquery-6.2 {
499 set callcnt
500 } {4}
501 do_test subquery-6.3 {
502 set callcnt 0
503 execsql {
504 SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
505 }
506 } {one two three four}
507 do_test subquery-6.4 {
508 set callcnt
509 } {1}
510
511 if 0 { ############# disable until we get #2652 fixed
512 # Ticket #2652. Allow aggregate functions of outer queries inside
513 # a non-aggregate subquery.
514 #
515 do_test subquery-7.1 {
516 execsql {
517 CREATE TABLE t7(c7);
518 INSERT INTO t7 VALUES(1);
519 INSERT INTO t7 VALUES(2);
520 INSERT INTO t7 VALUES(3);
521 CREATE TABLE t8(c8);
522 INSERT INTO t8 VALUES(100);
523 INSERT INTO t8 VALUES(200);
524 INSERT INTO t8 VALUES(300);
525 CREATE TABLE t9(c9);
526 INSERT INTO t9 VALUES(10000);
527 INSERT INTO t9 VALUES(20000);
528 INSERT INTO t9 VALUES(30000);
529
530 SELECT (SELECT c7+c8 FROM t7) FROM t8;
531 }
532 } {101 201 301}
533 do_test subquery-7.2 {
534 execsql {
535 SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
536 }
537 } {103 203 303}
538 do_test subquery-7.3 {
539 execsql {
540 SELECT (SELECT c7+max(c8) FROM t8) FROM t7
541 }
542 } {301}
543 do_test subquery-7.4 {
544 execsql {
545 SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
546 }
547 } {303}
548 do_test subquery-7.5 {
549 execsql {
550 SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
551 }
552 } {300}
553 do_test subquery-7.6 {
554 execsql {
555 SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
556 }
557 } {30101 30102 30103}
558 do_test subquery-7.7 {
559 execsql {
560 SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
561 }
562 } {30101 30102 30103}
563 do_test subquery-7.8 {
564 execsql {
565 SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
566 }
567 } {10103}
568 do_test subquery-7.9 {
569 execsql {
570 SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
571 }
572 } {10301 10302 10303}
573 do_test subquery-7.10 {
574 execsql {
575 SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
576 }
577 } {30101 30102 30103}
578 do_test subquery-7.11 {
579 execsql {
580 SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
581 }
582 } {30303}
583 } ;############# Disabled
584
585 # 2015-04-21.
586 # Verify that a memory leak in the table column type and collation analysis
587 # is plugged.
588 #
589 do_execsql_test subquery-8.1 {
590 CREATE TABLE t8(a TEXT, b INT);
591 SELECT (SELECT 0 FROM (SELECT * FROM t1)) AS x WHERE x;
592 SELECT (SELECT 0 FROM (SELECT * FROM (SELECT 0))) AS x WHERE x;
593 } {}
594
595 # 2022-01-12 https://sqlite.org/forum/forumpost/0ec80f12d02acb3f
596 #
597 reset_db
598 do_execsql_test subquery-9.1 {
599 CREATE TABLE t1(x);
600 INSERT INTO t1 VALUES(1),(1),(1);
601 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 100) FROM t1;
602 } {{} {} {}}
603 do_execsql_test subquery-9.2 {
604 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 0) FROM t1;
605 } {1 1 1}
606 do_execsql_test subquery-9.3 {
607 INSERT INTO t1 VALUES(2);
608 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 1) FROM t1;
609 } {2 2 2 2}
610 do_execsql_test subquery-9.4 {
611 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 2) FROM t1;
612 } {{} {} {} {}}
613
614 # 2023-09-15
615 # Query planner performance regression reported by private email
616 # on 2023-09-14, caused by VIEWSCAN optimization of check-in 609fbb94b8f01d67
617 # from 2022-09-01.
618 #
619 reset_db
620 do_execsql_test subquery-10.1 {
621 CREATE TABLE t1(aa TEXT, bb INT, cc TEXT);
622 CREATE INDEX x11 on t1(bb);
623 CREATE INDEX x12 on t1(aa);
624 CREATE TABLE t2(aa TEXT, xx INT);
625 ANALYZE sqlite_master;
626 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x11', '156789 28');
627 INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x12', '156789 1');
628 ANALYZE sqlite_master;
629 }
630 do_eqp_test subquery-10.2 {
631 WITH v1(aa,cc,bb) AS (SELECT aa, cc, bb FROM t1 WHERE bb=12345),
632 v2(aa,mx) AS (SELECT aa, max(xx) FROM t2 GROUP BY aa)
633 SELECT * FROM v1 JOIN v2 ON v1.aa=v2.aa;
634 } {
635 QUERY PLAN
636 |--CO-ROUTINE v2
637 | |--SCAN t2
638 | `--USE TEMP B-TREE FOR GROUP BY
639 |--SEARCH t1 USING INDEX x11 (bb=?)
640 `--SEARCH v2 USING AUTOMATIC COVERING INDEX (aa=?)
641 }
642 # ^^^^^^^^^^^^^
643 # Prior to the fix the incorrect (slow) plan caused by the
644 # VIEWSCAN optimization was:
645 #
646 # QUERY PLAN
647 # |--CO-ROUTINE v2
648 # | |--SCAN t2
649 # | `--USE TEMP B-TREE FOR GROUP BY
650 # |--SCAN v2
651 # `--SEARCH t1 USING INDEX x12 (aa=?)
652 #
653 #---------------------------------------------------------------------------
654 # Follow-up on 2025-04-14. Performance issue found while working
655 # on Fossil (Fossil check-in 2025-04-13T19:54).
656 #
657 do_execsql_test 10.3 {
658 CREATE TABLE blob(
659 rid INTEGER PRIMARY KEY,
660 size INT,
661 uuid TEXT
662 );
663 CREATE TABLE delta(
664 rid INTEGER PRIMARY KEY,
665 srcid INT
666 );
667 CREATE INDEX delta_i1 ON delta(srcid);
668 }
669 do_eqp_test subquery-10.4 {
670 WITH RECURSIVE deltasof(rid) AS (
671 SELECT rid FROM delta WHERE srcid=125020
672 UNION
673 SELECT delta.rid FROM deltasof, delta
674 WHERE delta.srcid=deltasof.rid)
675 SELECT deltasof.rid, blob.uuid FROM deltasof, blob
676 WHERE blob.rid=deltasof.rid;
677 } {
678 QUERY PLAN
679 |--CO-ROUTINE deltasof
680 | |--SETUP
681 | | `--SEARCH delta USING COVERING INDEX delta_i1 (srcid=?)
682 | `--RECURSIVE STEP
683 | |--SCAN deltasof
684 | `--SEARCH delta USING COVERING INDEX delta_i1 (srcid=?)
685 |--SCAN deltasof
686 `--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)
687 }
688 # ^^^^^^^^^^^^^^^^^^
689 # deltasof should be the outer loop and blob the inner loop
690 # Prior to the fix, SQLite was doing it the other way around.
691
692 #-----------------------------------------------------------------------------
693 # 2024-04-25 Column affinities for columns of compound subqueries
694 #
695 reset_db
696 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
697 do_execsql_test subquery-11.1 {
698 CREATE TABLE t1(ix INT, rx REAL, bx BLOB, tx TEXT, ax);
699 INSERT INTO t1 VALUES(1,1.0,x'31','x',NULL);
700 WITH c(a) AS (SELECT 'y' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
701 WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 'y') SELECT affinity(a) FROM c;
702 } {text text text text}
703 do_execsql_test subquery-11.2 {
704 WITH c(a) AS (SELECT 2 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
705 WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2) SELECT affinity(a) FROM c;
706 } {blob blob blob blob}
707 do_execsql_test subquery-11.3 {
708 WITH c(a) AS (SELECT 2.0 UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
709 WITH c(a) AS (SELECT tx FROM t1 UNION SELECT 2.0) SELECT affinity(a) FROM c;
710 } {blob blob blob blob}
711 do_execsql_test subquery-11.4 {
712 WITH c(a) AS (SELECT null UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
713 WITH c(a) AS (SELECT tx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
714 } {text text text text}
715 do_execsql_test subquery-11.5 {
716 WITH c(a) AS (SELECT x'32' UNION SELECT tx FROM t1) SELECT affinity(a) FROM c;
717 WITH c(a) AS (SELECT tx FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
718 } {text text text text}
719 do_execsql_test subquery-11.6 {
720 WITH c(a) AS (SELECT 3 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
721 WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3) SELECT affinity(a) FROM c;
722 } {integer integer integer integer}
723 do_execsql_test subquery-11.7 {
724 WITH c(a) AS (SELECT 3.0 UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
725 WITH c(a) AS (SELECT ix FROM t1 UNION SELECT 3.0) SELECT affinity(a) FROM c;
726 } {integer integer integer integer}
727 do_execsql_test subquery-11.8 {
728 WITH c(a) AS (SELECT '3' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
729 WITH c(a) AS (SELECT ix FROM t1 UNION SELECT '3') SELECT affinity(a) FROM c;
730 } {blob blob blob blob}
731 do_execsql_test subquery-11.10 {
732 WITH c(a) AS (SELECT x'32' UNION SELECT ix FROM t1) SELECT affinity(a) FROM c;
733 WITH c(a) AS (SELECT ix FROM t1 UNION SELECT x'32') SELECT affinity(a) FROM c;
734 } {integer integer integer integer}
735 do_execsql_test subquery-11.11 {
736 WITH c(a) AS (SELECT 4 UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
737 WITH c(a) AS (SELECT rx FROM t1 UNION SELECT 4) SELECT affinity(a) FROM c;
738 } {real real real real}
739 do_execsql_test subquery-11.12 {
740 WITH c(a) AS (SELECT '4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
741 WITH c(a) AS (SELECT rx FROM t1 UNION SELECT '4') SELECT affinity(a) FROM c;
742 } {blob blob blob blob}
743 do_execsql_test subquery-11.13 {
744 WITH c(a) AS (SELECT null UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
745 WITH c(a) AS (SELECT rx FROM t1 UNION SELECT null) SELECT affinity(a) FROM c;
746 } {real real real real}
747 do_execsql_test subquery-11.14 {
748 WITH c(a) AS (SELECT x'b4' UNION SELECT rx FROM t1) SELECT affinity(a) FROM c;
749 WITH c(a) AS (SELECT rx FROM t1 UNION SELECT x'b4') SELECT affinity(a) FROM c;
750 } {real real real real}
751
752 finish_test