| OLD | NEW |
|---|
| (Empty) |
| 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 9
0 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 {recursive reference in a subquery: 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 } {53467891267219534819834256785976142342685379171392485696153728428741963534528
6179} | |
| 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 #------------------------------------------------------------------------- | |
| 869 do_execsql_test 17.1 { | |
| 870 WITH x(a) AS ( | |
| 871 WITH y(b) AS (SELECT 10) | |
| 872 SELECT 9 UNION ALL SELECT * FROM y | |
| 873 ) | |
| 874 SELECT * FROM x | |
| 875 } {9 10} | |
| 876 | |
| 877 do_execsql_test 17.2 { | |
| 878 WITH x AS ( | |
| 879 WITH y(b) AS (SELECT 10) | |
| 880 SELECT * FROM y UNION ALL SELECT * FROM y | |
| 881 ) | |
| 882 SELECT * FROM x | |
| 883 } {10 10} | |
| 884 | |
| 885 do_test 17.2 { | |
| 886 db eval { | |
| 887 WITH x AS ( | |
| 888 WITH y(b) AS (SELECT 10) | |
| 889 SELECT * FROM y UNION ALL SELECT * FROM y | |
| 890 ) | |
| 891 SELECT * FROM x | |
| 892 } A { | |
| 893 # no op | |
| 894 } | |
| 895 set A(*) | |
| 896 } {b} | |
| 897 | |
| 898 do_catchsql_test 17.3 { | |
| 899 WITH i AS ( | |
| 900 WITH j AS (SELECT 5) | |
| 901 SELECT 5 FROM i UNION SELECT 8 FROM i | |
| 902 ) | |
| 903 SELECT * FROM i; | |
| 904 } {1 {circular reference: i}} | |
| 905 | |
| 906 do_catchsql_test 17.4 { | |
| 907 WITH i AS ( | |
| 908 WITH j AS (SELECT 5) | |
| 909 SELECT 5 FROM t1 UNION SELECT 8 FROM t11 | |
| 910 ) | |
| 911 SELECT * FROM i; | |
| 912 } {1 {no such table: t11}} | |
| 913 | |
| 914 do_execsql_test 17.5 { | |
| 915 WITH | |
| 916 x1 AS (SELECT 10), | |
| 917 x2 AS (SELECT * FROM x1), | |
| 918 x3 AS ( | |
| 919 WITH x1 AS (SELECT 11) | |
| 920 SELECT * FROM x2 UNION ALL SELECT * FROM x2 | |
| 921 ) | |
| 922 SELECT * FROM x3; | |
| 923 } {10 10} | |
| 924 | |
| 925 do_execsql_test 17.6 { | |
| 926 WITH | |
| 927 x1 AS (SELECT 10), | |
| 928 x2 AS (SELECT * FROM x1), | |
| 929 x3 AS ( | |
| 930 WITH x1 AS (SELECT 11) | |
| 931 SELECT * FROM x2 UNION ALL SELECT * FROM x1 | |
| 932 ) | |
| 933 SELECT * FROM x3; | |
| 934 } {10 11} | |
| 935 | |
| 936 do_execsql_test 17.7 { | |
| 937 WITH | |
| 938 x1 AS (SELECT 10), | |
| 939 x2 AS (SELECT * FROM x1), | |
| 940 x3 AS ( | |
| 941 WITH | |
| 942 x1 AS ( SELECT 11 ), | |
| 943 x4 AS ( SELECT * FROM x2 ) | |
| 944 SELECT * FROM x4 UNION ALL SELECT * FROM x1 | |
| 945 ) | |
| 946 SELECT * FROM x3; | |
| 947 } {10 11} | |
| 948 | |
| 949 do_execsql_test 17.8 { | |
| 950 WITH | |
| 951 x1 AS (SELECT 10), | |
| 952 x2 AS (SELECT * FROM x1), | |
| 953 x3 AS ( | |
| 954 WITH | |
| 955 x1 AS ( SELECT 11 ), | |
| 956 x4 AS ( SELECT * FROM x2 ) | |
| 957 SELECT * FROM x4 UNION ALL SELECT * FROM x1 | |
| 958 ) | |
| 959 SELECT * FROM x3; | |
| 960 } {10 11} | |
| 961 | |
| 962 do_execsql_test 17.9 { | |
| 963 WITH | |
| 964 x1 AS (SELECT 10), | |
| 965 x2 AS (SELECT 11), | |
| 966 x3 AS ( | |
| 967 SELECT * FROM x1 UNION ALL SELECT * FROM x2 | |
| 968 ), | |
| 969 x4 AS ( | |
| 970 WITH | |
| 971 x1 AS (SELECT 12), | |
| 972 x2 AS (SELECT 13) | |
| 973 SELECT * FROM x3 | |
| 974 ) | |
| 975 SELECT * FROM x4; | |
| 976 } {10 11} | |
| 977 | |
| 978 # Added to test a fix to a faulty assert() discovered by libFuzzer. | |
| 979 # | |
| 980 do_execsql_test 18.1 { | |
| 981 WITH xyz(x) AS (VALUES(NULL) UNION SELECT round(1<x) FROM xyz ORDER BY 1) | |
| 982 SELECT quote(x) FROM xyz; | |
| 983 } {NULL} | |
| 984 do_execsql_test 18.2 { | |
| 985 WITH xyz(x) AS ( | |
| 986 SELECT printf('%d', 5) * NULL | |
| 987 UNION SELECT round(1<1+x) | |
| 988 FROM xyz ORDER BY 1 | |
| 989 ) | |
| 990 SELECT 1 FROM xyz; | |
| 991 } 1 | |
| 992 | |
| 993 | |
| 994 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
