github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/with (about) 1 exec-ddl 2 CREATE TABLE x(a INT, b INT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE y(a INT) 7 ---- 8 9 build 10 WITH t AS (SELECT a FROM y WHERE a < 3) 11 SELECT * FROM x NATURAL JOIN t 12 ---- 13 with &1 (t) 14 ├── columns: a:3!null b:4 15 ├── project 16 │ ├── columns: y.a:1!null 17 │ └── select 18 │ ├── columns: y.a:1!null y.rowid:2!null 19 │ ├── scan y 20 │ │ └── columns: y.a:1 y.rowid:2!null 21 │ └── filters 22 │ └── y.a:1 < 3 23 └── project 24 ├── columns: x.a:3!null b:4 25 └── inner-join (hash) 26 ├── columns: x.a:3!null b:4 x.rowid:5!null a:6!null 27 ├── scan x 28 │ └── columns: x.a:3 b:4 x.rowid:5!null 29 ├── with-scan &1 (t) 30 │ ├── columns: a:6!null 31 │ └── mapping: 32 │ └── y.a:1 => a:6 33 └── filters 34 └── x.a:3 = a:6 35 36 build 37 EXPLAIN 38 WITH t AS (SELECT a FROM y WHERE a < 3) 39 SELECT * FROM x NATURAL JOIN t 40 ---- 41 explain 42 ├── columns: tree:7 field:8 description:9 43 └── with &1 (t) 44 ├── columns: a:3!null b:4 45 ├── project 46 │ ├── columns: y.a:1!null 47 │ └── select 48 │ ├── columns: y.a:1!null y.rowid:2!null 49 │ ├── scan y 50 │ │ └── columns: y.a:1 y.rowid:2!null 51 │ └── filters 52 │ └── y.a:1 < 3 53 └── project 54 ├── columns: x.a:3!null b:4 55 └── inner-join (hash) 56 ├── columns: x.a:3!null b:4 x.rowid:5!null a:6!null 57 ├── scan x 58 │ └── columns: x.a:3 b:4 x.rowid:5!null 59 ├── with-scan &1 (t) 60 │ ├── columns: a:6!null 61 │ └── mapping: 62 │ └── y.a:1 => a:6 63 └── filters 64 └── x.a:3 = a:6 65 66 build 67 WITH 68 q AS (SELECT NULL) 69 SELECT 70 1 + (SELECT * FROM q) 71 ---- 72 with &1 (q) 73 ├── columns: "?column?":3 74 ├── project 75 │ ├── columns: "?column?":1 76 │ ├── values 77 │ │ └── () 78 │ └── projections 79 │ └── NULL [as="?column?":1] 80 └── project 81 ├── columns: "?column?":3 82 ├── values 83 │ └── () 84 └── projections 85 └── NULL [as="?column?":3] 86 87 build 88 CREATE VIEW v1 AS 89 WITH t AS (SELECT a FROM y WHERE a < 3) 90 SELECT 1 FROM x NATURAL JOIN t 91 ---- 92 create-view t.public.v1 93 ├── WITH t AS (SELECT a FROM t.public.y WHERE a < 3) SELECT 1 FROM t.public.x NATURAL JOIN t 94 ├── columns: "?column?":7 95 └── dependencies 96 ├── y [columns: (0,1)] 97 └── x [columns: (0-2)] 98 99 build 100 CREATE TABLE t1 AS 101 WITH t AS (SELECT a FROM y WHERE a < 3) 102 SELECT 1 FROM x NATURAL JOIN t 103 ---- 104 create-table 105 ├── CREATE TABLE t1 AS WITH t AS (SELECT a FROM t.public.y WHERE a < 3) SELECT 1 FROM t.public.x NATURAL JOIN t 106 └── project 107 ├── columns: rowid:8 "?column?":7!null 108 ├── with &1 (t) 109 │ ├── columns: "?column?":7!null 110 │ ├── project 111 │ │ ├── columns: y.a:1!null 112 │ │ └── select 113 │ │ ├── columns: y.a:1!null y.rowid:2!null 114 │ │ ├── scan y 115 │ │ │ └── columns: y.a:1 y.rowid:2!null 116 │ │ └── filters 117 │ │ └── y.a:1 < 3 118 │ └── project 119 │ ├── columns: "?column?":7!null 120 │ ├── inner-join (hash) 121 │ │ ├── columns: x.a:3!null b:4 x.rowid:5!null a:6!null 122 │ │ ├── scan x 123 │ │ │ └── columns: x.a:3 b:4 x.rowid:5!null 124 │ │ ├── with-scan &1 (t) 125 │ │ │ ├── columns: a:6!null 126 │ │ │ └── mapping: 127 │ │ │ └── y.a:1 => a:6 128 │ │ └── filters 129 │ │ └── x.a:3 = a:6 130 │ └── projections 131 │ └── 1 [as="?column?":7] 132 └── projections 133 └── unique_rowid() [as=rowid:8] 134 135 build 136 WITH t AS (SELECT a FROM y WHERE a < 3) 137 SELECT * FROM t 138 ---- 139 with &1 (t) 140 ├── columns: a:3!null 141 ├── project 142 │ ├── columns: y.a:1!null 143 │ └── select 144 │ ├── columns: y.a:1!null rowid:2!null 145 │ ├── scan y 146 │ │ └── columns: y.a:1 rowid:2!null 147 │ └── filters 148 │ └── y.a:1 < 3 149 └── with-scan &1 (t) 150 ├── columns: a:3!null 151 └── mapping: 152 └── y.a:1 => a:3 153 154 # Chaining multiple CTEs. 155 build 156 WITH 157 t1 AS (SELECT a FROM y WHERE a < 3), 158 t2 AS (SELECT * FROM t1 WHERE a > 1) 159 SELECT * FROM t2 160 ---- 161 with &1 (t1) 162 ├── columns: a:4!null 163 ├── project 164 │ ├── columns: y.a:1!null 165 │ └── select 166 │ ├── columns: y.a:1!null rowid:2!null 167 │ ├── scan y 168 │ │ └── columns: y.a:1 rowid:2!null 169 │ └── filters 170 │ └── y.a:1 < 3 171 └── with &2 (t2) 172 ├── columns: a:4!null 173 ├── select 174 │ ├── columns: a:3!null 175 │ ├── with-scan &1 (t1) 176 │ │ ├── columns: a:3!null 177 │ │ └── mapping: 178 │ │ └── y.a:1 => a:3 179 │ └── filters 180 │ └── a:3 > 1 181 └── with-scan &2 (t2) 182 ├── columns: a:4!null 183 └── mapping: 184 └── a:3 => a:4 185 186 build 187 WITH 188 t1 AS (SELECT a FROM y WHERE a < 3), 189 t2 AS (SELECT * FROM t1 WHERE a > 1), 190 t3 AS (SELECT * FROM t2 WHERE a = 2) 191 SELECT * FROM t3 192 ---- 193 with &1 (t1) 194 ├── columns: a:5!null 195 ├── project 196 │ ├── columns: y.a:1!null 197 │ └── select 198 │ ├── columns: y.a:1!null rowid:2!null 199 │ ├── scan y 200 │ │ └── columns: y.a:1 rowid:2!null 201 │ └── filters 202 │ └── y.a:1 < 3 203 └── with &2 (t2) 204 ├── columns: a:5!null 205 ├── select 206 │ ├── columns: a:3!null 207 │ ├── with-scan &1 (t1) 208 │ │ ├── columns: a:3!null 209 │ │ └── mapping: 210 │ │ └── y.a:1 => a:3 211 │ └── filters 212 │ └── a:3 > 1 213 └── with &3 (t3) 214 ├── columns: a:5!null 215 ├── select 216 │ ├── columns: a:4!null 217 │ ├── with-scan &2 (t2) 218 │ │ ├── columns: a:4!null 219 │ │ └── mapping: 220 │ │ └── a:3 => a:4 221 │ └── filters 222 │ └── a:4 = 2 223 └── with-scan &3 (t3) 224 ├── columns: a:5!null 225 └── mapping: 226 └── a:4 => a:5 227 228 build 229 WITH 230 t1 AS (SELECT * FROM y WHERE a < 3), 231 t2 AS (SELECT * FROM y WHERE a > 1), 232 t3 AS (SELECT * FROM t1 WHERE a < 4), 233 t4 AS (SELECT * FROM t2 WHERE a > 3) 234 SELECT * FROM t3 NATURAL JOIN t4 235 ---- 236 with &1 (t1) 237 ├── columns: a:7!null 238 ├── project 239 │ ├── columns: y.a:1!null 240 │ └── select 241 │ ├── columns: y.a:1!null rowid:2!null 242 │ ├── scan y 243 │ │ └── columns: y.a:1 rowid:2!null 244 │ └── filters 245 │ └── y.a:1 < 3 246 └── with &2 (t2) 247 ├── columns: a:7!null 248 ├── project 249 │ ├── columns: y.a:3!null 250 │ └── select 251 │ ├── columns: y.a:3!null rowid:4!null 252 │ ├── scan y 253 │ │ └── columns: y.a:3 rowid:4!null 254 │ └── filters 255 │ └── y.a:3 > 1 256 └── with &3 (t3) 257 ├── columns: a:7!null 258 ├── select 259 │ ├── columns: a:5!null 260 │ ├── with-scan &1 (t1) 261 │ │ ├── columns: a:5!null 262 │ │ └── mapping: 263 │ │ └── y.a:1 => a:5 264 │ └── filters 265 │ └── a:5 < 4 266 └── with &4 (t4) 267 ├── columns: a:7!null 268 ├── select 269 │ ├── columns: a:6!null 270 │ ├── with-scan &2 (t2) 271 │ │ ├── columns: a:6!null 272 │ │ └── mapping: 273 │ │ └── y.a:3 => a:6 274 │ └── filters 275 │ └── a:6 > 3 276 └── project 277 ├── columns: a:7!null 278 └── inner-join (hash) 279 ├── columns: a:7!null a:8!null 280 ├── with-scan &3 (t3) 281 │ ├── columns: a:7!null 282 │ └── mapping: 283 │ └── a:5 => a:7 284 ├── with-scan &4 (t4) 285 │ ├── columns: a:8!null 286 │ └── mapping: 287 │ └── a:6 => a:8 288 └── filters 289 └── a:7 = a:8 290 291 # Make sure they scope properly. 292 build 293 WITH t AS (SELECT true) SELECT * FROM (WITH t AS (SELECT false) SELECT * FROM t) 294 ---- 295 with &1 (t) 296 ├── columns: bool:3!null 297 ├── project 298 │ ├── columns: bool:1!null 299 │ ├── values 300 │ │ └── () 301 │ └── projections 302 │ └── true [as=bool:1] 303 └── with &2 (t) 304 ├── columns: bool:3!null 305 ├── project 306 │ ├── columns: bool:2!null 307 │ ├── values 308 │ │ └── () 309 │ └── projections 310 │ └── false [as=bool:2] 311 └── with-scan &2 (t) 312 ├── columns: bool:3!null 313 └── mapping: 314 └── bool:2 => bool:3 315 316 build 317 WITH 318 t AS (SELECT true), 319 t AS (SELECT false) 320 SELECT * FROM t 321 ---- 322 error (42712): WITH query name t specified more than once 323 324 # Using a CTE once in another CTE and once otherwise. 325 build 326 WITH 327 t1 AS (SELECT true), 328 t2 AS (SELECT * FROM t1) 329 SELECT * FROM t1 NATURAL JOIN t2 330 ---- 331 with &1 (t1) 332 ├── columns: bool:3!null 333 ├── project 334 │ ├── columns: bool:1!null 335 │ ├── values 336 │ │ └── () 337 │ └── projections 338 │ └── true [as=bool:1] 339 └── with &2 (t2) 340 ├── columns: bool:3!null 341 ├── with-scan &1 (t1) 342 │ ├── columns: bool:2!null 343 │ └── mapping: 344 │ └── bool:1 => bool:2 345 └── project 346 ├── columns: bool:3!null 347 └── inner-join (hash) 348 ├── columns: bool:3!null bool:4!null 349 ├── with-scan &1 (t1) 350 │ ├── columns: bool:3!null 351 │ └── mapping: 352 │ └── bool:1 => bool:3 353 ├── with-scan &2 (t2) 354 │ ├── columns: bool:4!null 355 │ └── mapping: 356 │ └── bool:2 => bool:4 357 └── filters 358 └── bool:3 = bool:4 359 360 build 361 WITH 362 t1 AS (SELECT * FROM x), 363 t2 AS (SELECT * FROM x NATURAL JOIN t1) 364 SELECT * FROM t2 NATURAL JOIN x 365 ---- 366 with &1 (t1) 367 ├── columns: a:9!null b:10!null 368 ├── project 369 │ ├── columns: x.a:1 x.b:2 370 │ └── scan x 371 │ └── columns: x.a:1 x.b:2 rowid:3!null 372 └── with &2 (t2) 373 ├── columns: a:9!null b:10!null 374 ├── project 375 │ ├── columns: x.a:4!null x.b:5!null 376 │ └── inner-join (hash) 377 │ ├── columns: x.a:4!null x.b:5!null rowid:6!null a:7!null b:8!null 378 │ ├── scan x 379 │ │ └── columns: x.a:4 x.b:5 rowid:6!null 380 │ ├── with-scan &1 (t1) 381 │ │ ├── columns: a:7 b:8 382 │ │ └── mapping: 383 │ │ ├── x.a:1 => a:7 384 │ │ └── x.b:2 => b:8 385 │ └── filters 386 │ ├── x.a:4 = a:7 387 │ └── x.b:5 = b:8 388 └── project 389 ├── columns: a:9!null b:10!null 390 └── inner-join (hash) 391 ├── columns: a:9!null b:10!null x.a:11!null x.b:12!null rowid:13!null 392 ├── with-scan &2 (t2) 393 │ ├── columns: a:9!null b:10!null 394 │ └── mapping: 395 │ ├── x.a:4 => a:9 396 │ └── x.b:5 => b:10 397 ├── scan x 398 │ └── columns: x.a:11 x.b:12 rowid:13!null 399 └── filters 400 ├── a:9 = x.a:11 401 └── b:10 = x.b:12 402 403 build 404 WITH t AS (SELECT a FROM y WHERE a < 3) 405 SELECT * FROM t NATURAL JOIN t 406 ---- 407 error (42712): source name "t" specified more than once (missing AS clause) 408 409 build 410 WITH t(x) AS (SELECT a FROM x) 411 SELECT x FROM (SELECT x FROM t) 412 ---- 413 with &1 (t) 414 ├── columns: x:4 415 ├── project 416 │ ├── columns: a:1 417 │ └── scan x 418 │ └── columns: a:1 b:2 rowid:3!null 419 └── with-scan &1 (t) 420 ├── columns: x:4 421 └── mapping: 422 └── a:1 => x:4 423 424 build 425 WITH t(a, b) AS (SELECT true a, false b) 426 SELECT a, b FROM t 427 ---- 428 with &1 (t) 429 ├── columns: a:3!null b:4!null 430 ├── project 431 │ ├── columns: a:1!null b:2!null 432 │ ├── values 433 │ │ └── () 434 │ └── projections 435 │ ├── true [as=a:1] 436 │ └── false [as=b:2] 437 └── with-scan &1 (t) 438 ├── columns: a:3!null b:4!null 439 └── mapping: 440 ├── a:1 => a:3 441 └── b:2 => b:4 442 443 build 444 WITH t(b, a) AS (SELECT true a, false b) 445 SELECT a, b FROM t 446 ---- 447 with &1 (t) 448 ├── columns: a:4!null b:3!null 449 ├── project 450 │ ├── columns: a:1!null b:2!null 451 │ ├── values 452 │ │ └── () 453 │ └── projections 454 │ ├── true [as=a:1] 455 │ └── false [as=b:2] 456 └── with-scan &1 (t) 457 ├── columns: b:3!null a:4!null 458 └── mapping: 459 ├── a:1 => b:3 460 └── b:2 => a:4 461 462 build 463 WITH t AS (SELECT a FROM x) 464 SELECT * FROM y WHERE a IN (SELECT * FROM t) 465 ---- 466 with &1 (t) 467 ├── columns: a:4 468 ├── project 469 │ ├── columns: x.a:1 470 │ └── scan x 471 │ └── columns: x.a:1 b:2 x.rowid:3!null 472 └── project 473 ├── columns: y.a:4 474 └── select 475 ├── columns: y.a:4 y.rowid:5!null 476 ├── scan y 477 │ └── columns: y.a:4 y.rowid:5!null 478 └── filters 479 └── any: eq 480 ├── with-scan &1 (t) 481 │ ├── columns: a:6 482 │ └── mapping: 483 │ └── x.a:1 => a:6 484 └── y.a:4 485 486 build 487 WITH t(x) AS (SELECT a FROM x) 488 SELECT * FROM y WHERE a IN (SELECT x FROM t) 489 ---- 490 with &1 (t) 491 ├── columns: a:4 492 ├── project 493 │ ├── columns: x.a:1 494 │ └── scan x 495 │ └── columns: x.a:1 b:2 x.rowid:3!null 496 └── project 497 ├── columns: y.a:4 498 └── select 499 ├── columns: y.a:4 y.rowid:5!null 500 ├── scan y 501 │ └── columns: y.a:4 y.rowid:5!null 502 └── filters 503 └── any: eq 504 ├── with-scan &1 (t) 505 │ ├── columns: x:6 506 │ └── mapping: 507 │ └── x.a:1 => x:6 508 └── y.a:4 509 510 # Using a subquery inside a CTE 511 build 512 SELECT * FROM x WHERE a IN 513 (WITH t AS (SELECT * FROM y WHERE a < 3) SELECT * FROM t) 514 ---- 515 with &1 (t) 516 ├── columns: a:1 b:2 517 ├── project 518 │ ├── columns: y.a:4!null 519 │ └── select 520 │ ├── columns: y.a:4!null y.rowid:5!null 521 │ ├── scan y 522 │ │ └── columns: y.a:4 y.rowid:5!null 523 │ └── filters 524 │ └── y.a:4 < 3 525 └── project 526 ├── columns: x.a:1 b:2 527 └── select 528 ├── columns: x.a:1 b:2 x.rowid:3!null 529 ├── scan x 530 │ └── columns: x.a:1 b:2 x.rowid:3!null 531 └── filters 532 └── any: eq 533 ├── with-scan &1 (t) 534 │ ├── columns: a:6!null 535 │ └── mapping: 536 │ └── y.a:4 => a:6 537 └── x.a:1 538 539 # Using a correlated subquery inside a CTE 540 build 541 SELECT (WITH t AS (SELECT * FROM y WHERE x.a = y.a) SELECT * FROM t LIMIT 1) FROM x 542 ---- 543 error (0A000): CTEs may not be correlated 544 545 # Rename columns 546 build 547 WITH t(b) AS (SELECT a FROM x) SELECT b, t.b FROM t 548 ---- 549 with &1 (t) 550 ├── columns: b:4 b:4 551 ├── project 552 │ ├── columns: a:1 553 │ └── scan x 554 │ └── columns: a:1 x.b:2 rowid:3!null 555 └── with-scan &1 (t) 556 ├── columns: b:4 557 └── mapping: 558 └── a:1 => b:4 559 560 build 561 WITH t(b, c) AS (SELECT a FROM x) SELECT b, t.b FROM t 562 ---- 563 error (42P10): source "t" has 1 columns available but 2 columns specified 564 565 # Ensure you can't reference the original table name 566 build 567 WITH t AS (SELECT a FROM x) SELECT a, x.t FROM t 568 ---- 569 error (42P01): no data source matches prefix: x 570 571 # Nested WITH, name shadowing 572 build 573 WITH t(x) AS (WITH t(x) AS (SELECT 1) SELECT x * 10 FROM t) SELECT x + 2 FROM t 574 ---- 575 with &1 (t) 576 ├── columns: "?column?":5!null 577 ├── project 578 │ ├── columns: "?column?":1!null 579 │ ├── values 580 │ │ └── () 581 │ └── projections 582 │ └── 1 [as="?column?":1] 583 └── with &2 (t) 584 ├── columns: "?column?":5!null 585 ├── project 586 │ ├── columns: "?column?":3!null 587 │ ├── with-scan &1 (t) 588 │ │ ├── columns: x:2!null 589 │ │ └── mapping: 590 │ │ └── "?column?":1 => x:2 591 │ └── projections 592 │ └── x:2 * 10 [as="?column?":3] 593 └── project 594 ├── columns: "?column?":5!null 595 ├── with-scan &2 (t) 596 │ ├── columns: x:4!null 597 │ └── mapping: 598 │ └── "?column?":3 => x:4 599 └── projections 600 └── x:4 + 2 [as="?column?":5] 601 602 build 603 WITH one AS (SELECT a AS u FROM x), 604 two AS (SELECT a AS v FROM (SELECT a FROM y UNION ALL SELECT u FROM one)) 605 SELECT * FROM one JOIN two ON u = v 606 ---- 607 with &1 (one) 608 ├── columns: u:8!null v:9!null 609 ├── project 610 │ ├── columns: x.a:1 611 │ └── scan x 612 │ └── columns: x.a:1 b:2 x.rowid:3!null 613 └── with &2 (two) 614 ├── columns: u:8!null v:9!null 615 ├── union-all 616 │ ├── columns: a:7 617 │ ├── left columns: y.a:4 618 │ ├── right columns: u:6 619 │ ├── project 620 │ │ ├── columns: y.a:4 621 │ │ └── scan y 622 │ │ └── columns: y.a:4 y.rowid:5!null 623 │ └── with-scan &1 (one) 624 │ ├── columns: u:6 625 │ └── mapping: 626 │ └── x.a:1 => u:6 627 └── inner-join (hash) 628 ├── columns: u:8!null v:9!null 629 ├── with-scan &1 (one) 630 │ ├── columns: u:8 631 │ └── mapping: 632 │ └── x.a:1 => u:8 633 ├── with-scan &2 (two) 634 │ ├── columns: v:9 635 │ └── mapping: 636 │ └── a:7 => v:9 637 └── filters 638 └── u:8 = v:9 639 640 build 641 WITH foo AS (SELECT x.a FROM x ORDER by x.a) SELECT * FROM foo 642 ---- 643 with &1 (foo) 644 ├── columns: a:4 645 ├── project 646 │ ├── columns: x.a:1 647 │ └── scan x 648 │ └── columns: x.a:1 b:2 rowid:3!null 649 └── with-scan &1 (foo) 650 ├── columns: a:4 651 └── mapping: 652 └── x.a:1 => a:4 653 654 # Mutations. 655 build 656 WITH t AS (SELECT a FROM x) INSERT INTO x SELECT a + 20 FROM t RETURNING * 657 ---- 658 with &1 (t) 659 ├── columns: a:4 b:5 660 ├── project 661 │ ├── columns: x.a:1 662 │ └── scan x 663 │ └── columns: x.a:1 b:2 rowid:3!null 664 └── project 665 ├── columns: x.a:4 b:5 666 └── insert x 667 ├── columns: x.a:4 b:5 rowid:6!null 668 ├── insert-mapping: 669 │ ├── "?column?":8 => x.a:4 670 │ ├── column9:9 => b:5 671 │ └── column10:10 => rowid:6 672 └── project 673 ├── columns: column9:9 column10:10 "?column?":8 674 ├── project 675 │ ├── columns: "?column?":8 676 │ ├── with-scan &1 (t) 677 │ │ ├── columns: a:7 678 │ │ └── mapping: 679 │ │ └── x.a:1 => a:7 680 │ └── projections 681 │ └── a:7 + 20 [as="?column?":8] 682 └── projections 683 ├── NULL::INT8 [as=column9:9] 684 └── unique_rowid() [as=column10:10] 685 686 build 687 WITH t AS (SELECT a FROM x) UPDATE x SET a = (SELECT * FROM t) RETURNING * 688 ---- 689 with &1 (t) 690 ├── columns: a:4 b:5 691 ├── project 692 │ ├── columns: x.a:1 693 │ └── scan x 694 │ └── columns: x.a:1 b:2 rowid:3!null 695 └── project 696 ├── columns: x.a:4 b:5 697 └── update x 698 ├── columns: x.a:4 b:5 rowid:6!null 699 ├── fetch columns: x.a:7 b:8 rowid:9 700 ├── update-mapping: 701 │ └── a_new:11 => x.a:4 702 └── project 703 ├── columns: a_new:11 x.a:7 b:8 rowid:9!null 704 ├── scan x 705 │ └── columns: x.a:7 b:8 rowid:9!null 706 └── projections 707 └── subquery [as=a_new:11] 708 └── max1-row 709 ├── columns: a:10 710 └── with-scan &1 (t) 711 ├── columns: a:10 712 └── mapping: 713 └── x.a:1 => a:10 714 715 build 716 WITH t AS (SELECT a FROM x) DELETE FROM x WHERE a = (SELECT * FROM t) RETURNING * 717 ---- 718 with &1 (t) 719 ├── columns: a:4!null b:5 720 ├── project 721 │ ├── columns: x.a:1 722 │ └── scan x 723 │ └── columns: x.a:1 b:2 rowid:3!null 724 └── project 725 ├── columns: x.a:4!null b:5 726 └── delete x 727 ├── columns: x.a:4!null b:5 rowid:6!null 728 ├── fetch columns: x.a:7 b:8 rowid:9 729 └── select 730 ├── columns: x.a:7!null b:8 rowid:9!null 731 ├── scan x 732 │ └── columns: x.a:7 b:8 rowid:9!null 733 └── filters 734 └── eq 735 ├── x.a:7 736 └── subquery 737 └── max1-row 738 ├── columns: a:10 739 └── with-scan &1 (t) 740 ├── columns: a:10 741 └── mapping: 742 └── x.a:1 => a:10 743 744 # Correlated WITH is not allowed. 745 746 build 747 SELECT (WITH foo AS (SELECT x.a FROM x WHERE x.a = y.a) SELECT a FROM foo) FROM y 748 ---- 749 error (0A000): CTEs may not be correlated 750 751 build 752 SELECT (WITH foo AS (SELECT (SELECT y.a) FROM x) SELECT a FROM foo) FROM y 753 ---- 754 error (0A000): CTEs may not be correlated 755 756 # Regression test for #40407. 757 exec-ddl 758 CREATE TABLE xy (x INT, y INT, z TIMESTAMP); 759 ---- 760 761 exec-ddl 762 CREATE TABLE uv (u INT, v INT); 763 ---- 764 765 build 766 WITH 767 t AS (SELECT xy.x FROM xy INNER JOIN uv ON xy.x = uv.u ORDER BY uv.v DESC LIMIT 5) 768 DELETE FROM 769 xy 770 WHERE 771 x = ANY (SELECT * FROM t); 772 ---- 773 with &1 (t) 774 ├── project 775 │ ├── columns: xy.x:1!null 776 │ └── limit 777 │ ├── columns: xy.x:1!null v:6 778 │ ├── internal-ordering: -6 779 │ ├── sort 780 │ │ ├── columns: xy.x:1!null v:6 781 │ │ ├── ordering: -6 782 │ │ ├── limit hint: 5.00 783 │ │ └── project 784 │ │ ├── columns: xy.x:1!null v:6 785 │ │ └── inner-join (hash) 786 │ │ ├── columns: xy.x:1!null y:2 z:3 xy.rowid:4!null u:5!null v:6 uv.rowid:7!null 787 │ │ ├── scan xy 788 │ │ │ └── columns: xy.x:1 y:2 z:3 xy.rowid:4!null 789 │ │ ├── scan uv 790 │ │ │ └── columns: u:5 v:6 uv.rowid:7!null 791 │ │ └── filters 792 │ │ └── xy.x:1 = u:5 793 │ └── 5 794 └── delete xy 795 ├── columns: <none> 796 ├── fetch columns: xy.x:12 y:13 z:14 xy.rowid:15 797 └── select 798 ├── columns: xy.x:12 y:13 z:14 xy.rowid:15!null 799 ├── scan xy 800 │ └── columns: xy.x:12 y:13 z:14 xy.rowid:15!null 801 └── filters 802 └── any: eq 803 ├── with-scan &1 (t) 804 │ ├── columns: x:16!null 805 │ └── mapping: 806 │ └── xy.x:1 => x:16 807 └── xy.x:12 808 809 # Check hidden column handling: level, node_type should not be output. 810 build 811 WITH cte AS (EXPLAIN (VERBOSE) SELECT 1) SELECT * FROM cte 812 ---- 813 with &1 (cte) 814 ├── columns: tree:9 field:10 description:11 columns:12 ordering:13 815 ├── project 816 │ ├── columns: tree:2 field:5 description:6 columns:7 ordering:8 817 │ └── explain 818 │ ├── columns: tree:2 level:3 node_type:4 field:5 description:6 columns:7 ordering:8 819 │ ├── mode: verbose 820 │ └── project 821 │ ├── columns: "?column?":1!null 822 │ ├── values 823 │ │ └── () 824 │ └── projections 825 │ └── 1 [as="?column?":1] 826 └── with-scan &1 (cte) 827 ├── columns: tree:9 field:10 description:11 columns:12 ordering:13 828 └── mapping: 829 ├── tree:2 => tree:9 830 ├── field:5 => field:10 831 ├── description:6 => description:11 832 ├── columns:7 => columns:12 833 └── ordering:8 => ordering:13 834 835 # WITH RECURSIVE examples from postgres docs. 836 837 build 838 WITH RECURSIVE t(n) AS ( 839 VALUES (1) 840 UNION ALL 841 SELECT n+1 FROM t WHERE n < 100 842 ) 843 SELECT sum(n) FROM t 844 ---- 845 with &2 (t) 846 ├── columns: sum:6 847 ├── recursive-c-t-e 848 │ ├── columns: n:2 849 │ ├── working table binding: &1 850 │ ├── initial columns: column1:1 851 │ ├── recursive columns: "?column?":4 852 │ ├── values 853 │ │ ├── columns: column1:1!null 854 │ │ └── (1,) 855 │ └── project 856 │ ├── columns: "?column?":4!null 857 │ ├── select 858 │ │ ├── columns: n:3!null 859 │ │ ├── with-scan &1 (t) 860 │ │ │ ├── columns: n:3 861 │ │ │ └── mapping: 862 │ │ │ └── n:2 => n:3 863 │ │ └── filters 864 │ │ └── n:3 < 100 865 │ └── projections 866 │ └── n:3 + 1 [as="?column?":4] 867 └── scalar-group-by 868 ├── columns: sum:6 869 ├── with-scan &2 (t) 870 │ ├── columns: n:5 871 │ └── mapping: 872 │ └── n:2 => n:5 873 └── aggregations 874 └── sum [as=sum:6] 875 └── n:5 876 877 exec-ddl 878 CREATE TABLE parts (part STRING, sub_part STRING, quantity INT) 879 ---- 880 881 build 882 WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( 883 SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' 884 UNION ALL 885 SELECT p.sub_part, p.part, p.quantity 886 FROM included_parts AS pr, parts AS p 887 WHERE p.part = pr.sub_part 888 ) 889 SELECT sub_part, sum(quantity) as total_quantity 890 FROM included_parts 891 GROUP BY sub_part 892 ---- 893 with &2 (included_parts) 894 ├── columns: sub_part:15 total_quantity:18 895 ├── recursive-c-t-e 896 │ ├── columns: sub_part:5 part:6 quantity:7 897 │ ├── working table binding: &1 898 │ ├── initial columns: parts.sub_part:2 parts.part:1 parts.quantity:3 899 │ ├── recursive columns: p.sub_part:12 p.part:11 p.quantity:13 900 │ ├── project 901 │ │ ├── columns: parts.part:1!null parts.sub_part:2 parts.quantity:3 902 │ │ └── select 903 │ │ ├── columns: parts.part:1!null parts.sub_part:2 parts.quantity:3 parts.rowid:4!null 904 │ │ ├── scan parts 905 │ │ │ └── columns: parts.part:1 parts.sub_part:2 parts.quantity:3 parts.rowid:4!null 906 │ │ └── filters 907 │ │ └── parts.part:1 = 'our_product' 908 │ └── project 909 │ ├── columns: p.part:11!null p.sub_part:12 p.quantity:13 910 │ └── select 911 │ ├── columns: sub_part:8!null part:9 quantity:10 p.part:11!null p.sub_part:12 p.quantity:13 p.rowid:14!null 912 │ ├── inner-join (cross) 913 │ │ ├── columns: sub_part:8 part:9 quantity:10 p.part:11 p.sub_part:12 p.quantity:13 p.rowid:14!null 914 │ │ ├── with-scan &1 (included_parts) 915 │ │ │ ├── columns: sub_part:8 part:9 quantity:10 916 │ │ │ └── mapping: 917 │ │ │ ├── sub_part:5 => sub_part:8 918 │ │ │ ├── part:6 => part:9 919 │ │ │ └── quantity:7 => quantity:10 920 │ │ ├── scan p 921 │ │ │ └── columns: p.part:11 p.sub_part:12 p.quantity:13 p.rowid:14!null 922 │ │ └── filters (true) 923 │ └── filters 924 │ └── p.part:11 = sub_part:8 925 └── group-by 926 ├── columns: sub_part:15 sum:18 927 ├── grouping columns: sub_part:15 928 ├── project 929 │ ├── columns: sub_part:15 quantity:17 930 │ └── with-scan &2 (included_parts) 931 │ ├── columns: sub_part:15 part:16 quantity:17 932 │ └── mapping: 933 │ ├── sub_part:5 => sub_part:15 934 │ ├── part:6 => part:16 935 │ └── quantity:7 => quantity:17 936 └── aggregations 937 └── sum [as=sum:18] 938 └── quantity:17 939 940 941 exec-ddl 942 CREATE TABLE graph (id INT PRIMARY KEY, link INT, data STRING) 943 ---- 944 945 build 946 WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( 947 SELECT g.id, g.link, g.data, 1, 948 ARRAY[g.id], 949 false 950 FROM graph g 951 UNION ALL 952 SELECT g.id, g.link, g.data, sg.depth + 1, 953 path || g.id, 954 g.id = ANY(path) 955 FROM graph g, search_graph sg 956 WHERE g.id = sg.link AND NOT cycle 957 ) 958 SELECT * FROM search_graph 959 ---- 960 with &2 (search_graph) 961 ├── columns: id:25 link:26 data:27 depth:28 path:29 cycle:30 962 ├── recursive-c-t-e 963 │ ├── columns: id:7 link:8 data:9 depth:10 path:11 cycle:12 964 │ ├── working table binding: &1 965 │ ├── initial columns: g.id:1 g.link:2 g.data:3 "?column?":4 array:5 bool:6 966 │ ├── recursive columns: g.id:13 g.link:14 g.data:15 "?column?":22 "?column?":23 "?column?":24 967 │ ├── project 968 │ │ ├── columns: "?column?":4!null array:5!null bool:6!null g.id:1!null g.link:2 g.data:3 969 │ │ ├── scan g 970 │ │ │ └── columns: g.id:1!null g.link:2 g.data:3 971 │ │ └── projections 972 │ │ ├── 1 [as="?column?":4] 973 │ │ ├── ARRAY[g.id:1] [as=array:5] 974 │ │ └── false [as=bool:6] 975 │ └── project 976 │ ├── columns: "?column?":22 "?column?":23 "?column?":24 g.id:13!null g.link:14 g.data:15 977 │ ├── select 978 │ │ ├── columns: g.id:13!null g.link:14 g.data:15 id:16 link:17!null data:18 depth:19 path:20 cycle:21!null 979 │ │ ├── inner-join (cross) 980 │ │ │ ├── columns: g.id:13!null g.link:14 g.data:15 id:16 link:17 data:18 depth:19 path:20 cycle:21 981 │ │ │ ├── scan g 982 │ │ │ │ └── columns: g.id:13!null g.link:14 g.data:15 983 │ │ │ ├── with-scan &1 (search_graph) 984 │ │ │ │ ├── columns: id:16 link:17 data:18 depth:19 path:20 cycle:21 985 │ │ │ │ └── mapping: 986 │ │ │ │ ├── id:7 => id:16 987 │ │ │ │ ├── link:8 => link:17 988 │ │ │ │ ├── data:9 => data:18 989 │ │ │ │ ├── depth:10 => depth:19 990 │ │ │ │ ├── path:11 => path:20 991 │ │ │ │ └── cycle:12 => cycle:21 992 │ │ │ └── filters (true) 993 │ │ └── filters 994 │ │ └── (g.id:13 = link:17) AND (NOT cycle:21) 995 │ └── projections 996 │ ├── depth:19 + 1 [as="?column?":22] 997 │ ├── path:20 || g.id:13 [as="?column?":23] 998 │ └── g.id:13 = ANY path:20 [as="?column?":24] 999 └── with-scan &2 (search_graph) 1000 ├── columns: id:25 link:26 data:27 depth:28 path:29 cycle:30 1001 └── mapping: 1002 ├── id:7 => id:25 1003 ├── link:8 => link:26 1004 ├── data:9 => data:27 1005 ├── depth:10 => depth:28 1006 ├── path:11 => path:29 1007 └── cycle:12 => cycle:30 1008 1009 # Test where initial query has duplicate columns. 1010 build 1011 WITH RECURSIVE cte(a, b) AS ( 1012 SELECT 0, 0 1013 UNION ALL 1014 SELECT a+1, b+10 FROM cte WHERE a < 5 1015 ) SELECT * FROM cte; 1016 ---- 1017 with &2 (cte) 1018 ├── columns: a:8 b:9 1019 ├── recursive-c-t-e 1020 │ ├── columns: a:2 b:3 1021 │ ├── working table binding: &1 1022 │ ├── initial columns: "?column?":1 "?column?":1 1023 │ ├── recursive columns: "?column?":6 "?column?":7 1024 │ ├── project 1025 │ │ ├── columns: "?column?":1!null 1026 │ │ ├── values 1027 │ │ │ └── () 1028 │ │ └── projections 1029 │ │ └── 0 [as="?column?":1] 1030 │ └── project 1031 │ ├── columns: "?column?":6!null "?column?":7 1032 │ ├── select 1033 │ │ ├── columns: a:4!null b:5 1034 │ │ ├── with-scan &1 (cte) 1035 │ │ │ ├── columns: a:4 b:5 1036 │ │ │ └── mapping: 1037 │ │ │ ├── a:2 => a:4 1038 │ │ │ └── b:3 => b:5 1039 │ │ └── filters 1040 │ │ └── a:4 < 5 1041 │ └── projections 1042 │ ├── a:4 + 1 [as="?column?":6] 1043 │ └── b:5 + 10 [as="?column?":7] 1044 └── with-scan &2 (cte) 1045 ├── columns: a:8 b:9 1046 └── mapping: 1047 ├── a:2 => a:8 1048 └── b:3 => b:9 1049 1050 # Test where recursive query has duplicate columns. 1051 build 1052 WITH RECURSIVE cte(a, b) AS ( 1053 SELECT 0, 1 1054 UNION ALL 1055 SELECT a+1, a+1 FROM cte WHERE a < 5 1056 ) SELECT * FROM cte; 1057 ---- 1058 with &2 (cte) 1059 ├── columns: a:8 b:9 1060 ├── recursive-c-t-e 1061 │ ├── columns: a:3 b:4 1062 │ ├── working table binding: &1 1063 │ ├── initial columns: "?column?":1 "?column?":2 1064 │ ├── recursive columns: "?column?":7 "?column?":7 1065 │ ├── project 1066 │ │ ├── columns: "?column?":1!null "?column?":2!null 1067 │ │ ├── values 1068 │ │ │ └── () 1069 │ │ └── projections 1070 │ │ ├── 0 [as="?column?":1] 1071 │ │ └── 1 [as="?column?":2] 1072 │ └── project 1073 │ ├── columns: "?column?":7!null 1074 │ ├── select 1075 │ │ ├── columns: a:5!null b:6 1076 │ │ ├── with-scan &1 (cte) 1077 │ │ │ ├── columns: a:5 b:6 1078 │ │ │ └── mapping: 1079 │ │ │ ├── a:3 => a:5 1080 │ │ │ └── b:4 => b:6 1081 │ │ └── filters 1082 │ │ └── a:5 < 5 1083 │ └── projections 1084 │ └── a:5 + 1 [as="?column?":7] 1085 └── with-scan &2 (cte) 1086 ├── columns: a:8 b:9 1087 └── mapping: 1088 ├── a:3 => a:8 1089 └── b:4 => b:9 1090 1091 # Allow non-recursive CTE when RECURSIVE is used. 1092 build 1093 WITH RECURSIVE cte(a, b) AS ( 1094 SELECT 1, 2 1095 ) SELECT * FROM cte; 1096 ---- 1097 with &2 (cte) 1098 ├── columns: a:3!null b:4!null 1099 ├── project 1100 │ ├── columns: "?column?":1!null "?column?":2!null 1101 │ ├── values 1102 │ │ └── () 1103 │ └── projections 1104 │ ├── 1 [as="?column?":1] 1105 │ └── 2 [as="?column?":2] 1106 └── with-scan &2 (cte) 1107 ├── columns: a:3!null b:4!null 1108 └── mapping: 1109 ├── "?column?":1 => a:3 1110 └── "?column?":2 => b:4 1111 1112 # Allow non-recursive CTE even when it has UNION ALL. 1113 build 1114 WITH RECURSIVE cte(a, b) AS ( 1115 SELECT 1, 2 1116 UNION ALL 1117 SELECT 3, 4 1118 ) SELECT * FROM cte; 1119 ---- 1120 with &2 (cte) 1121 ├── columns: a:9!null b:10!null 1122 ├── union 1123 │ ├── columns: "?column?":7!null "?column?":8!null 1124 │ ├── left columns: "?column?":1 "?column?":2 1125 │ ├── right columns: "?column?":5 "?column?":6 1126 │ ├── project 1127 │ │ ├── columns: "?column?":1!null "?column?":2!null 1128 │ │ ├── values 1129 │ │ │ └── () 1130 │ │ └── projections 1131 │ │ ├── 1 [as="?column?":1] 1132 │ │ └── 2 [as="?column?":2] 1133 │ └── project 1134 │ ├── columns: "?column?":5!null "?column?":6!null 1135 │ ├── values 1136 │ │ └── () 1137 │ └── projections 1138 │ ├── 3 [as="?column?":5] 1139 │ └── 4 [as="?column?":6] 1140 └── with-scan &2 (cte) 1141 ├── columns: a:9!null b:10!null 1142 └── mapping: 1143 ├── "?column?":7 => a:9 1144 └── "?column?":8 => b:10 1145 1146 # Allow non-recursive CTE even when it has UNION. 1147 build 1148 WITH RECURSIVE cte(a, b) AS ( 1149 SELECT 1, 2 1150 UNION 1151 SELECT 3, 4 1152 ) SELECT * FROM cte; 1153 ---- 1154 with &2 (cte) 1155 ├── columns: a:7!null b:8!null 1156 ├── union 1157 │ ├── columns: "?column?":5!null "?column?":6!null 1158 │ ├── left columns: "?column?":1 "?column?":2 1159 │ ├── right columns: "?column?":3 "?column?":4 1160 │ ├── project 1161 │ │ ├── columns: "?column?":1!null "?column?":2!null 1162 │ │ ├── values 1163 │ │ │ └── () 1164 │ │ └── projections 1165 │ │ ├── 1 [as="?column?":1] 1166 │ │ └── 2 [as="?column?":2] 1167 │ └── project 1168 │ ├── columns: "?column?":3!null "?column?":4!null 1169 │ ├── values 1170 │ │ └── () 1171 │ └── projections 1172 │ ├── 3 [as="?column?":3] 1173 │ └── 4 [as="?column?":4] 1174 └── with-scan &2 (cte) 1175 ├── columns: a:7!null b:8!null 1176 └── mapping: 1177 ├── "?column?":5 => a:7 1178 └── "?column?":6 => b:8 1179 1180 # Error cases. 1181 build 1182 WITH RECURSIVE cte(a, b) AS ( 1183 SELECT 1+a, 1+b FROM cte 1184 ) SELECT * FROM cte; 1185 ---- 1186 error (42601): recursive query "cte" does not have the form non-recursive-term UNION ALL recursive-term 1187 1188 build 1189 WITH RECURSIVE cte(a, b) AS ( 1190 SELECT 1, 2 1191 UNION 1192 SELECT 1+a, 1+b FROM cte 1193 ) SELECT * FROM cte; 1194 ---- 1195 error (0A000): unimplemented: recursive query "cte" uses UNION which is not implemented (only UNION ALL is supported) 1196 1197 build 1198 WITH RECURSIVE cte(a, b) AS ( 1199 SELECT 1+a, 1+b FROM cte 1200 UNION ALL 1201 SELECT 3, 4 1202 ) SELECT * FROM cte; 1203 ---- 1204 error (42601): recursive reference to query "cte" must not appear within its non-recursive term 1205 1206 build 1207 WITH RECURSIVE cte(a, b) AS ( 1208 SELECT 1, 2 1209 UNION ALL 1210 SELECT c1.a+c2.a, c1.b+c2.b FROM cte AS c1, cte AS c2 1211 ) SELECT * FROM cte; 1212 ---- 1213 error (42601): recursive reference to query "cte" must not appear more than once 1214 1215 # If we really need to reference the working table multiple times, we can use 1216 # an inner WITH. 1217 build 1218 WITH RECURSIVE cte(a, b) AS ( 1219 SELECT 1, 2 1220 UNION ALL 1221 (WITH foo AS (SELECT * FROM cte) SELECT c1.a+c2.a, c1.b+c2.b FROM foo AS c1, foo AS c2) 1222 ) SELECT * FROM cte; 1223 ---- 1224 with &3 (cte) 1225 ├── columns: a:13 b:14 1226 ├── recursive-c-t-e 1227 │ ├── columns: a:3 b:4 1228 │ ├── working table binding: &1 1229 │ ├── initial columns: "?column?":1 "?column?":2 1230 │ ├── recursive columns: "?column?":11 "?column?":12 1231 │ ├── project 1232 │ │ ├── columns: "?column?":1!null "?column?":2!null 1233 │ │ ├── values 1234 │ │ │ └── () 1235 │ │ └── projections 1236 │ │ ├── 1 [as="?column?":1] 1237 │ │ └── 2 [as="?column?":2] 1238 │ └── with &2 (foo) 1239 │ ├── columns: "?column?":11 "?column?":12 1240 │ ├── with-scan &1 (cte) 1241 │ │ ├── columns: a:5 b:6 1242 │ │ └── mapping: 1243 │ │ ├── a:3 => a:5 1244 │ │ └── b:4 => b:6 1245 │ └── project 1246 │ ├── columns: "?column?":11 "?column?":12 1247 │ ├── inner-join (cross) 1248 │ │ ├── columns: a:7 b:8 a:9 b:10 1249 │ │ ├── with-scan &2 (foo) 1250 │ │ │ ├── columns: a:7 b:8 1251 │ │ │ └── mapping: 1252 │ │ │ ├── a:5 => a:7 1253 │ │ │ └── b:6 => b:8 1254 │ │ ├── with-scan &2 (foo) 1255 │ │ │ ├── columns: a:9 b:10 1256 │ │ │ └── mapping: 1257 │ │ │ ├── a:5 => a:9 1258 │ │ │ └── b:6 => b:10 1259 │ │ └── filters (true) 1260 │ └── projections 1261 │ ├── a:7 + a:9 [as="?column?":11] 1262 │ └── b:8 + b:10 [as="?column?":12] 1263 └── with-scan &3 (cte) 1264 ├── columns: a:13 b:14 1265 └── mapping: 1266 ├── a:3 => a:13 1267 └── b:4 => b:14 1268 1269 # Veryify use of WITH inside the initial statement. 1270 build 1271 WITH RECURSIVE cte(a) AS ( 1272 (WITH v(x) AS (VALUES (1), (2)) SELECT v.x + v1.x FROM v, v AS v1) 1273 UNION ALL 1274 (SELECT a*10 FROM cte WHERE a < 100) 1275 ) SELECT * FROM cte; 1276 ---- 1277 with &3 (cte) 1278 ├── columns: a:8 1279 ├── recursive-c-t-e 1280 │ ├── columns: a:5 1281 │ ├── working table binding: &1 1282 │ ├── initial columns: "?column?":4 1283 │ ├── recursive columns: "?column?":7 1284 │ ├── with &2 (v) 1285 │ │ ├── columns: "?column?":4!null 1286 │ │ ├── values 1287 │ │ │ ├── columns: column1:1!null 1288 │ │ │ ├── (1,) 1289 │ │ │ └── (2,) 1290 │ │ └── project 1291 │ │ ├── columns: "?column?":4!null 1292 │ │ ├── inner-join (cross) 1293 │ │ │ ├── columns: x:2!null x:3!null 1294 │ │ │ ├── with-scan &2 (v) 1295 │ │ │ │ ├── columns: x:2!null 1296 │ │ │ │ └── mapping: 1297 │ │ │ │ └── column1:1 => x:2 1298 │ │ │ ├── with-scan &2 (v) 1299 │ │ │ │ ├── columns: x:3!null 1300 │ │ │ │ └── mapping: 1301 │ │ │ │ └── column1:1 => x:3 1302 │ │ │ └── filters (true) 1303 │ │ └── projections 1304 │ │ └── x:2 + x:3 [as="?column?":4] 1305 │ └── project 1306 │ ├── columns: "?column?":7!null 1307 │ ├── select 1308 │ │ ├── columns: a:6!null 1309 │ │ ├── with-scan &1 (cte) 1310 │ │ │ ├── columns: a:6 1311 │ │ │ └── mapping: 1312 │ │ │ └── a:5 => a:6 1313 │ │ └── filters 1314 │ │ └── a:6 < 100 1315 │ └── projections 1316 │ └── a:6 * 10 [as="?column?":7] 1317 └── with-scan &3 (cte) 1318 ├── columns: a:8 1319 └── mapping: 1320 └── a:5 => a:8 1321 1322 # Mutating WITHs not allowed at non-root positions. 1323 build 1324 SELECT * FROM (WITH foo AS (INSERT INTO y VALUES (1) RETURNING *) SELECT * FROM foo) 1325 ---- 1326 error (0A000): WITH clause containing a data-modifying statement must be at the top level 1327 1328 build 1329 SELECT (WITH foo AS (INSERT INTO y VALUES (1) RETURNING *) SELECT * FROM foo) 1330 ---- 1331 error (0A000): WITH clause containing a data-modifying statement must be at the top level 1332 1333 build 1334 SELECT (WITH foo AS (UPDATE y SET a = 4 RETURNING *) SELECT * FROM foo) 1335 ---- 1336 error (0A000): WITH clause containing a data-modifying statement must be at the top level 1337 1338 # Certain contexts besides the literal root allow mutating CTEs, like 1339 # underneath an EXPLAIN. 1340 build 1341 EXPLAIN WITH foo AS (INSERT INTO y VALUES (1) RETURNING *) SELECT * FROM foo 1342 ---- 1343 explain 1344 ├── columns: tree:6 field:7 description:8 1345 └── with &1 (foo) 1346 ├── columns: a:5!null 1347 ├── project 1348 │ ├── columns: y.a:1!null 1349 │ └── insert y 1350 │ ├── columns: y.a:1!null rowid:2!null 1351 │ ├── insert-mapping: 1352 │ │ ├── column1:3 => y.a:1 1353 │ │ └── column4:4 => rowid:2 1354 │ └── project 1355 │ ├── columns: column4:4 column1:3!null 1356 │ ├── values 1357 │ │ ├── columns: column1:3!null 1358 │ │ └── (1,) 1359 │ └── projections 1360 │ └── unique_rowid() [as=column4:4] 1361 └── with-scan &1 (foo) 1362 ├── columns: a:5!null 1363 └── mapping: 1364 └── y.a:1 => a:5 1365 1366 exec-ddl 1367 CREATE TABLE a(x INT); 1368 ---- 1369 1370 build 1371 INSERT INTO a(x) 1372 (WITH b(z) AS (VALUES (1),(2),(3)) SELECT z+1 AS w FROM b) 1373 ---- 1374 with &1 (b) 1375 ├── values 1376 │ ├── columns: column1:3!null 1377 │ ├── (1,) 1378 │ ├── (2,) 1379 │ └── (3,) 1380 └── insert a 1381 ├── columns: <none> 1382 ├── insert-mapping: 1383 │ ├── w:5 => x:1 1384 │ └── column6:6 => rowid:2 1385 └── project 1386 ├── columns: column6:6 w:5!null 1387 ├── project 1388 │ ├── columns: w:5!null 1389 │ ├── with-scan &1 (b) 1390 │ │ ├── columns: z:4!null 1391 │ │ └── mapping: 1392 │ │ └── column1:3 => z:4 1393 │ └── projections 1394 │ └── z:4 + 1 [as=w:5] 1395 └── projections 1396 └── unique_rowid() [as=column6:6] 1397 1398 build 1399 SELECT 1400 * 1401 FROM 1402 (VALUES (1), (2)) AS v (x), 1403 LATERAL (SELECT * FROM (WITH foo AS (SELECT 1 + x) SELECT * FROM foo)) 1404 ---- 1405 error (0A000): CTEs may not be correlated 1406 1407 # Subquery as a whole is correlated, but the WITH is not. 1408 build 1409 SELECT (WITH foo as (VALUES (1)) SELECT x) FROM (VALUES (1)) AS v(x) 1410 ---- 1411 with &1 (foo) 1412 ├── columns: x:4 1413 ├── values 1414 │ ├── columns: column1:2!null 1415 │ └── (1,) 1416 └── project 1417 ├── columns: x:4 1418 ├── values 1419 │ ├── columns: column1:1!null 1420 │ └── (1,) 1421 └── projections 1422 └── subquery [as=x:4] 1423 └── max1-row 1424 ├── columns: x:3 1425 └── project 1426 ├── columns: x:3 1427 ├── values 1428 │ └── () 1429 └── projections 1430 └── column1:1 [as=x:3] 1431 1432 # Regression test for #43963. 1433 build 1434 WITH a AS (SELECT 1 AS testval) SELECT a.testval FROM a 1435 ---- 1436 with &1 (a) 1437 ├── columns: testval:2!null 1438 ├── project 1439 │ ├── columns: testval:1!null 1440 │ ├── values 1441 │ │ └── () 1442 │ └── projections 1443 │ └── 1 [as=testval:1] 1444 └── with-scan &1 (a) 1445 ├── columns: testval:2!null 1446 └── mapping: 1447 └── testval:1 => testval:2 1448 1449 build 1450 WITH t AS MATERIALIZED (SELECT a FROM y WHERE a < 3) 1451 SELECT * FROM x NATURAL JOIN t 1452 ---- 1453 with &1 (t) 1454 ├── columns: a:3!null b:4 1455 ├── materialized 1456 ├── project 1457 │ ├── columns: y.a:1!null 1458 │ └── select 1459 │ ├── columns: y.a:1!null y.rowid:2!null 1460 │ ├── scan y 1461 │ │ └── columns: y.a:1 y.rowid:2!null 1462 │ └── filters 1463 │ └── y.a:1 < 3 1464 └── project 1465 ├── columns: x.a:3!null b:4 1466 └── inner-join (hash) 1467 ├── columns: x.a:3!null b:4 x.rowid:5!null a:6!null 1468 ├── scan x 1469 │ └── columns: x.a:3 b:4 x.rowid:5!null 1470 ├── with-scan &1 (t) 1471 │ ├── columns: a:6!null 1472 │ └── mapping: 1473 │ └── y.a:1 => a:6 1474 └── filters 1475 └── x.a:3 = a:6 1476 1477 build 1478 WITH t AS NOT MATERIALIZED (SELECT a FROM y WHERE a < 3) 1479 SELECT * FROM x NATURAL JOIN t 1480 ---- 1481 with &1 (t) 1482 ├── columns: a:3!null b:4 1483 ├── not-materialized 1484 ├── project 1485 │ ├── columns: y.a:1!null 1486 │ └── select 1487 │ ├── columns: y.a:1!null y.rowid:2!null 1488 │ ├── scan y 1489 │ │ └── columns: y.a:1 y.rowid:2!null 1490 │ └── filters 1491 │ └── y.a:1 < 3 1492 └── project 1493 ├── columns: x.a:3!null b:4 1494 └── inner-join (hash) 1495 ├── columns: x.a:3!null b:4 x.rowid:5!null a:6!null 1496 ├── scan x 1497 │ └── columns: x.a:3 b:4 x.rowid:5!null 1498 ├── with-scan &1 (t) 1499 │ ├── columns: a:6!null 1500 │ └── mapping: 1501 │ └── y.a:1 => a:6 1502 └── filters 1503 └── x.a:3 = a:6