| 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://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 |
# forum post https://sqlite.org/forum/forumpost/2026-03-04T05:06:26Z |
| 1241 |
# |
| 1242 |
db null NULL |
| 1243 |
do_execsql_test 28.1 { |
| 1244 |
DROP TABLE t1; |
| 1245 |
CREATE TABLE t1(x INTEGER PRIMARY KEY); |
| 1246 |
INSERT INTO t1 VALUES(1),(4),(999); |
| 1247 |
SELECT ( |
| 1248 |
WITH RECURSIVE t2(y) AS ( |
| 1249 |
SELECT 4 |
| 1250 |
UNION |
| 1251 |
SELECT NULL |
| 1252 |
UNION |
| 1253 |
SELECT y+1 FROM t2 WHERE y=4 ORDER BY 1 |
| 1254 |
) |
| 1255 |
SELECT 1 FROM t2 WHERE y=x |
| 1256 |
) FROM t1; |
| 1257 |
} {NULL 1 NULL} |
| 1258 |
|
| 1259 |
finish_test |