| blob | 5765399d102163741f0c746c4e9a3da569b281ea |
1 # 2014 January 11
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 testing the WITH clause.
13 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix with1
19 ifcapable {!cte} {
20 finish_test
21 return
22 }
24 do_execsql_test 1.0 {
25 CREATE TABLE t1(x INTEGER, y INTEGER);
26 WITH x(a) AS ( SELECT * FROM t1) SELECT 10
27 } {10}
29 do_execsql_test 1.1 {
30 SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
31 } {10}
33 do_execsql_test 1.2 {
34 WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
35 } {}
37 do_execsql_test 1.3 {
38 WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
39 } {}
41 do_execsql_test 1.4 {
42 WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
43 } {}
45 #--------------------------------------------------------------------------
47 do_execsql_test 2.1 {
48 DROP TABLE IF EXISTS t1;
49 CREATE TABLE t1(x);
50 INSERT INTO t1 VALUES(1);
51 INSERT INTO t1 VALUES(2);
52 WITH tmp AS ( SELECT * FROM t1 ) SELECT x FROM tmp;
53 } {1 2}
55 do_execsql_test 2.2 {
56 WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
57 } {1 2}
59 do_execsql_test 2.3 {
60 SELECT * FROM (
61 WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
62 );
63 } {1 2}
65 do_execsql_test 2.4 {
66 WITH tmp1(a) AS ( SELECT * FROM t1 ),
67 tmp2(x) AS ( SELECT * FROM tmp1)
68 SELECT * FROM tmp2;
69 } {1 2}
71 do_execsql_test 2.5 {
72 WITH tmp2(x) AS ( SELECT * FROM tmp1),
73 tmp1(a) AS ( SELECT * FROM t1 )
74 SELECT * FROM tmp2;
75 } {1 2}
77 #-------------------------------------------------------------------------
78 do_catchsql_test 3.1 {
79 WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
80 tmp1(a) AS ( SELECT * FROM tmp2 )
81 SELECT * FROM tmp1;
82 } {1 {circular reference: tmp1}}
84 do_catchsql_test 3.2 {
85 CREATE TABLE t2(x INTEGER);
86 WITH tmp(a) AS (SELECT * FROM t1),
87 tmp(a) AS (SELECT * FROM t1)
88 SELECT * FROM tmp;
89 } {1 {duplicate WITH table name: tmp}}
91 do_execsql_test 3.3 {
92 CREATE TABLE t3(x);
93 CREATE TABLE t4(x);
95 INSERT INTO t3 VALUES('T3');
96 INSERT INTO t4 VALUES('T4');
98 WITH t3(a) AS (SELECT * FROM t4)
99 SELECT * FROM t3;
100 } {T4}
102 do_execsql_test 3.4 {
103 WITH tmp AS ( SELECT * FROM t3 ),
104 tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
105 SELECT * FROM tmp2;
106 } {T4}
108 do_execsql_test 3.5 {
109 WITH tmp AS ( SELECT * FROM t3 ),
110 tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
111 SELECT * FROM tmp2;
112 } {T3}
114 do_catchsql_test 3.6 {
115 WITH tmp AS ( SELECT * FROM t3 ),
116 SELECT * FROM tmp;
117 } {1 {near "SELECT": syntax error}}
119 #-------------------------------------------------------------------------
120 do_execsql_test 4.1 {
121 DROP TABLE IF EXISTS t1;
122 CREATE TABLE t1(x);
123 INSERT INTO t1 VALUES(1);
124 INSERT INTO t1 VALUES(2);
125 INSERT INTO t1 VALUES(3);
126 INSERT INTO t1 VALUES(4);
128 WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
129 DELETE FROM t1 WHERE x IN dset;
130 SELECT * FROM t1;
131 } {1 3}
133 do_execsql_test 4.2 {
134 WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
135 INSERT INTO t1 SELECT * FROM iset;
136 SELECT * FROM t1;
137 } {1 3 2 4}
139 do_execsql_test 4.3 {
140 WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
141 UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
142 SELECT * FROM t1;
143 } {1 3 8 9}
145 #-------------------------------------------------------------------------
146 #
147 do_execsql_test 5.1 {
148 WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
149 SELECT x FROM i LIMIT 10;
150 } {1 2 3 4 5 6 7 8 9 10}
152 do_catchsql_test 5.2 {
153 WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
154 SELECT x FROM i LIMIT 10;
155 } {0 {1 2 3 4 5 6 7 8 9 10}}
157 do_execsql_test 5.2.1 {
158 CREATE TABLE edge(xfrom, xto, seq, PRIMARY KEY(xfrom, xto)) WITHOUT ROWID;
159 INSERT INTO edge VALUES(0, 1, 10);
160 INSERT INTO edge VALUES(1, 2, 20);
161 INSERT INTO edge VALUES(0, 3, 30);
162 INSERT INTO edge VALUES(2, 4, 40);
163 INSERT INTO edge VALUES(3, 4, 40);
164 INSERT INTO edge VALUES(2, 5, 50);
165 INSERT INTO edge VALUES(3, 6, 60);
166 INSERT INTO edge VALUES(5, 7, 70);
167 INSERT INTO edge VALUES(3, 7, 70);
168 INSERT INTO edge VALUES(4, 8, 80);
169 INSERT INTO edge VALUES(7, 8, 80);
170 INSERT INTO edge VALUES(8, 9, 90);
172 WITH RECURSIVE
173 ancest(id, mtime) AS
174 (VALUES(0, 0)
175 UNION
176 SELECT edge.xto, edge.seq FROM edge, ancest
177 WHERE edge.xfrom=ancest.id
178 ORDER BY 2
179 )
180 SELECT * FROM ancest;
181 } {0 0 1 10 2 20 3 30 4 40 5 50 6 60 7 70 8 80 9 90}
182 do_execsql_test 5.2.2 {
183 WITH RECURSIVE
184 ancest(id, mtime) AS
185 (VALUES(0, 0)
186 UNION ALL
187 SELECT edge.xto, edge.seq FROM edge, ancest
188 WHERE edge.xfrom=ancest.id
189 ORDER BY 2
190 )
191 SELECT * FROM ancest;
192 } {0 0 1 10 2 20 3 30 4 40 4 40 5 50 6 60 7 70 7 70 8 80 8 80 8 80 8 80 9 90 9 90 9 90 9 90}
193 do_execsql_test 5.2.3 {
194 WITH RECURSIVE
195 ancest(id, mtime) AS
196 (VALUES(0, 0)
197 UNION ALL
198 SELECT edge.xto, edge.seq FROM edge, ancest
199 WHERE edge.xfrom=ancest.id
200 ORDER BY 2 LIMIT 4 OFFSET 2
201 )
202 SELECT * FROM ancest;
203 } {2 20 3 30 4 40 4 40}
205 do_catchsql_test 5.3 {
206 WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 5)
207 SELECT x FROM i;
208 } {0 {1 2 3 4 5}}
210 do_execsql_test 5.4 {
211 WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
212 SELECT x FROM i LIMIT 20;
213 } {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
215 do_execsql_test 5.5 {
216 WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
217 SELECT x FROM i LIMIT 20;
218 } {1 2 3 4 5 6 7 8 9 0}
220 do_catchsql_test 5.6.1 {
221 WITH i(x, y) AS ( VALUES(1) )
222 SELECT * FROM i;
223 } {1 {table i has 1 values for 2 columns}}
225 do_catchsql_test 5.6.2 {
226 WITH i(x) AS ( VALUES(1,2) )
227 SELECT * FROM i;
228 } {1 {table i has 2 values for 1 columns}}
230 do_catchsql_test 5.6.3 {
231 CREATE TABLE t5(a, b);
232 WITH i(x) AS ( SELECT * FROM t5 )
233 SELECT * FROM i;
234 } {1 {table i has 2 values for 1 columns}}
236 do_catchsql_test 5.6.4 {
237 WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
238 SELECT * FROM i;
239 } {1 {table i has 2 values for 1 columns}}
241 do_catchsql_test 5.6.5 {
242 WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
243 SELECT * FROM i;
244 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
246 do_catchsql_test 5.6.6 {
247 WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
248 SELECT * FROM i;
249 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
251 do_catchsql_test 5.6.7 {
252 WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
253 SELECT * FROM i;
254 } {1 {table i has 2 values for 1 columns}}
256 #-------------------------------------------------------------------------
257 #
258 do_execsql_test 6.1 {
259 CREATE TABLE f(
260 id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
261 );
263 INSERT INTO f VALUES(0, NULL, '');
264 INSERT INTO f VALUES(1, 0, 'bin');
265 INSERT INTO f VALUES(2, 1, 'true');
266 INSERT INTO f VALUES(3, 1, 'false');
267 INSERT INTO f VALUES(4, 1, 'ls');
268 INSERT INTO f VALUES(5, 1, 'grep');
269 INSERT INTO f VALUES(6, 0, 'etc');
270 INSERT INTO f VALUES(7, 6, 'rc.d');
271 INSERT INTO f VALUES(8, 7, 'rc.apache');
272 INSERT INTO f VALUES(9, 7, 'rc.samba');
273 INSERT INTO f VALUES(10, 0, 'home');
274 INSERT INTO f VALUES(11, 10, 'dan');
275 INSERT INTO f VALUES(12, 11, 'public_html');
276 INSERT INTO f VALUES(13, 12, 'index.html');
277 INSERT INTO f VALUES(14, 13, 'logo.gif');
278 }
280 do_execsql_test 6.2 {
281 WITH flat(fid, fpath) AS (
282 SELECT id, '' FROM f WHERE parentid IS NULL
283 UNION ALL
284 SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
285 )
286 SELECT fpath FROM flat WHERE fpath!='' ORDER BY 1;
287 } {
288 /bin
289 /bin/false /bin/grep /bin/ls /bin/true
290 /etc
291 /etc/rc.d
292 /etc/rc.d/rc.apache /etc/rc.d/rc.samba
293 /home
294 /home/dan
295 /home/dan/public_html
296 /home/dan/public_html/index.html
297 /home/dan/public_html/index.html/logo.gif
298 }
300 do_execsql_test 6.3 {
301 WITH flat(fid, fpath) AS (
302 SELECT id, '' FROM f WHERE parentid IS NULL
303 UNION ALL
304 SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
305 )
306 SELECT count(*) FROM flat;
307 } {15}
309 do_execsql_test 6.4 {
310 WITH x(i) AS (
311 SELECT 1
312 UNION ALL
313 SELECT i+1 FROM x WHERE i<10
314 )
315 SELECT count(*) FROM x
316 } {10}
319 #-------------------------------------------------------------------------
321 do_execsql_test 7.1 {
322 CREATE TABLE tree(i, p);
323 INSERT INTO tree VALUES(1, NULL);
324 INSERT INTO tree VALUES(2, 1);
325 INSERT INTO tree VALUES(3, 1);
326 INSERT INTO tree VALUES(4, 2);
327 INSERT INTO tree VALUES(5, 4);
328 }
330 do_execsql_test 7.2 {
331 WITH t(id, path) AS (
332 SELECT i, '' FROM tree WHERE p IS NULL
333 UNION ALL
334 SELECT i, path || '/' || i FROM tree, t WHERE p = id
335 )
336 SELECT path FROM t;
337 } {{} /2 /3 /2/4 /2/4/5}
339 do_execsql_test 7.3 {
340 WITH t(id) AS (
341 VALUES(2)
342 UNION ALL
343 SELECT i FROM tree, t WHERE p = id
344 )
345 SELECT id FROM t;
346 } {2 4 5}
348 do_catchsql_test 7.4 {
349 WITH t(id) AS (
350 VALUES(2)
351 UNION ALL
352 SELECT i FROM tree WHERE p IN (SELECT id FROM t)
353 )
354 SELECT id FROM t;
355 } {1 {circular reference: t}}
357 do_catchsql_test 7.5 {
358 WITH t(id) AS (
359 VALUES(2)
360 UNION ALL
361 SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
362 )
363 SELECT id FROM t;
364 } {1 {multiple recursive references: t}}
366 do_catchsql_test 7.6 {
367 WITH t(id) AS (
368 SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
369 UNION ALL
370 SELECT i FROM tree, t WHERE p = id
371 )
372 SELECT id FROM t;
373 } {1 {circular reference: t}}
375 # Compute the mandelbrot set using a recursive query
376 #
377 do_execsql_test 8.1-mandelbrot {
378 WITH RECURSIVE
379 xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
380 yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
381 m(iter, cx, cy, x, y) AS (
382 SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
383 UNION ALL
384 SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
385 WHERE (x*x + y*y) < 4.0 AND iter<28
386 ),
387 m2(iter, cx, cy) AS (
388 SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
389 ),
390 a(t) AS (
391 SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
392 FROM m2 GROUP BY cy
393 )
394 SELECT group_concat(rtrim(t),x'0a') FROM a;
395 } {{ ....#
396 ..#*..
397 ..+####+.
398 .......+####.... +
399 ..##+*##########+.++++
400 .+.##################+.
401 .............+###################+.+
402 ..++..#.....*#####################+.
403 ...+#######++#######################.
404 ....+*################################.
405 #############################################...
406 ....+*################################.
407 ...+#######++#######################.
408 ..++..#.....*#####################+.
409 .............+###################+.+
410 .+.##################+.
411 ..##+*##########+.++++
412 .......+####.... +
413 ..+####+.
414 ..#*..
415 ....#
416 +.}}
418 # Solve a sudoku puzzle using a recursive query
419 #
420 do_execsql_test 8.2-soduko {
421 WITH RECURSIVE
422 input(sud) AS (
423 VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
424 ),
426 /* A table filled with digits 1..9, inclusive. */
427 digits(z, lp) AS (
428 VALUES('1', 1)
429 UNION ALL SELECT
430 CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
431 ),
433 /* The tricky bit. */
434 x(s, ind) AS (
435 SELECT sud, instr(sud, '.') FROM input
436 UNION ALL
437 SELECT
438 substr(s, 1, ind-1) || z || substr(s, ind+1),
439 instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
440 FROM x, digits AS z
441 WHERE ind>0
442 AND NOT EXISTS (
443 SELECT 1
444 FROM digits AS lp
445 WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
446 OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
447 OR z.z = substr(s, (((ind-1)/3) % 3) * 3
448 + ((ind-1)/27) * 27 + lp
449 + ((lp-1) / 3) * 6, 1)
450 )
451 )
452 SELECT s FROM x WHERE ind=0;
453 } {534678912672195348198342567859761423426853791713924856961537284287419635345286179}
455 #--------------------------------------------------------------------------
456 # Some tests that use LIMIT and OFFSET in the definition of recursive CTEs.
457 #
458 set I [list 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20]
459 proc limit_test {tn iLimit iOffset} {
460 if {$iOffset < 0} { set iOffset 0 }
461 if {$iLimit < 0 } {
462 set result [lrange $::I $iOffset end]
463 } else {
464 set result [lrange $::I $iOffset [expr $iLimit+$iOffset-1]]
465 }
466 uplevel [list do_execsql_test $tn [subst -nocommands {
467 WITH ii(a) AS (
468 VALUES(1)
469 UNION ALL
470 SELECT a+1 FROM ii WHERE a<20
471 LIMIT $iLimit OFFSET $iOffset
472 )
473 SELECT * FROM ii
474 }] $result]
475 }
477 limit_test 9.1 20 0
478 limit_test 9.2 0 0
479 limit_test 9.3 19 1
480 limit_test 9.4 20 -1
481 limit_test 9.5 5 5
482 limit_test 9.6 0 -1
483 limit_test 9.7 40 -1
484 limit_test 9.8 -1 -1
485 limit_test 9.9 -1 -1
487 #--------------------------------------------------------------------------
488 # Test the ORDER BY clause on recursive tables.
489 #
491 do_execsql_test 10.1 {
492 DROP TABLE IF EXISTS tree;
493 CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
494 }
496 proc insert_into_tree {L} {
497 db eval { DELETE FROM tree }
498 foreach key $L {
499 unset -nocomplain parentid
500 foreach seg [split $key /] {
501 if {$seg==""} continue
502 set id [db one {
503 SELECT id FROM tree WHERE parentid IS $parentid AND payload=$seg
504 }]
505 if {$id==""} {
506 db eval { INSERT INTO tree VALUES(NULL, $parentid, $seg) }
507 set parentid [db last_insert_rowid]
508 } else {
509 set parentid $id
510 }
511 }
512 }
513 }
515 insert_into_tree {
516 /a/a/a
517 /a/b/c
518 /a/b/c/d
519 /a/b/d
520 }
521 do_execsql_test 10.2 {
522 WITH flat(fid, p) AS (
523 SELECT id, '/' || payload FROM tree WHERE parentid IS NULL
524 UNION ALL
525 SELECT id, p || '/' || payload FROM flat, tree WHERE parentid=fid
526 )
527 SELECT p FROM flat ORDER BY p;
528 } {
529 /a /a/a /a/a/a
530 /a/b /a/b/c /a/b/c/d
531 /a/b/d
532 }
534 # Scan the tree-structure currently stored in table tree. Return a list
535 # of nodes visited.
536 #
537 proc scan_tree {bDepthFirst bReverse} {
539 set order "ORDER BY "
540 if {$bDepthFirst==0} { append order "2 ASC," }
541 if {$bReverse==0} {
542 append order " 3 ASC"
543 } else {
544 append order " 3 DESC"
545 }
547 db eval "
548 WITH flat(fid, depth, p) AS (
549 SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
550 UNION ALL
551 SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
552 $order
553 )
554 SELECT p FROM flat;
555 "
556 }
558 insert_into_tree {
559 /a/b
560 /a/b/c
561 /a/d
562 /a/d/e
563 /a/d/f
564 /g/h
565 }
567 # Breadth first, siblings in ascending order.
568 #
569 do_test 10.3 {
570 scan_tree 0 0
571 } [list {*}{
572 /a /g
573 /a/b /a/d /g/h
574 /a/b/c /a/d/e /a/d/f
575 }]
577 # Depth first, siblings in ascending order.
578 #
579 do_test 10.4 {
580 scan_tree 1 0
581 } [list {*}{
582 /a /a/b /a/b/c
583 /a/d /a/d/e
584 /a/d/f
585 /g /g/h
586 }]
588 # Breadth first, siblings in descending order.
589 #
590 do_test 10.5 {
591 scan_tree 0 1
592 } [list {*}{
593 /g /a
594 /g/h /a/d /a/b
595 /a/d/f /a/d/e /a/b/c
596 }]
598 # Depth first, siblings in ascending order.
599 #
600 do_test 10.6 {
601 scan_tree 1 1
602 } [list {*}{
603 /g /g/h
604 /a /a/d /a/d/f
605 /a/d/e
606 /a/b /a/b/c
607 }]
610 # Test name resolution in ORDER BY clauses.
611 #
612 do_catchsql_test 10.7.1 {
613 WITH t(a) AS (
614 SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
615 )
616 SELECT * FROM t
617 } {1 {1st ORDER BY term does not match any column in the result set}}
618 do_execsql_test 10.7.2 {
619 WITH t(a) AS (
620 SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
621 )
622 SELECT * FROM t
623 } {1 2 3 4 5}
624 do_execsql_test 10.7.3 {
625 WITH t(a) AS (
626 SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
627 )
628 SELECT * FROM t
629 } {1 2 3 4 5}
631 # Test COLLATE clauses attached to ORDER BY.
632 #
633 insert_into_tree {
634 /a/b
635 /a/C
636 /a/d
637 /B/e
638 /B/F
639 /B/g
640 /c/h
641 /c/I
642 /c/j
643 }
645 do_execsql_test 10.8.1 {
646 WITH flat(fid, depth, p) AS (
647 SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
648 UNION ALL
649 SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
650 ORDER BY 2, 3 COLLATE nocase
651 )
652 SELECT p FROM flat;
653 } {
654 /a /B /c
655 /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
656 }
657 do_execsql_test 10.8.2 {
658 WITH flat(fid, depth, p) AS (
659 SELECT id, 1, ('/' || payload) COLLATE nocase
660 FROM tree WHERE parentid IS NULL
661 UNION ALL
662 SELECT id, depth+1, (p||'/'||payload)
663 FROM flat, tree WHERE parentid=fid
664 ORDER BY 2, 3
665 )
666 SELECT p FROM flat;
667 } {
668 /a /B /c
669 /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
670 }
672 do_execsql_test 10.8.3 {
673 WITH flat(fid, depth, p) AS (
674 SELECT id, 1, ('/' || payload)
675 FROM tree WHERE parentid IS NULL
676 UNION ALL
677 SELECT id, depth+1, (p||'/'||payload) COLLATE nocase
678 FROM flat, tree WHERE parentid=fid
679 ORDER BY 2, 3
680 )
681 SELECT p FROM flat;
682 } {
683 /a /B /c
684 /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
685 }
687 do_execsql_test 10.8.4.1 {
688 CREATE TABLE tst(a,b);
689 INSERT INTO tst VALUES('a', 'A');
690 INSERT INTO tst VALUES('b', 'B');
691 INSERT INTO tst VALUES('c', 'C');
692 SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
693 } {a A b B c C}
694 do_execsql_test 10.8.4.2 {
695 SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
696 } {A B C a b c}
697 do_execsql_test 10.8.4.3 {
698 SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
699 } {a A b B c C}
701 # Test cases to illustrate on the ORDER BY clause on a recursive query can be
702 # used to control depth-first versus breath-first search in a tree.
703 #
704 do_execsql_test 11.1 {
705 CREATE TABLE org(
706 name TEXT PRIMARY KEY,
707 boss TEXT REFERENCES org
708 ) WITHOUT ROWID;
709 INSERT INTO org VALUES('Alice',NULL);
710 INSERT INTO org VALUES('Bob','Alice');
711 INSERT INTO org VALUES('Cindy','Alice');
712 INSERT INTO org VALUES('Dave','Bob');
713 INSERT INTO org VALUES('Emma','Bob');
714 INSERT INTO org VALUES('Fred','Cindy');
715 INSERT INTO org VALUES('Gail','Cindy');
716 INSERT INTO org VALUES('Harry','Dave');
717 INSERT INTO org VALUES('Ingrid','Dave');
718 INSERT INTO org VALUES('Jim','Emma');
719 INSERT INTO org VALUES('Kate','Emma');
720 INSERT INTO org VALUES('Lanny','Fred');
721 INSERT INTO org VALUES('Mary','Fred');
722 INSERT INTO org VALUES('Noland','Gail');
723 INSERT INTO org VALUES('Olivia','Gail');
724 -- The above are all under Alice. Add a few more records for people
725 -- not in Alice's group, just to prove that they won't be selected.
726 INSERT INTO org VALUES('Xaviar',NULL);
727 INSERT INTO org VALUES('Xia','Xaviar');
728 INSERT INTO org VALUES('Xerxes','Xaviar');
729 INSERT INTO org VALUES('Xena','Xia');
730 -- Find all members of Alice's group, breath-first order
731 WITH RECURSIVE
732 under_alice(name,level) AS (
733 VALUES('Alice','0')
734 UNION ALL
735 SELECT org.name, under_alice.level+1
736 FROM org, under_alice
737 WHERE org.boss=under_alice.name
738 ORDER BY 2
739 )
740 SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
741 FROM under_alice;
742 } {{Alice
743 ...Bob
744 ...Cindy
745 ......Dave
746 ......Emma
747 ......Fred
748 ......Gail
749 .........Harry
750 .........Ingrid
751 .........Jim
752 .........Kate
753 .........Lanny
754 .........Mary
755 .........Noland
756 .........Olivia}}
758 # The previous query used "ORDER BY level" to yield a breath-first search.
759 # Change that to "ORDER BY level DESC" for a depth-first search.
760 #
761 do_execsql_test 11.2 {
762 WITH RECURSIVE
763 under_alice(name,level) AS (
764 VALUES('Alice','0')
765 UNION ALL
766 SELECT org.name, under_alice.level+1
767 FROM org, under_alice
768 WHERE org.boss=under_alice.name
769 ORDER BY 2 DESC
770 )
771 SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
772 FROM under_alice;
773 } {{Alice
774 ...Bob
775 ......Dave
776 .........Harry
777 .........Ingrid
778 ......Emma
779 .........Jim
780 .........Kate
781 ...Cindy
782 ......Fred
783 .........Lanny
784 .........Mary
785 ......Gail
786 .........Noland
787 .........Olivia}}
789 # Without an ORDER BY clause, the recursive query should use a FIFO,
790 # resulting in a breath-first search.
791 #
792 do_execsql_test 11.3 {
793 WITH RECURSIVE
794 under_alice(name,level) AS (
795 VALUES('Alice','0')
796 UNION ALL
797 SELECT org.name, under_alice.level+1
798 FROM org, under_alice
799 WHERE org.boss=under_alice.name
800 )
801 SELECT group_concat(substr('...............',1,level*3) || name,x'0a')
802 FROM under_alice;
803 } {{Alice
804 ...Bob
805 ...Cindy
806 ......Dave
807 ......Emma
808 ......Fred
809 ......Gail
810 .........Harry
811 .........Ingrid
812 .........Jim
813 .........Kate
814 .........Lanny
815 .........Mary
816 .........Noland
817 .........Olivia}}
819 #--------------------------------------------------------------------------
820 # Ticket [31a19d11b97088296ac104aaff113a9790394927] (2014-02-09)
821 # Name resolution issue with compound SELECTs and Common Table Expressions
822 #
823 do_execsql_test 12.1 {
824 WITH RECURSIVE
825 t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<20),
826 t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<20)
827 SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1;
828 } {2 4 8 10 14 16 20}
830 # 2015-03-21
831 # Column wildcards on the LHS of a recursive table expression
832 #
833 do_catchsql_test 13.1 {
834 WITH RECURSIVE c(i) AS (SELECT * UNION ALL SELECT i+1 FROM c WHERE i<10)
835 SELECT i FROM c;
836 } {1 {no tables specified}}
837 do_catchsql_test 13.2 {
838 WITH RECURSIVE c(i) AS (SELECT 5,* UNION ALL SELECT i+1 FROM c WHERE i<10)
839 SELECT i FROM c;
840 } {1 {no tables specified}}
841 do_catchsql_test 13.3 {
842 WITH RECURSIVE c(i,j) AS (SELECT 5,* UNION ALL SELECT i+1,11 FROM c WHERE i<10)
843 SELECT i FROM c;
844 } {1 {table c has 1 values for 2 columns}}
846 # 2015-04-12
847 #
848 do_execsql_test 14.1 {
849 WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary;
850 } {}
852 # 2015-05-27: Do not allow rowid usage within a CTE
853 #
854 do_catchsql_test 15.1 {
855 WITH RECURSIVE
856 d(x) AS (VALUES(1) UNION ALL SELECT rowid+1 FROM d WHERE rowid<10)
857 SELECT x FROM d;
858 } {1 {no such column: rowid}}
860 # 2015-07-05: Do not allow aggregate recursive queries
861 #
862 do_catchsql_test 16.1 {
863 WITH RECURSIVE
864 i(x) AS (VALUES(1) UNION SELECT count(*) FROM i)
865 SELECT * FROM i;
866 } {1 {recursive aggregate queries not supported}}
868 # Or window-function recursive queries. Ticket e8275b41.
869 #
870 ifcapable windowfunc {
871 do_catchsql_test 16.2 {
872 WITH RECURSIVE
873 i(x) AS (VALUES(1) UNION SELECT count(*) OVER () FROM i)
874 SELECT * FROM i;
875 } {1 {cannot use window functions in recursive queries}}
876 do_catchsql_test 16.3 {
877 WITH RECURSIVE
878 t(id, parent) AS (VALUES(1,2)),
879 q(id, parent, rn) AS (
880 VALUES(1,2,3)
881 UNION ALL
882 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
883 FROM q JOIN t ON t.parent = q.id
884 )
885 SELECT * FROM q;
886 } {1 {cannot use window functions in recursive queries}}
887 }
889 #-------------------------------------------------------------------------
890 do_execsql_test 17.1 {
891 WITH x(a) AS (
892 WITH y(b) AS (SELECT 10)
893 SELECT 9 UNION ALL SELECT * FROM y
894 )
895 SELECT * FROM x
896 } {9 10}
898 do_execsql_test 17.2 {
899 WITH x AS (
900 WITH y(b) AS (SELECT 10)
901 SELECT * FROM y UNION ALL SELECT * FROM y
902 )
903 SELECT * FROM x
904 } {10 10}
906 do_test 17.2 {
907 db eval {
908 WITH x AS (
909 WITH y(b) AS (SELECT 10)
910 SELECT * FROM y UNION ALL SELECT * FROM y
911 )
912 SELECT * FROM x
913 } A {
914 # no op
915 }
916 set A(*)
917 } {b}
919 do_catchsql_test 17.3 {
920 WITH i AS (
921 WITH j AS (SELECT 5)
922 SELECT 5 FROM i UNION SELECT 8 FROM i
923 )
924 SELECT * FROM i;
925 } {1 {circular reference: i}}
927 do_catchsql_test 17.4 {
928 WITH i AS (
929 WITH j AS (SELECT 5)
930 SELECT 5 FROM t1 UNION SELECT 8 FROM t11
931 )
932 SELECT * FROM i;
933 } {1 {no such table: t11}}
935 do_execsql_test 17.5 {
936 WITH
937 x1 AS (SELECT 10),
938 x2 AS (SELECT * FROM x1),
939 x3 AS (
940 WITH x1 AS (SELECT 11)
941 SELECT * FROM x2 UNION ALL SELECT * FROM x2
942 )
943 SELECT * FROM x3;
944 } {10 10}
946 do_execsql_test 17.6 {
947 WITH
948 x1 AS (SELECT 10),
949 x2 AS (SELECT * FROM x1),
950 x3 AS (
951 WITH x1 AS (SELECT 11)
952 SELECT * FROM x2 UNION ALL SELECT * FROM x1
953 )
954 SELECT * FROM x3;
955 } {10 11}
957 do_execsql_test 17.7 {
958 WITH
959 x1 AS (SELECT 10),
960 x2 AS (SELECT * FROM x1),
961 x3 AS (
962 WITH
963 x1 AS ( SELECT 11 ),
964 x4 AS ( SELECT * FROM x2 )
965 SELECT * FROM x4 UNION ALL SELECT * FROM x1
966 )
967 SELECT * FROM x3;
968 } {10 11}
970 do_execsql_test 17.8 {
971 WITH
972 x1 AS (SELECT 10),
973 x2 AS (SELECT * FROM x1),
974 x3 AS (
975 WITH
976 x1 AS ( SELECT 11 ),
977 x4 AS ( SELECT * FROM x2 )
978 SELECT * FROM x4 UNION ALL SELECT * FROM x1
979 )
980 SELECT * FROM x3;
981 } {10 11}
983 do_execsql_test 17.9 {
984 WITH
985 x1 AS (SELECT 10),
986 x2 AS (SELECT 11),
987 x3 AS (
988 SELECT * FROM x1 UNION ALL SELECT * FROM x2
989 ),
990 x4 AS (
991 WITH
992 x1 AS (SELECT 12),
993 x2 AS (SELECT 13)
994 SELECT * FROM x3
995 )
996 SELECT * FROM x4;
997 } {10 11}
999 # Added to test a fix to a faulty assert() discovered by libFuzzer.
1000 #
1001 do_execsql_test 18.1 {
1002 WITH xyz(x) AS (VALUES(NULL) UNION SELECT round(1<x) FROM xyz ORDER BY 1)
1003 SELECT quote(x) FROM xyz;
1004 } {NULL}
1005 do_execsql_test 18.2 {
1006 WITH xyz(x) AS (
1007 SELECT printf('%d', 5) * NULL
1008 UNION SELECT round(1<1+x)
1009 FROM xyz ORDER BY 1
1010 )
1011 SELECT 1 FROM xyz;
1012 } 1
1014 # EXPLAIN QUERY PLAN on a self-join of a CTE
1015 #
1016 do_execsql_test 19.1a {
1017 DROP TABLE IF EXISTS t1;
1018 CREATE TABLE t1(x);
1019 }
1020 do_eqp_test 19.1b {
1021 WITH
1022 x1(a) AS (values(100))
1023 INSERT INTO t1(x)
1024 SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
1025 SELECT * FROM t1;
1026 } {
1027 QUERY PLAN
1028 |--MATERIALIZE x1
1029 | `--SCAN CONSTANT ROW
1030 |--SCAN x1
1031 `--SCAN x1
1032 }
1034 # 2017-10-28.
1035 # See check-in https://sqlite.org/src/info/0926df095faf72c2
1036 # Tried to optimize co-routine processing by changing a Copy opcode
1037 # into SCopy. But OSSFuzz found two (similar) cases where that optimization
1038 # does not work.
1039 #
1040 do_execsql_test 20.1 {
1041 WITH c(i)AS(VALUES(9)UNION SELECT~i FROM c)SELECT max(5)>i fROM c;
1042 } {0}
1043 do_execsql_test 20.2 {
1044 WITH c(i)AS(VALUES(5)UNIoN SELECT 0)SELECT min(1)-i fROM c;
1045 } {1}
1047 # 2018-12-26
1048 # Two different CTE tables with the same name appear in within a single FROM
1049 # clause due to the query-flattener optimization. make sure this does not cause
1050 # problems. This problem was discovered by Matt Denton.
1051 #
1052 do_execsql_test 21.1 {
1053 WITH RECURSIVE t21(a,b) AS (
1054 WITH t21(x) AS (VALUES(1))
1055 SELECT x, x FROM t21 ORDER BY 1
1056 )
1057 SELECT * FROM t21 AS tA, t21 AS tB
1058 } {1 1 1 1}
1059 do_execsql_test 21.1b {
1060 /* This variant from chromium bug 922312 on 2019-01-16 */
1061 WITH RECURSIVE t21(a,b) AS (
1062 WITH t21(x) AS (VALUES(1))
1063 SELECT x, x FROM t21 ORDER BY 1 LIMIT 5
1064 )
1065 SELECT * FROM t21 AS tA, t21 AS tB
1066 } {1 1 1 1}
1067 do_execsql_test 21.2 {
1068 SELECT printf('',
1069 EXISTS (WITH RECURSIVE Table0 AS (WITH Table0 AS (SELECT DISTINCT 1)
1070 SELECT *, * FROM Table0 ORDER BY 1 DESC)
1071 SELECT * FROM Table0 NATURAL JOIN Table0));
1072 } {{}}
1074 # 2019-01-17
1075 # Make sure crazy nexted CTE joins terminate with an error quickly.
1076 #
1077 do_catchsql_test 22.1 {
1078 WITH RECURSIVE c AS (
1079 WITH RECURSIVE c AS (
1080 WITH RECURSIVE c AS (
1081 WITH RECURSIVE c AS (
1082 WITH c AS (VALUES(0))
1083 SELECT 1 FROM c LEFT JOIN c ON ltrim(1)
1084 )
1085 SELECT 1 FROM c,c,c,c,c,c,c,c,c
1086 )
1087 SELECT 2 FROM c,c,c,c,c,c,c,c,c
1088 )
1089 SELECT 3 FROM c,c,c,c,c,c,c,c,c
1090 )
1091 SELECT 4 FROM c,c,c,c,c,c,c,c,c;
1092 } {1 {too many FROM clause terms, max: 200}}
1094 # 2019-05-22
1095 # ticket https://www.sqlite.org/src/tktview/ce823231949d3abf42453c8f20
1096 #
1097 sqlite3 db :memory:
1098 do_execsql_test 23.1 {
1099 CREATE TABLE t1(id INTEGER NULL PRIMARY KEY, name Text);
1100 INSERT INTO t1 VALUES (1, 'john');
1101 INSERT INTO t1 VALUES (2, 'james');
1102 INSERT INTO t1 VALUES (3, 'jingle');
1103 INSERT INTO t1 VALUES (4, 'himer');
1104 INSERT INTO t1 VALUES (5, 'smith');
1105 CREATE VIEW v2 AS
1106 WITH t4(Name) AS (VALUES ('A'), ('B'))
1107 SELECT Name Name FROM t4;
1108 CREATE VIEW v3 AS
1109 WITH t4(Att, Val, Act) AS (VALUES
1110 ('C', 'D', 'E'),
1111 ('F', 'G', 'H')
1112 )
1113 SELECT D.Id Id, P.Name Protocol, T.Att Att, T.Val Val, T.Act Act
1114 FROM t1 D
1115 CROSS JOIN v2 P
1116 CROSS JOIN t4 T;
1117 SELECT * FROM v3;
1118 } {1 A C D E 1 A F G H 1 B C D E 1 B F G H 2 A C D E 2 A F G H 2 B C D E 2 B F G H 3 A C D E 3 A F G H 3 B C D E 3 B F G H 4 A C D E 4 A F G H 4 B C D E 4 B F G H 5 A C D E 5 A F G H 5 B C D E 5 B F G H}
1120 #-------------------------------------------------------------------------
1121 reset_db
1122 do_execsql_test 24.1 {
1123 CREATE TABLE t1(a, b, c);
1124 CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
1125 }
1126 do_test 24.1 {
1127 set program [db eval {EXPLAIN SELECT 1 FROM v1,v1,v1}]
1128 expr [lsearch $program OpenDup]>0
1129 } {1}
1130 do_execsql_test 24.2 {
1131 ATTACH "" AS aux;
1132 CREATE VIEW aux.v3 AS VALUES(1);
1133 CREATE VIEW main.v3 AS VALUES(3);
1135 CREATE VIEW aux.v2 AS SELECT * FROM v3;
1136 CREATE VIEW main.v2 AS SELECT * FROM v3;
1138 SELECT * FROM main.v2 AS a, aux.v2 AS b, aux.v2 AS c, main.v2 AS d;
1139 } {
1140 3 1 1 3
1141 }
1143 # 2020-01-02 chromium ticket 1033461
1144 # Do not allow the generated name of a CTE be "true" or "false" as
1145 # such a label might be later confused for the boolean literals of
1146 # the same name, causing inconsistencies in the abstract syntax
1147 # tree. This problem first arose in version 3.23.0 when SQLite
1148 # began recognizing "true" and "false" as boolean literals, but also
1149 # had to continue to recognize "true" and "false" as identifiers for
1150 # backwards compatibility.
1151 #
1152 foreach {id dual} {
1153 1 {CREATE TABLE dual AS SELECT 'X' AS dummy}
1154 2 {CREATE TEMP TABLE dual AS SELECT 'X' AS dummy}
1155 3 {CREATE VIEW dual(dummy) AS VALUES('X')}
1156 4 {CREATE TEMP VIEW dual(dummy) AS VALUES('X')}
1157 } {
1158 reset_db
1159 db eval $dual
1160 do_execsql_test 25.$id {
1161 WITH cte1 AS (
1162 SELECT TRUE, (
1163 WITH cte2 AS (SELECT avg(DISTINCT TRUE) FROM dual)
1164 SELECT 2571 FROM cte2
1165 ) AS subquery1
1166 FROM dual
1167 GROUP BY 1
1168 )
1169 SELECT (SELECT 1324 FROM cte1) FROM cte1;
1170 } {1324}
1171 }
1173 do_catchsql_test 26.0 {
1174 WITH i(x) AS (
1175 VALUES(1) UNION ALL SELECT x+1 FRO, a.b,O. * ,I¬i O, a.b,O. * ORDER BY 1
1176 )
1177 SELECT x,O. * O FROM i ¬I,I? 10;
1178 } {1 {near "O": syntax error}}
1180 # 2020-09-17 ticket c51489c3b8f919c5
1181 # DISTINCT cannot be ignored in a UNION ALL recursive CTE
1182 #
1183 reset_db
1184 do_execsql_test 26.1 {
1185 CREATE TABLE t (label VARCHAR(10), step INTEGER);
1186 INSERT INTO T VALUES('a', 1);
1187 INSERT INTO T VALUES('a', 1);
1188 INSERT INTO T VALUES('b', 1);
1189 WITH RECURSIVE cte(label, step) AS (
1190 SELECT DISTINCT * FROM t
1191 UNION ALL
1192 SELECT label, step + 1 FROM cte WHERE step < 3
1193 )
1194 SELECT * FROM cte ORDER BY +label, +step;
1195 } {a 1 a 2 a 3 b 1 b 2 b 3}
1196 do_execsql_test 26.2 {
1197 WITH RECURSIVE cte(label, step) AS (
1198 SELECT * FROM t
1199 UNION
1200 SELECT label, step + 1 FROM cte WHERE step < 3
1201 )
1202 SELECT * FROM cte ORDER BY +label, +step;
1203 } {a 1 a 2 a 3 b 1 b 2 b 3}
1204 do_execsql_test 26.3 {
1205 CREATE TABLE tworow(x);
1206 INSERT INTO tworow(x) VALUES(1),(2);
1207 DELETE FROM t WHERE rowid=2;
1208 WITH RECURSIVE cte(label, step) AS (
1209 SELECT * FROM t
1210 UNION ALL
1211 SELECT DISTINCT label, step + 1 FROM cte, tworow WHERE step < 3
1212 )
1213 SELECT * FROM cte ORDER BY +label, +step;
1214 } {a 1 a 2 a 3 b 1 b 2 b 3}
1216 # 2021-05-20
1217 # forum post https://sqlite.org/forum/forumpost/8590e3f6dc
1218 #
1219 reset_db
1220 do_execsql_test 27.1 {
1221 CREATE TABLE t1(k);
1222 CREATE TABLE log(k, cte_map, main_map);
1223 CREATE TABLE map(k, v);
1224 INSERT INTO map VALUES(1, 'main1'), (2, 'main2');
1226 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
1227 INSERT INTO log
1228 WITH map(k,v) AS (VALUES(1,'cte1'),(2,'cte2'))
1229 SELECT
1230 new.k,
1231 (SELECT v FROM map WHERE k=new.k),
1232 (SELECT v FROM main.map WHERE k=new.k);
1233 END;
1235 INSERT INTO t1 VALUES(1);
1236 INSERT INTO t1 VALUES(2);
1237 SELECT k, cte_map, main_map, '|' FROM log ORDER BY k;
1238 } {1 cte1 main1 | 2 cte2 main2 |}
1240 finish_test