test/with1.test

   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 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set ::testprefix with1
  18
  19 ifcapable {!cte} {
  20   finish_test
  21   return
  22 }
  23
  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}
  28
  29 do_execsql_test 1.1 {
  30   SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
  31 } {10}
  32
  33 do_execsql_test 1.2 {
  34   WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
  35 } {}
  36
  37 do_execsql_test 1.3 {
  38   WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
  39 } {}
  40
  41 do_execsql_test 1.4 {
  42   WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
  43 } {}
  44
  45 #--------------------------------------------------------------------------
  46
  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}
  54
  55 do_execsql_test 2.2 {
  56   WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
  57 } {1 2}
  58
  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}
  64
  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}
  70
  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}
  76
  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}}
  83
  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}}
  90
  91 do_execsql_test 3.3 {
  92   CREATE TABLE t3(x);
  93   CREATE TABLE t4(x);
  94
  95   INSERT INTO t3 VALUES('T3');
  96   INSERT INTO t4 VALUES('T4');
  97
  98   WITH t3(a) AS (SELECT * FROM t4)
  99   SELECT * FROM t3;
 100 } {T4}
 101
 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}
 107
 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}
 113
 114 do_catchsql_test 3.6 {
 115   WITH tmp AS ( SELECT * FROM t3 ),
 116   SELECT * FROM tmp;
 117 } {1 {near "SELECT": syntax error}}
 118
 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);
 127
 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}
 132
 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}
 138
 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}
 144
 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}
 151
 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}}
 156
 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}
 204
 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}}
 209
 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}
 214
 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}
 219
 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}}
 224
 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}}
 229
 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}}
 235
 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}}
 240
 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}}
 245
 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}}
 250
 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}}
 255
 256 #-------------------------------------------------------------------------
 257 #
 258 do_execsql_test 6.1 {
 259   CREATE TABLE f(
 260       id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
 261   );
 262
 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 }
 279
 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 }
 299
 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}
 308
 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}
 317
 318
 319 #-------------------------------------------------------------------------
 320
 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 }
 329
 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}
 338
 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}
 347
 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}}
 356
 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}}
 365
 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}}
 374
 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                                     +.}}
 417
 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     ),
 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}
 454
 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 }
 476
 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
 486
 487 #--------------------------------------------------------------------------
 488 # Test the ORDER BY clause on recursive tables.
 489 #
 490
 491 do_execsql_test 10.1 {
 492   DROP TABLE IF EXISTS tree;
 493   CREATE TABLE tree(id INTEGER PRIMARY KEY, parentid, payload);
 494 }
 495
 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 }
 514
 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 }
 533
 534 # Scan the tree-structure currently stored in table tree. Return a list
 535 # of nodes visited.
 536 #
 537 proc scan_tree {bDepthFirst bReverse} {
 538
 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   }
 546
 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 }
 557
 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 }
 566
 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 }]
 576
 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 }]
 587
 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 }]
 597
 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 }]
 608
 609
 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}
 630
 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 }
 644
 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 }
 671
 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 }
 686
 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}
 700
 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}}
 757
 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}}
 788
 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}}
 818
 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}
 829
 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}}
 845
 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 } {}
 851
 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}}
 859
 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}}
 867
 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 }
 888
 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}
 897
 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}
 905
 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}
 918
 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}}
 926
 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}}
 934
 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}
 945
 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}
 956
 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}
 969
 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}
 982
 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}
 998
 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
1013
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 }
1033
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}
1046
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 } {{}}
1073
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 NOT MATERIALIZED (
1079      WITH RECURSIVE c AS NOT MATERIALIZED (
1080         WITH RECURSIVE c AS NOT MATERIALIZED (
1081            WITH RECURSIVE c AS NOT MATERIALIZED (
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}}
1093
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}
1119
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 * FROM v1 AS aa, v1 AS bb, v1 AS cc}]
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);
1134
1135   CREATE VIEW aux.v2 AS SELECT * FROM v3;
1136   CREATE VIEW main.v2 AS SELECT * FROM v3;
1137
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 }
1142
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 }
1172
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}}
1179
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}
1215
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');
1225
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;
1234
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 |}
1239
1240 finish_test