Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / with1.test
blob42d2277aeffac418354be0e1c53686889993fc16
1 # 2014 January 11
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.  The
12 # focus of this file is testing the WITH clause.
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set ::testprefix with1
19 ifcapable {!cte} {
20   finish_test
21   return
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 #-------------------------------------------------------------------------
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);
171   
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 #-------------------------------------------------------------------------
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');
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
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);
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 {recursive reference in a subquery: 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
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
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     ),
425   
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     ),
432   
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.
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]
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.
491 do_execsql_test 10.1 {
492   DROP TABLE IF EXISTS tree;
493   CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
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   }
515 insert_into_tree {
516   /a/a/a
517   /a/b/c
518   /a/b/c/d
519   /a/b/d
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
534 # Scan the tree-structure currently stored in table tree. Return a list
535 # of nodes visited.
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   "
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
567 # Breadth first, siblings in ascending order.
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
577 # Depth first, siblings in ascending order.
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
588 # Breadth first, siblings in descending order.
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 
598 # Depth first, siblings in ascending order.
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
610 # Test name resolution in ORDER BY clauses.
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.
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
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
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
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
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.
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.
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.
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 
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}
831 finish_test