gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/with1.test (about) 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 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); 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