github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/with (about) 1 statement ok 2 CREATE TABLE x(a) AS SELECT generate_series(1, 3) 3 4 statement ok 5 CREATE TABLE y(a) AS SELECT generate_series(2, 4) 6 7 query I rowsort 8 WITH t AS (SELECT a FROM y WHERE a < 3) 9 SELECT * FROM x NATURAL JOIN t 10 ---- 11 2 12 13 query I 14 WITH t AS (SELECT * FROM y WHERE a < 3) 15 SELECT * FROM x NATURAL JOIN t 16 ---- 17 2 18 19 # Using a CTE inside a subquery 20 query I rowsort 21 WITH t(x) AS (SELECT a FROM x) 22 SELECT * FROM y WHERE a IN (SELECT x FROM t) 23 ---- 24 2 25 3 26 27 # Using a subquery inside a CTE 28 query I 29 SELECT * FROM x WHERE a IN 30 (WITH t AS (SELECT * FROM y WHERE a < 3) SELECT * FROM t) 31 ---- 32 2 33 34 # Rename columns 35 query II rowsort 36 WITH t(b) AS (SELECT a FROM x) SELECT b, t.b FROM t 37 ---- 38 1 1 39 2 2 40 3 3 41 42 query BB 43 WITH t(a, b) AS (SELECT true a, false b) 44 SELECT a, b FROM t 45 ---- 46 true false 47 48 query BB 49 WITH t(b, a) AS (SELECT true a, false b) 50 SELECT a, b FROM t 51 ---- 52 false true 53 54 statement error WITH clause containing a data-modifying statement must be at the top level 55 SELECT (WITH foo AS (INSERT INTO y VALUES (1) RETURNING *) SELECT * FROM foo) 56 57 statement error WITH query name t specified more than once 58 WITH 59 t AS (SELECT true), 60 t AS (SELECT false) 61 SELECT * FROM t 62 63 query error source "t" has 1 columns available but 2 columns specified 64 WITH t(b, c) AS (SELECT a FROM x) SELECT b, t.b FROM t 65 66 # Ensure you can't reference the original table name 67 query error no data source matches prefix: x 68 WITH t AS (SELECT a FROM x) SELECT a, x.t FROM t 69 70 # Nested WITH, name shadowing 71 query I 72 WITH t(x) AS (WITH t(x) AS (SELECT 1) SELECT x * 10 FROM t) SELECT x + 2 FROM t 73 ---- 74 12 75 76 # CTEs with DMLs 77 78 query error pgcode 42P01 relation "t" does not exist 79 WITH t AS (SELECT * FROM x) INSERT INTO t VALUES (1) 80 81 query I rowsort 82 WITH t AS (SELECT a FROM x) INSERT INTO x SELECT a + 20 FROM t RETURNING * 83 ---- 84 21 85 22 86 23 87 88 query I rowsort 89 SELECT * from x 90 ---- 91 1 92 2 93 3 94 21 95 22 96 23 97 98 query I rowsort 99 WITH t AS ( 100 UPDATE x SET a = a * 100 RETURNING a 101 ) 102 SELECT * FROM t 103 ---- 104 100 105 200 106 300 107 2100 108 2200 109 2300 110 111 query I rowsort 112 SELECT * from x 113 ---- 114 100 115 200 116 300 117 2100 118 2200 119 2300 120 121 query I rowsort 122 WITH t AS ( 123 DELETE FROM x RETURNING a 124 ) 125 SELECT * FROM t 126 ---- 127 100 128 200 129 300 130 2100 131 2200 132 2300 133 134 query I rowsort 135 SELECT * from x 136 ---- 137 138 # #22420: ensure okay error message for CTE clause without output columns 139 query error WITH clause "t" does not return any columns 140 WITH t AS ( 141 INSERT INTO x(a) VALUES(0) 142 ) 143 SELECT * FROM t 144 145 # however if there are no side effects, no errors are required. 146 query I 147 WITH t AS (SELECT 1) SELECT 2 148 ---- 149 2 150 151 # Regression tests for #24303. 152 153 statement ok 154 CREATE TABLE a(x INT); 155 156 statement count 3 157 INSERT INTO a(x) 158 (WITH b(z) AS (VALUES (1),(2),(3)) SELECT z+1 AS w FROM b) 159 160 statement count 1 161 INSERT INTO a(x) 162 (WITH a(z) AS (VALUES (1)) SELECT z+1 AS w FROM a); 163 164 # When #24303 is fixed, the following query should succeed. 165 query error unimplemented: multiple WITH clauses in parentheses 166 (WITH woo AS (VALUES (1)) 167 (WITH waa AS (VALUES (2)) 168 TABLE waa)) 169 170 171 # When #24303 is fixed, the following query should fail with 172 # error "no such relation woo". 173 query error unimplemented: multiple WITH clauses in parentheses 174 (WITH woo AS (VALUES (1)) 175 (WITH waa AS (VALUES (2)) 176 TABLE woo)) 177 178 statement ok 179 CREATE TABLE lim(x) AS SELECT 0 180 181 # This is an oddity in PostgreSQL: even though the WITH clause 182 # occurs in the inside parentheses, the scope of the alias `lim` 183 # extends to the outer parentheses. 184 query I 185 ((WITH lim(x) AS (SELECT 1) SELECT 123) 186 LIMIT ( 187 SELECT x FROM lim -- intuitively this should refer to the real table lim defined above 188 -- and use LIMIT 0; 189 -- however, postgres flattens the inner WITH and outer LIMIT 190 -- at the same scope so the limit becomes 1. 191 )) 192 ---- 193 123 194 195 # Ditto if table `lim` did not even exist. 196 statement ok 197 DROP TABLE lim 198 199 query I 200 ((WITH lim(x) AS (SELECT 1) SELECT 123) LIMIT (SELECT x FROM lim)) 201 ---- 202 123 203 204 # CTE with an ORDER BY. 205 206 statement ok 207 CREATE TABLE ab (a INT PRIMARY KEY, b INT) 208 209 statement ok 210 INSERT INTO ab VALUES (1, 2), (3, 4), (5, 6) 211 212 query I rowsort 213 WITH a AS (SELECT a FROM ab ORDER BY b) SELECT * FROM a 214 ---- 215 1 216 3 217 5 218 219 statement ok 220 CREATE TABLE x2(a) AS SELECT generate_series(1, 3) 221 222 statement ok 223 CREATE TABLE y2(b) AS SELECT generate_series(2, 4) 224 225 # Referencing a CTE multiple times. 226 query II rowsort 227 WITH t AS (SELECT b FROM y2) SELECT * FROM t JOIN t AS q ON true 228 ---- 229 2 2 230 2 3 231 2 4 232 3 2 233 3 3 234 3 4 235 4 2 236 4 3 237 4 4 238 239 query II rowsort 240 WITH 241 one AS (SELECT a AS u FROM x2), 242 two AS (SELECT b AS v FROM (SELECT b FROM y2 UNION ALL SELECT u FROM one)) 243 SELECT 244 * 245 FROM 246 one JOIN two ON u = v 247 ---- 248 1 1 249 2 2 250 3 3 251 2 2 252 3 3 253 254 # Mutation CTEs that aren't referenced elsewhere in the query. 255 statement ok 256 CREATE TABLE z (c INT PRIMARY KEY); 257 258 query I 259 WITH foo AS (INSERT INTO z VALUES (10) RETURNING 1) SELECT 2 260 ---- 261 2 262 263 query I 264 SELECT * FROM z 265 ---- 266 10 267 268 query I 269 WITH foo AS (UPDATE z SET c = 20 RETURNING 1) SELECT 3 270 ---- 271 3 272 273 query I 274 SELECT * FROM z 275 ---- 276 20 277 278 query I 279 WITH foo AS (DELETE FROM z RETURNING 1) SELECT 4 280 ---- 281 4 282 283 query I 284 SELECT count(*) FROM z 285 ---- 286 0 287 288 # WITH and prepared statements. 289 290 statement ok 291 CREATE TABLE engineer ( 292 fellow BOOL NOT NULL, id INT4 NOT NULL, companyname VARCHAR(255) NOT NULL, 293 PRIMARY KEY (id, companyname) 294 ) 295 296 statement ok 297 PREPARE x (INT4, VARCHAR, INT4, VARCHAR) AS 298 WITH ht_engineer (id, companyname) AS ( 299 SELECT id, companyname FROM (VALUES ($1, $2), ($3, $4)) AS ht (id, companyname) 300 ) 301 DELETE FROM engineer WHERE (id, companyname) IN (SELECT id, companyname FROM ht_engineer) 302 303 statement ok 304 EXECUTE x (1, 'fo', 2, 'bar') 305 306 statement ok 307 PREPARE z(int) AS WITH foo AS (SELECT * FROM x2 WHERE a = $1) SELECT * FROM foo 308 309 query I 310 EXECUTE z(1) 311 ---- 312 1 313 314 query I 315 EXECUTE z(2) 316 ---- 317 2 318 319 query I 320 EXECUTE z(3) 321 ---- 322 3 323 324 # WITH containing a placeholder that isn't referenced. 325 326 statement ok 327 PREPARE z2(int) AS WITH foo AS (SELECT * FROM x WHERE a = $1) SELECT * FROM x2 ORDER BY a 328 329 query I 330 EXECUTE z2(1) 331 ---- 332 1 333 2 334 3 335 336 statement ok 337 PREPARE z3(int) AS WITH foo AS (SELECT $1) SELECT * FROM foo 338 339 query I 340 EXECUTE z3(3) 341 ---- 342 3 343 344 statement ok 345 PREPARE z4(int) AS WITH foo AS (SELECT $1), bar AS (SELECT * FROM foo) SELECT * FROM bar 346 347 query I 348 EXECUTE z4(3) 349 ---- 350 3 351 352 statement ok 353 PREPARE z5(int, int) AS WITH foo AS (SELECT $1), bar AS (SELECT $2) (SELECT * FROM foo) UNION ALL (SELECT * FROM bar) 354 355 query I rowsort 356 EXECUTE z5(3, 5) 357 ---- 358 3 359 5 360 361 # TODO(justin): re-enable this, we don't allow WITHs having outer columns. 362 # statement ok 363 # PREPARE z6(int) AS 364 # SELECT * FROM 365 # (VALUES (1), (2)) v(x), 366 # LATERAL (SELECT * FROM 367 # (WITH foo AS (SELECT $1 + x) SELECT * FROM foo) 368 # ) 369 370 # query II 371 # EXECUTE z6(3) 372 # ---- 373 # 1 4 374 # 2 5 375 376 # Recursive CTE example from postgres docs. 377 query T 378 WITH RECURSIVE t(n) AS ( 379 VALUES (1) 380 UNION ALL 381 SELECT n+1 FROM t WHERE n < 100 382 ) 383 SELECT sum(n) FROM t 384 ---- 385 5050 386 387 # Test where initial query has duplicate columns. 388 query II 389 WITH RECURSIVE cte(a, b) AS ( 390 SELECT 0, 0 391 UNION ALL 392 SELECT a+1, b+10 FROM cte WHERE a < 5 393 ) SELECT * FROM cte; 394 ---- 395 0 0 396 1 10 397 2 20 398 3 30 399 4 40 400 5 50 401 402 # Test where recursive query has duplicate columns. 403 query II 404 WITH RECURSIVE cte(a, b) AS ( 405 SELECT 0, 1 406 UNION ALL 407 SELECT a+1, a+1 FROM cte WHERE a < 5 408 ) SELECT * FROM cte; 409 ---- 410 0 1 411 1 1 412 2 2 413 3 3 414 4 4 415 5 5 416 417 # Recursive CTE examples adapted from 418 # https://malisper.me/generating-fractals-with-postgres-escape-time-fractals. 419 query T 420 WITH RECURSIVE points AS ( 421 SELECT i::float * 0.05 AS r, j::float * 0.05 AS c 422 FROM generate_series(-20, 20) AS a (i), generate_series(-40, 20) AS b (j) 423 ), iterations AS ( 424 SELECT r, 425 c, 426 0.0::float AS zr, 427 0.0::float AS zc, 428 0 AS iteration 429 FROM points 430 UNION ALL 431 SELECT r, 432 c, 433 zr*zr - zc*zc + c AS zr, 434 2*zr*zc + r AS zc, 435 iteration+1 AS iteration 436 FROM iterations WHERE zr*zr + zc*zc < 4 AND iteration < 20 437 ), final_iteration AS ( 438 SELECT * FROM iterations WHERE iteration = 20 439 ), marked_points AS ( 440 SELECT r, 441 c, 442 (CASE WHEN EXISTS (SELECT 1 FROM final_iteration i WHERE p.r = i.r AND p.c = i.c) 443 THEN 'oo' ELSE '··' END) AS marker FROM points p 444 ), lines AS ( 445 SELECT r, string_agg(marker, '' ORDER BY c ASC) AS r_text 446 FROM marked_points 447 GROUP BY r 448 ) SELECT string_agg(r_text, E'\n' ORDER BY r DESC) FROM lines 449 ---- 450 ················································································oo········································ 451 ············································································oo············································ 452 ··········································································oooo············································ 453 ······································································oo··oooo············································ 454 ········································································oooooooo·········································· 455 ······································································oooooooooooo········································ 456 ········································································oooooooo·········································· 457 ··························································oo····oooooooooooooooooooo··oo·································· 458 ··························································oooo··oooooooooooooooooooooooo·································· 459 ··························································oooooooooooooooooooooooooooooooooooooo·························· 460 ··························································oooooooooooooooooooooooooooooooooooooo·························· 461 ····················································oooooooooooooooooooooooooooooooooooooooooo···························· 462 ······················································oooooooooooooooooooooooooooooooooooooooo···························· 463 ····················································oooooooooooooooooooooooooooooooooooooooooooooo························ 464 ··································oo····oo··········oooooooooooooooooooooooooooooooooooooooooooo·························· 465 ··································oooooooooooo······oooooooooooooooooooooooooooooooooooooooooooo·························· 466 ··································oooooooooooooo····oooooooooooooooooooooooooooooooooooooooooooooo························ 467 ································oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo························ 468 ······························oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·························· 469 ··························oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo···························· 470 ··oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo······························ 471 ··························oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo···························· 472 ······························oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·························· 473 ································oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo························ 474 ··································oooooooooooooo····oooooooooooooooooooooooooooooooooooooooooooooo························ 475 ··································oooooooooooo······oooooooooooooooooooooooooooooooooooooooooooo·························· 476 ··································oo····oo··········oooooooooooooooooooooooooooooooooooooooooooo·························· 477 ····················································oooooooooooooooooooooooooooooooooooooooooooooo························ 478 ······················································oooooooooooooooooooooooooooooooooooooooo···························· 479 ····················································oooooooooooooooooooooooooooooooooooooooooo···························· 480 ··························································oooooooooooooooooooooooooooooooooooooo·························· 481 ··························································oooooooooooooooooooooooooooooooooooooo·························· 482 ··························································oooo··oooooooooooooooooooooooo·································· 483 ··························································oo····oooooooooooooooooooo··oo·································· 484 ········································································oooooooo·········································· 485 ······································································oooooooooooo········································ 486 ········································································oooooooo·········································· 487 ······································································oo··oooo············································ 488 ··········································································oooo············································ 489 ············································································oo············································ 490 ················································································oo········································ 491 492 query T 493 WITH RECURSIVE points AS ( 494 SELECT i::float * 0.05 AS r, j::float * 0.05 AS c 495 FROM generate_series(-20, 20) AS a (i), generate_series(-30, 30) AS b (j) 496 ), iterations AS ( 497 SELECT r, c, c::float AS zr, r::float AS zc, 0 AS iteration FROM points 498 UNION ALL 499 SELECT r, c, zr*zr - zc*zc + 1 - 1.61803398875 AS zr, 2*zr*zc AS zc, iteration+1 AS iteration 500 FROM iterations WHERE zr*zr + zc*zc < 4 AND iteration < 20 501 ), final_iteration AS ( 502 SELECT * FROM iterations WHERE iteration = 20 503 ), marked_points AS ( 504 SELECT r, c, (CASE WHEN EXISTS (SELECT 1 FROM final_iteration i WHERE p.r = i.r AND p.c = i.c) 505 THEN 'oo' 506 ELSE '··' 507 END) AS marker 508 FROM points p 509 ), rows AS ( 510 SELECT r, string_agg(marker, '' ORDER BY c ASC) AS r_text 511 FROM marked_points 512 GROUP BY r 513 ) SELECT string_agg(r_text, E'\n' ORDER BY r DESC) FROM rows 514 ---- 515 ·························································································································· 516 ·························································································································· 517 ····························································oo···························································· 518 ····························································oo···························································· 519 ························································oooooooooo························································ 520 ························································oooooooooo························································ 521 ························································oooooooooo························································ 522 ··············································oo··oooooooooooooooooooooo··oo·············································· 523 ··············································oooooooooooooooooooooooooooooo·············································· 524 ············································oooooooooooooooooooooooooooooooooo············································ 525 ··········································oooooooooooooooooooooooooooooooooooooo·········································· 526 ························oooo····oo········oooooooooooooooooooooooooooooooooooooo········oo····oooo························ 527 ························oooooooooooooo····oooooooooooooooooooooooooooooooooooooo····oooooooooooooo························ 528 ······················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo······················ 529 ····················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo···················· 530 ··················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·················· 531 ··················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·················· 532 ··········oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·········· 533 ··········oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·········· 534 ······oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo······ 535 ····oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo···· 536 ······oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo······ 537 ··········oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·········· 538 ··········oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·········· 539 ··················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·················· 540 ··················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo·················· 541 ····················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo···················· 542 ······················oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo······················ 543 ························oooooooooooooo····oooooooooooooooooooooooooooooooooooooo····oooooooooooooo························ 544 ························oooo····oo········oooooooooooooooooooooooooooooooooooooo········oo····oooo························ 545 ··········································oooooooooooooooooooooooooooooooooooooo·········································· 546 ············································oooooooooooooooooooooooooooooooooo············································ 547 ··············································oooooooooooooooooooooooooooooo·············································· 548 ··············································oo··oooooooooooooooooooooo··oo·············································· 549 ························································oooooooooo························································ 550 ························································oooooooooo························································ 551 ························································oooooooooo························································ 552 ····························································oo···························································· 553 ····························································oo···························································· 554 ·························································································································· 555 ·························································································································· 556 557 # Regression test for #45869 (CTE inside recursive CTE). 558 query T rowsort 559 WITH RECURSIVE x(a) AS ( 560 VALUES ('a'), ('b') 561 UNION ALL 562 (WITH z AS (SELECT * FROM x) 563 SELECT z.a || z1.a AS a FROM z CROSS JOIN z AS z1 WHERE length(z.a) < 3 564 ) 565 ) 566 SELECT * FROM x 567 ---- 568 a 569 b 570 aa 571 ba 572 ab 573 bb 574 aaaa 575 baaa 576 abaa 577 bbaa 578 aaba 579 baba 580 abba 581 bbba 582 aaab 583 baab 584 abab 585 bbab 586 aabb 587 babb 588 abbb 589 bbbb