github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/subquery (about) 1 # tests adapted from logictest -- subquery 2 3 # Tests for subqueries (SELECT statements which are part of a bigger statement). 4 5 build 6 SELECT (SELECT 1 a) AS r 7 ---- 8 project 9 ├── columns: r:2 10 ├── values 11 │ └── () 12 └── projections 13 └── subquery [as=r:2] 14 └── max1-row 15 ├── columns: a:1!null 16 └── project 17 ├── columns: a:1!null 18 ├── values 19 │ └── () 20 └── projections 21 └── 1 [as=a:1] 22 23 build 24 SELECT 1 IN (SELECT 1 a) AS r 25 ---- 26 project 27 ├── columns: r:2 28 ├── values 29 │ └── () 30 └── projections 31 └── any: eq [as=r:2] 32 ├── project 33 │ ├── columns: a:1!null 34 │ ├── values 35 │ │ └── () 36 │ └── projections 37 │ └── 1 [as=a:1] 38 └── 1 39 40 build 41 SELECT 1 IN ((((SELECT 1 a)))) AS r 42 ---- 43 project 44 ├── columns: r:2 45 ├── values 46 │ └── () 47 └── projections 48 └── any: eq [as=r:2] 49 ├── project 50 │ ├── columns: a:1!null 51 │ ├── values 52 │ │ └── () 53 │ └── projections 54 │ └── 1 [as=a:1] 55 └── 1 56 57 build 58 SELECT ARRAY(((((VALUES (1), (2))))))[2] 59 ---- 60 project 61 ├── columns: array:2 62 ├── values 63 │ └── () 64 └── projections 65 └── indirection [as=array:2] 66 ├── array-flatten 67 │ └── values 68 │ ├── columns: column1:1!null 69 │ ├── (1,) 70 │ └── (2,) 71 └── 2 72 73 build 74 SELECT 1 + (SELECT 1 a) AS r 75 ---- 76 project 77 ├── columns: r:2 78 ├── values 79 │ └── () 80 └── projections 81 └── plus [as=r:2] 82 ├── 1 83 └── subquery 84 └── max1-row 85 ├── columns: a:1!null 86 └── project 87 ├── columns: a:1!null 88 ├── values 89 │ └── () 90 └── projections 91 └── 1 [as=a:1] 92 93 build 94 SELECT 1 + (SELECT 1 AS a, 2 AS b) AS r 95 ---- 96 error (22023): unsupported binary operator: <int> + <tuple{int AS a, int AS b}> 97 98 build 99 SELECT (1, 2, 3) IN (SELECT 1 AS a, 2 AS b, 3 AS c) AS r 100 ---- 101 project 102 ├── columns: r:5 103 ├── values 104 │ └── () 105 └── projections 106 └── any: eq [as=r:5] 107 ├── project 108 │ ├── columns: column4:4!null 109 │ ├── project 110 │ │ ├── columns: a:1!null b:2!null c:3!null 111 │ │ ├── values 112 │ │ │ └── () 113 │ │ └── projections 114 │ │ ├── 1 [as=a:1] 115 │ │ ├── 2 [as=b:2] 116 │ │ └── 3 [as=c:3] 117 │ └── projections 118 │ └── (a:1, b:2, c:3) [as=column4:4] 119 └── (1, 2, 3) 120 121 build 122 SELECT (1, 2, 3) = (SELECT 1 AS a, 2 AS b, 3 AS c) AS r 123 ---- 124 project 125 ├── columns: r:5 126 ├── values 127 │ └── () 128 └── projections 129 └── eq [as=r:5] 130 ├── (1, 2, 3) 131 └── subquery 132 └── max1-row 133 ├── columns: column4:4!null 134 └── project 135 ├── columns: column4:4!null 136 ├── project 137 │ ├── columns: a:1!null b:2!null c:3!null 138 │ ├── values 139 │ │ └── () 140 │ └── projections 141 │ ├── 1 [as=a:1] 142 │ ├── 2 [as=b:2] 143 │ └── 3 [as=c:3] 144 └── projections 145 └── (a:1, b:2, c:3) [as=column4:4] 146 147 build 148 SELECT (1, 2, 3) != (SELECT 1 AS a, 2 AS b, 3 AS c) AS r 149 ---- 150 project 151 ├── columns: r:5 152 ├── values 153 │ └── () 154 └── projections 155 └── ne [as=r:5] 156 ├── (1, 2, 3) 157 └── subquery 158 └── max1-row 159 ├── columns: column4:4!null 160 └── project 161 ├── columns: column4:4!null 162 ├── project 163 │ ├── columns: a:1!null b:2!null c:3!null 164 │ ├── values 165 │ │ └── () 166 │ └── projections 167 │ ├── 1 [as=a:1] 168 │ ├── 2 [as=b:2] 169 │ └── 3 [as=c:3] 170 └── projections 171 └── (a:1, b:2, c:3) [as=column4:4] 172 173 build 174 SELECT (SELECT 1 AS x, 2 AS y, 3 AS z) = (SELECT 1 AS a, 2 AS b, 3 AS c) AS r 175 ---- 176 project 177 ├── columns: r:9 178 ├── values 179 │ └── () 180 └── projections 181 └── eq [as=r:9] 182 ├── subquery 183 │ └── max1-row 184 │ ├── columns: column7:7!null 185 │ └── project 186 │ ├── columns: column7:7!null 187 │ ├── project 188 │ │ ├── columns: x:1!null y:2!null z:3!null 189 │ │ ├── values 190 │ │ │ └── () 191 │ │ └── projections 192 │ │ ├── 1 [as=x:1] 193 │ │ ├── 2 [as=y:2] 194 │ │ └── 3 [as=z:3] 195 │ └── projections 196 │ └── (x:1, y:2, z:3) [as=column7:7] 197 └── subquery 198 └── max1-row 199 ├── columns: column8:8!null 200 └── project 201 ├── columns: column8:8!null 202 ├── project 203 │ ├── columns: a:4!null b:5!null c:6!null 204 │ ├── values 205 │ │ └── () 206 │ └── projections 207 │ ├── 1 [as=a:4] 208 │ ├── 2 [as=b:5] 209 │ └── 3 [as=c:6] 210 └── projections 211 └── (a:4, b:5, c:6) [as=column8:8] 212 213 build 214 SELECT (SELECT 1 x) IN (SELECT 1 y) AS z 215 ---- 216 project 217 ├── columns: z:3 218 ├── values 219 │ └── () 220 └── projections 221 └── any: eq [as=z:3] 222 ├── project 223 │ ├── columns: y:2!null 224 │ ├── values 225 │ │ └── () 226 │ └── projections 227 │ └── 1 [as=y:2] 228 └── subquery 229 └── max1-row 230 ├── columns: x:1!null 231 └── project 232 ├── columns: x:1!null 233 ├── values 234 │ └── () 235 └── projections 236 └── 1 [as=x:1] 237 238 build 239 SELECT (SELECT 1 a) IN (1) AS r 240 ---- 241 project 242 ├── columns: r:2 243 ├── values 244 │ └── () 245 └── projections 246 └── in [as=r:2] 247 ├── subquery 248 │ └── max1-row 249 │ ├── columns: a:1!null 250 │ └── project 251 │ ├── columns: a:1!null 252 │ ├── values 253 │ │ └── () 254 │ └── projections 255 │ └── 1 [as=a:1] 256 └── (1,) 257 258 # NB: Cockroach has different behavior from Postgres on a few esoteric 259 # subqueries. The Cockroach behavior seems more sensical and 260 # supporting the specific Postgres behavior appears onerous. Fingers 261 # crossed this doesn't bite us down the road. 262 263 # Postgres cannot handle this query (but MySQL can), even though it 264 # seems sensical: 265 # ERROR: subquery must return only one column 266 # LINE 1: select (select 1, 2) IN (select 1, 2); 267 # ^ 268 build 269 SELECT (SELECT 1 AS a, 2 AS b) IN (SELECT 1 AS c, 2 AS d) AS r 270 ---- 271 project 272 ├── columns: r:7 273 ├── values 274 │ └── () 275 └── projections 276 └── any: eq [as=r:7] 277 ├── project 278 │ ├── columns: column5:5!null 279 │ ├── project 280 │ │ ├── columns: c:3!null d:4!null 281 │ │ ├── values 282 │ │ │ └── () 283 │ │ └── projections 284 │ │ ├── 1 [as=c:3] 285 │ │ └── 2 [as=d:4] 286 │ └── projections 287 │ └── (c:3, d:4) [as=column5:5] 288 └── subquery 289 └── max1-row 290 ├── columns: column6:6!null 291 └── project 292 ├── columns: column6:6!null 293 ├── project 294 │ ├── columns: a:1!null b:2!null 295 │ ├── values 296 │ │ └── () 297 │ └── projections 298 │ ├── 1 [as=a:1] 299 │ └── 2 [as=b:2] 300 └── projections 301 └── (a:1, b:2) [as=column6:6] 302 303 # Postgres cannot handle this query, even though it seems sensical: 304 # ERROR: subquery must return only one column 305 # LINE 1: select (select 1, 2) IN ((1, 2)); 306 # ^ 307 build 308 SELECT (SELECT 1 AS a, 2 AS b) IN ((1, 2)) AS r 309 ---- 310 project 311 ├── columns: r:4 312 ├── values 313 │ └── () 314 └── projections 315 └── in [as=r:4] 316 ├── subquery 317 │ └── max1-row 318 │ ├── columns: column3:3!null 319 │ └── project 320 │ ├── columns: column3:3!null 321 │ ├── project 322 │ │ ├── columns: a:1!null b:2!null 323 │ │ ├── values 324 │ │ │ └── () 325 │ │ └── projections 326 │ │ ├── 1 [as=a:1] 327 │ │ └── 2 [as=b:2] 328 │ └── projections 329 │ └── (a:1, b:2) [as=column3:3] 330 └── ((1, 2),) 331 332 # Postgres cannot handle this query, even though it seems sensical: 333 # ERROR: subquery has too many columns 334 # LINE 1: select (select (1, 2)) IN (select 1, 2); 335 # ^ 336 build 337 SELECT (SELECT (1, 2) AS a) IN (SELECT 1 AS b, 2 AS c) AS r 338 ---- 339 project 340 ├── columns: r:5 341 ├── values 342 │ └── () 343 └── projections 344 └── any: eq [as=r:5] 345 ├── project 346 │ ├── columns: column4:4!null 347 │ ├── project 348 │ │ ├── columns: b:2!null c:3!null 349 │ │ ├── values 350 │ │ │ └── () 351 │ │ └── projections 352 │ │ ├── 1 [as=b:2] 353 │ │ └── 2 [as=c:3] 354 │ └── projections 355 │ └── (b:2, c:3) [as=column4:4] 356 └── subquery 357 └── max1-row 358 ├── columns: a:1!null 359 └── project 360 ├── columns: a:1!null 361 ├── values 362 │ └── () 363 └── projections 364 └── (1, 2) [as=a:1] 365 366 build 367 SELECT (SELECT (1, 2) a) IN ((1, 2)) AS r 368 ---- 369 project 370 ├── columns: r:2 371 ├── values 372 │ └── () 373 └── projections 374 └── in [as=r:2] 375 ├── subquery 376 │ └── max1-row 377 │ ├── columns: a:1!null 378 │ └── project 379 │ ├── columns: a:1!null 380 │ ├── values 381 │ │ └── () 382 │ └── projections 383 │ └── (1, 2) [as=a:1] 384 └── ((1, 2),) 385 386 # Postgres cannot handle this query, even though it seems sensical: 387 # ERROR: subquery must return only one column 388 # LINE 1: select (select 1, 2) in (select (1, 2)); 389 # ^ 390 build 391 SELECT (SELECT 1 AS a, 2 AS b) IN (SELECT (1, 2) AS c) AS r 392 ---- 393 project 394 ├── columns: r:5 395 ├── values 396 │ └── () 397 └── projections 398 └── any: eq [as=r:5] 399 ├── project 400 │ ├── columns: c:3!null 401 │ ├── values 402 │ │ └── () 403 │ └── projections 404 │ └── (1, 2) [as=c:3] 405 └── subquery 406 └── max1-row 407 ├── columns: column4:4!null 408 └── project 409 ├── columns: column4:4!null 410 ├── project 411 │ ├── columns: a:1!null b:2!null 412 │ ├── values 413 │ │ └── () 414 │ └── projections 415 │ ├── 1 [as=a:1] 416 │ └── 2 [as=b:2] 417 └── projections 418 └── (a:1, b:2) [as=column4:4] 419 420 build 421 SELECT (SELECT (1, 2) a) IN (SELECT (1, 2) b) AS r 422 ---- 423 project 424 ├── columns: r:3 425 ├── values 426 │ └── () 427 └── projections 428 └── any: eq [as=r:3] 429 ├── project 430 │ ├── columns: b:2!null 431 │ ├── values 432 │ │ └── () 433 │ └── projections 434 │ └── (1, 2) [as=b:2] 435 └── subquery 436 └── max1-row 437 ├── columns: a:1!null 438 └── project 439 ├── columns: a:1!null 440 ├── values 441 │ └── () 442 └── projections 443 └── (1, 2) [as=a:1] 444 445 build 446 SELECT 1 = ANY(SELECT 1 a) AS r 447 ---- 448 project 449 ├── columns: r:2 450 ├── values 451 │ └── () 452 └── projections 453 └── any: eq [as=r:2] 454 ├── project 455 │ ├── columns: a:1!null 456 │ ├── values 457 │ │ └── () 458 │ └── projections 459 │ └── 1 [as=a:1] 460 └── 1 461 462 build 463 SELECT (1, 2) = ANY(SELECT 1 AS a, 2 AS b) AS r 464 ---- 465 project 466 ├── columns: r:4 467 ├── values 468 │ └── () 469 └── projections 470 └── any: eq [as=r:4] 471 ├── project 472 │ ├── columns: column3:3!null 473 │ ├── project 474 │ │ ├── columns: a:1!null b:2!null 475 │ │ ├── values 476 │ │ │ └── () 477 │ │ └── projections 478 │ │ ├── 1 [as=a:1] 479 │ │ └── 2 [as=b:2] 480 │ └── projections 481 │ └── (a:1, b:2) [as=column3:3] 482 └── (1, 2) 483 484 build 485 SELECT 1 = SOME(SELECT 1 a) AS r 486 ---- 487 project 488 ├── columns: r:2 489 ├── values 490 │ └── () 491 └── projections 492 └── any: eq [as=r:2] 493 ├── project 494 │ ├── columns: a:1!null 495 │ ├── values 496 │ │ └── () 497 │ └── projections 498 │ └── 1 [as=a:1] 499 └── 1 500 501 build 502 SELECT (1, 2) = SOME(SELECT 1 AS a, 2 AS b) AS r 503 ---- 504 project 505 ├── columns: r:4 506 ├── values 507 │ └── () 508 └── projections 509 └── any: eq [as=r:4] 510 ├── project 511 │ ├── columns: column3:3!null 512 │ ├── project 513 │ │ ├── columns: a:1!null b:2!null 514 │ │ ├── values 515 │ │ │ └── () 516 │ │ └── projections 517 │ │ ├── 1 [as=a:1] 518 │ │ └── 2 [as=b:2] 519 │ └── projections 520 │ └── (a:1, b:2) [as=column3:3] 521 └── (1, 2) 522 523 build 524 SELECT 1 = ALL(SELECT 1 a) AS r 525 ---- 526 project 527 ├── columns: r:2 528 ├── values 529 │ └── () 530 └── projections 531 └── not [as=r:2] 532 └── any: ne 533 ├── project 534 │ ├── columns: a:1!null 535 │ ├── values 536 │ │ └── () 537 │ └── projections 538 │ └── 1 [as=a:1] 539 └── 1 540 541 build 542 SELECT (1, 2) = ALL(SELECT 1 AS a, 2 AS b) AS r 543 ---- 544 project 545 ├── columns: r:4 546 ├── values 547 │ └── () 548 └── projections 549 └── not [as=r:4] 550 └── any: ne 551 ├── project 552 │ ├── columns: column3:3!null 553 │ ├── project 554 │ │ ├── columns: a:1!null b:2!null 555 │ │ ├── values 556 │ │ │ └── () 557 │ │ └── projections 558 │ │ ├── 1 [as=a:1] 559 │ │ └── 2 [as=b:2] 560 │ └── projections 561 │ └── (a:1, b:2) [as=column3:3] 562 └── (1, 2) 563 564 build 565 SELECT (SELECT 1 AS a, 2 AS b) AS r 566 ---- 567 error (42601): subquery must return only one column, found 2 568 569 build 570 SELECT 1 IN (SELECT 1 AS a, 2 AS b) AS r 571 ---- 572 error (22023): unsupported comparison operator: <int> IN <tuple{tuple{int AS a, int AS b}}> 573 574 build 575 SELECT (1, 2) IN (SELECT 1 AS a) AS r 576 ---- 577 error (22023): unsupported comparison operator: <tuple{int, int}> IN <tuple{int}> 578 579 exec-ddl 580 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 581 ---- 582 583 build 584 SELECT (1, 2) IN (SELECT * FROM abc) AS r 585 ---- 586 error (22023): unsupported comparison operator: <tuple{int, int}> IN <tuple{tuple{int AS a, int AS b, int AS c}}> 587 588 build 589 SELECT (1, 2) IN (SELECT a, b FROM abc) AS r 590 ---- 591 project 592 ├── columns: r:5 593 ├── values 594 │ └── () 595 └── projections 596 └── any: eq [as=r:5] 597 ├── project 598 │ ├── columns: column4:4 599 │ ├── project 600 │ │ ├── columns: a:1!null b:2 601 │ │ └── scan abc 602 │ │ └── columns: a:1!null b:2 c:3 603 │ └── projections 604 │ └── (a:1, b:2) [as=column4:4] 605 └── (1, 2) 606 607 build 608 SELECT (1, 2) IN (SELECT a, b FROM abc WHERE false) AS r 609 ---- 610 project 611 ├── columns: r:5 612 ├── values 613 │ └── () 614 └── projections 615 └── any: eq [as=r:5] 616 ├── project 617 │ ├── columns: column4:4 618 │ ├── project 619 │ │ ├── columns: a:1!null b:2 620 │ │ └── select 621 │ │ ├── columns: a:1!null b:2 c:3 622 │ │ ├── scan abc 623 │ │ │ └── columns: a:1!null b:2 c:3 624 │ │ └── filters 625 │ │ └── false 626 │ └── projections 627 │ └── (a:1, b:2) [as=column4:4] 628 └── (1, 2) 629 630 build 631 SELECT (SELECT * FROM abc) 632 ---- 633 error (42601): subquery must return only one column, found 3 634 635 build 636 SELECT (SELECT a FROM abc) 637 ---- 638 project 639 ├── columns: a:4 640 ├── values 641 │ └── () 642 └── projections 643 └── subquery [as=a:4] 644 └── max1-row 645 ├── columns: abc.a:1!null 646 └── project 647 ├── columns: abc.a:1!null 648 └── scan abc 649 └── columns: abc.a:1!null b:2 c:3 650 651 build 652 SELECT EXISTS (SELECT a FROM abc) 653 ---- 654 project 655 ├── columns: exists:4 656 ├── values 657 │ └── () 658 └── projections 659 └── exists [as=exists:4] 660 └── project 661 ├── columns: a:1!null 662 └── scan abc 663 └── columns: a:1!null b:2 c:3 664 665 build 666 SELECT true = EXISTS (SELECT 1) 667 ---- 668 project 669 ├── columns: "?column?":2 670 ├── values 671 │ └── () 672 └── projections 673 └── eq [as="?column?":2] 674 ├── true 675 └── exists 676 └── project 677 ├── columns: "?column?":1!null 678 ├── values 679 │ └── () 680 └── projections 681 └── 1 [as="?column?":1] 682 683 build 684 SELECT (SELECT a FROM abc WHERE false) 685 ---- 686 project 687 ├── columns: a:4 688 ├── values 689 │ └── () 690 └── projections 691 └── subquery [as=a:4] 692 └── max1-row 693 ├── columns: abc.a:1!null 694 └── project 695 ├── columns: abc.a:1!null 696 └── select 697 ├── columns: abc.a:1!null b:2 c:3 698 ├── scan abc 699 │ └── columns: abc.a:1!null b:2 c:3 700 └── filters 701 └── false 702 703 exec-ddl 704 CREATE TABLE kv (k INT PRIMARY KEY, v INT) 705 ---- 706 707 build 708 SELECT (SELECT abc.k FROM abc) FROM kv AS abc 709 ---- 710 error (42703): column "abc.k" does not exist 711 712 build 713 VALUES (1, (SELECT (2) AS a)) 714 ---- 715 values 716 ├── columns: column1:2!null column2:3 717 └── tuple 718 ├── 1 719 └── subquery 720 └── max1-row 721 ├── columns: a:1!null 722 └── project 723 ├── columns: a:1!null 724 ├── values 725 │ └── () 726 └── projections 727 └── 2 [as=a:1] 728 729 build 730 SELECT * FROM abc WHERE a = 7 731 ---- 732 select 733 ├── columns: a:1!null b:2 c:3 734 ├── scan abc 735 │ └── columns: a:1!null b:2 c:3 736 └── filters 737 └── a:1 = 7 738 739 exec-ddl 740 CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT) 741 ---- 742 743 build 744 SELECT * FROM xyz 745 ---- 746 scan xyz 747 └── columns: x:1!null y:2 z:3 748 749 build 750 SELECT 1 IN (SELECT x FROM xyz ORDER BY x DESC) AS r 751 ---- 752 project 753 ├── columns: r:4 754 ├── values 755 │ └── () 756 └── projections 757 └── any: eq [as=r:4] 758 ├── project 759 │ ├── columns: x:1!null 760 │ └── scan xyz 761 │ └── columns: x:1!null y:2 z:3 762 └── 1 763 764 build 765 SELECT * FROM xyz WHERE x = (SELECT min(x) FROM xyz) 766 ---- 767 select 768 ├── columns: x:1!null y:2 z:3 769 ├── scan xyz 770 │ └── columns: x:1!null y:2 z:3 771 └── filters 772 └── eq 773 ├── x:1 774 └── subquery 775 └── max1-row 776 ├── columns: min:7 777 └── scalar-group-by 778 ├── columns: min:7 779 ├── project 780 │ ├── columns: x:4!null 781 │ └── scan xyz 782 │ └── columns: x:4!null y:5 z:6 783 └── aggregations 784 └── min [as=min:7] 785 └── x:4 786 787 build 788 SELECT * FROM xyz WHERE x = (SELECT max(x) FROM xyz) 789 ---- 790 select 791 ├── columns: x:1!null y:2 z:3 792 ├── scan xyz 793 │ └── columns: x:1!null y:2 z:3 794 └── filters 795 └── eq 796 ├── x:1 797 └── subquery 798 └── max1-row 799 ├── columns: max:7 800 └── scalar-group-by 801 ├── columns: max:7 802 ├── project 803 │ ├── columns: x:4!null 804 │ └── scan xyz 805 │ └── columns: x:4!null y:5 z:6 806 └── aggregations 807 └── max [as=max:7] 808 └── x:4 809 810 # Drop previous table with same name, but different schema. 811 exec-ddl 812 DROP TABLE kv 813 ---- 814 815 exec-ddl 816 CREATE TABLE kv (k INT PRIMARY KEY, v STRING) 817 ---- 818 819 build 820 SELECT * FROM kv WHERE k = (SELECT k FROM kv WHERE (k, v) = (1, 'one')) 821 ---- 822 select 823 ├── columns: k:1!null v:2 824 ├── scan kv 825 │ └── columns: k:1!null v:2 826 └── filters 827 └── eq 828 ├── k:1 829 └── subquery 830 └── max1-row 831 ├── columns: k:3!null 832 └── project 833 ├── columns: k:3!null 834 └── select 835 ├── columns: k:3!null v:4 836 ├── scan kv 837 │ └── columns: k:3!null v:4 838 └── filters 839 └── (k:3, v:4) = (1, 'one') 840 841 build 842 SELECT EXISTS(SELECT 1 r FROM kv AS x WHERE x.k = 1) 843 ---- 844 project 845 ├── columns: exists:4 846 ├── values 847 │ └── () 848 └── projections 849 └── exists [as=exists:4] 850 └── project 851 ├── columns: r:3!null 852 ├── select 853 │ ├── columns: k:1!null v:2 854 │ ├── scan x 855 │ │ └── columns: k:1!null v:2 856 │ └── filters 857 │ └── k:1 = 1 858 └── projections 859 └── 1 [as=r:3] 860 861 build 862 SELECT EXISTS(SELECT 1 r FROM kv WHERE k = 2) 863 ---- 864 project 865 ├── columns: exists:4 866 ├── values 867 │ └── () 868 └── projections 869 └── exists [as=exists:4] 870 └── project 871 ├── columns: r:3!null 872 ├── select 873 │ ├── columns: k:1!null v:2 874 │ ├── scan kv 875 │ │ └── columns: k:1!null v:2 876 │ └── filters 877 │ └── k:1 = 2 878 └── projections 879 └── 1 [as=r:3] 880 881 882 # Tests for subquery in the FROM part of a SELECT 883 884 build 885 SELECT * FROM (VALUES (1, 2)) AS foo 886 ---- 887 values 888 ├── columns: column1:1!null column2:2!null 889 └── (1, 2) 890 891 build 892 SELECT * FROM (VALUES (1, 2)) 893 ---- 894 values 895 ├── columns: column1:1!null column2:2!null 896 └── (1, 2) 897 898 build 899 SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo 900 ---- 901 values 902 ├── columns: column1:1!null column2:2!null 903 ├── (1, 'one') 904 ├── (2, 'two') 905 └── (3, 'three') 906 907 build 908 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo 909 ---- 910 values 911 ├── columns: column1:1!null column2:2!null column3:3!null 912 ├── (1, 2, 3) 913 └── (4, 5, 6) 914 915 build 916 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2, foo3) 917 ---- 918 values 919 ├── columns: foo1:1!null foo2:2!null foo3:3!null 920 ├── (1, 2, 3) 921 └── (4, 5, 6) 922 923 build 924 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2) 925 ---- 926 values 927 ├── columns: foo1:1!null foo2:2!null column3:3!null 928 ├── (1, 2, 3) 929 └── (4, 5, 6) 930 931 build 932 SELECT * FROM (SELECT * FROM xyz) AS foo WHERE x < 7 933 ---- 934 select 935 ├── columns: x:1!null y:2 z:3 936 ├── scan foo 937 │ └── columns: x:1!null y:2 z:3 938 └── filters 939 └── x:1 < 7 940 941 build 942 SELECT * FROM (SELECT * FROM xyz) AS foo (foo1) WHERE foo1 < 7 943 ---- 944 select 945 ├── columns: foo1:1!null y:2 z:3 946 ├── scan foo 947 │ └── columns: foo1:1!null y:2 z:3 948 └── filters 949 └── foo1:1 < 7 950 951 build 952 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3)) as foo (foo1) WHERE foo1 < 7 953 ---- 954 select 955 ├── columns: foo1:1!null moo2:2 moo3:3 956 ├── scan foo 957 │ └── columns: foo1:1!null moo2:2 moo3:3 958 └── filters 959 └── foo1:1 < 7 960 961 build 962 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7 963 ---- 964 select 965 ├── columns: foo1:1!null moo2:2 moo3:3 966 ├── scan foo 967 │ └── columns: foo1:1!null moo2:2 moo3:3 968 └── filters 969 └── foo1:1 < 7 970 971 build 972 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7 ORDER BY moo2 DESC 973 ---- 974 sort 975 ├── columns: foo1:1!null moo2:2 moo3:3 976 ├── ordering: -2 977 └── select 978 ├── columns: foo1:1!null moo2:2 moo3:3 979 ├── scan foo 980 │ └── columns: foo1:1!null moo2:2 moo3:3 981 └── filters 982 └── foo1:1 < 7 983 984 build 985 SELECT * FROM (SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS moo (moo1, moo2, moo3) WHERE moo1 = 4) as foo (foo1) 986 ---- 987 select 988 ├── columns: foo1:1!null moo2:2!null moo3:3!null 989 ├── values 990 │ ├── columns: column1:1!null column2:2!null column3:3!null 991 │ ├── (1, 2, 3) 992 │ └── (4, 5, 6) 993 └── filters 994 └── column1:1 = 4 995 996 build 997 SELECT * FROM (SELECT * FROM (VALUES (1, 8, 8), (3, 1, 1), (2, 4, 4)) AS moo (moo1, moo2, moo3) ORDER BY moo2) as foo (foo1) ORDER BY foo1 998 ---- 999 sort 1000 ├── columns: foo1:1!null moo2:2!null moo3:3!null 1001 ├── ordering: +1 1002 └── values 1003 ├── columns: column1:1!null column2:2!null column3:3!null 1004 ├── (1, 8, 8) 1005 ├── (3, 1, 1) 1006 └── (2, 4, 4) 1007 1008 build 1009 SELECT a, b FROM (VALUES (1, 2, 3), (3, 4, 7), (5, 6, 10)) AS foo (a, b, c) WHERE a + b = c 1010 ---- 1011 project 1012 ├── columns: a:1!null b:2!null 1013 └── select 1014 ├── columns: column1:1!null column2:2!null column3:3!null 1015 ├── values 1016 │ ├── columns: column1:1!null column2:2!null column3:3!null 1017 │ ├── (1, 2, 3) 1018 │ ├── (3, 4, 7) 1019 │ └── (5, 6, 10) 1020 └── filters 1021 └── (column1:1 + column2:2) = column3:3 1022 1023 build 1024 SELECT foo.a FROM (VALUES (1), (2), (3)) AS foo (a) 1025 ---- 1026 values 1027 ├── columns: a:1!null 1028 ├── (1,) 1029 ├── (2,) 1030 └── (3,) 1031 1032 build 1033 SELECT foo.a, a, column2, foo.column2 FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo (a) 1034 ---- 1035 values 1036 ├── columns: a:1!null a:1!null column2:2!null column2:2!null 1037 ├── (1, 'one') 1038 ├── (2, 'two') 1039 └── (3, 'three') 1040 1041 build 1042 SELECT x FROM xyz WHERE x IN (SELECT x FROM xyz WHERE x = 7) 1043 ---- 1044 project 1045 ├── columns: x:1!null 1046 └── select 1047 ├── columns: x:1!null y:2 z:3 1048 ├── scan xyz 1049 │ └── columns: x:1!null y:2 z:3 1050 └── filters 1051 └── any: eq 1052 ├── project 1053 │ ├── columns: x:4!null 1054 │ └── select 1055 │ ├── columns: x:4!null y:5 z:6 1056 │ ├── scan xyz 1057 │ │ └── columns: x:4!null y:5 z:6 1058 │ └── filters 1059 │ └── x:4 = 7 1060 └── x:1 1061 1062 build 1063 SELECT x FROM xyz WHERE x = 7 LIMIT (SELECT x FROM xyz WHERE x = 1) 1064 ---- 1065 limit 1066 ├── columns: x:1!null 1067 ├── project 1068 │ ├── columns: x:1!null 1069 │ └── select 1070 │ ├── columns: x:1!null y:2 z:3 1071 │ ├── scan xyz 1072 │ │ └── columns: x:1!null y:2 z:3 1073 │ └── filters 1074 │ └── x:1 = 7 1075 └── subquery 1076 └── max1-row 1077 ├── columns: x:4!null 1078 └── project 1079 ├── columns: x:4!null 1080 └── select 1081 ├── columns: x:4!null y:5 z:6 1082 ├── scan xyz 1083 │ └── columns: x:4!null y:5 z:6 1084 └── filters 1085 └── x:4 = 1 1086 1087 build 1088 SELECT x FROM xyz ORDER BY x OFFSET (SELECT x FROM xyz WHERE x = 1) 1089 ---- 1090 offset 1091 ├── columns: x:1!null 1092 ├── internal-ordering: +1 1093 ├── ordering: +1 1094 ├── project 1095 │ ├── columns: x:1!null 1096 │ ├── ordering: +1 1097 │ └── scan xyz 1098 │ ├── columns: x:1!null y:2 z:3 1099 │ └── ordering: +1 1100 └── subquery 1101 └── max1-row 1102 ├── columns: x:4!null 1103 └── project 1104 ├── columns: x:4!null 1105 └── select 1106 ├── columns: x:4!null y:5 z:6 1107 ├── scan xyz 1108 │ └── columns: x:4!null y:5 z:6 1109 └── filters 1110 └── x:4 = 1 1111 1112 # check that residual filters are not expanded twice 1113 build 1114 SELECT x FROM xyz WHERE x IN (SELECT x FROM xyz) 1115 ---- 1116 project 1117 ├── columns: x:1!null 1118 └── select 1119 ├── columns: x:1!null y:2 z:3 1120 ├── scan xyz 1121 │ └── columns: x:1!null y:2 z:3 1122 └── filters 1123 └── any: eq 1124 ├── project 1125 │ ├── columns: x:4!null 1126 │ └── scan xyz 1127 │ └── columns: x:4!null y:5 z:6 1128 └── x:1 1129 1130 # This test checks that the double sub-query plan expansion caused by a 1131 # sub-expression being shared by two or more plan nodes does not 1132 # panic. 1133 exec-ddl 1134 CREATE TABLE tab4 (col0 INTEGER, col1 FLOAT, col3 INTEGER, col4 FLOAT, INDEX idx_tab4_0 (col4,col0)) 1135 ---- 1136 1137 build 1138 SELECT col0 FROM tab4 WHERE (col0 <= 0 AND col4 <= 5.38) OR (col4 IN (SELECT col1 FROM tab4 WHERE col1 > 8.27)) AND (col3 <= 5 AND (col3 BETWEEN 7 AND 9)) 1139 ---- 1140 project 1141 ├── columns: col0:1!null 1142 └── select 1143 ├── columns: col0:1!null col1:2 col3:3 col4:4!null rowid:5!null 1144 ├── scan tab4 1145 │ └── columns: col0:1 col1:2 col3:3 col4:4 rowid:5!null 1146 └── filters 1147 └── or 1148 ├── (col0:1 <= 0) AND (col4:4 <= 5.38) 1149 └── and 1150 ├── any: eq 1151 │ ├── project 1152 │ │ ├── columns: col1:7!null 1153 │ │ └── select 1154 │ │ ├── columns: col0:6 col1:7!null col3:8 col4:9 rowid:10!null 1155 │ │ ├── scan tab4 1156 │ │ │ └── columns: col0:6 col1:7 col3:8 col4:9 rowid:10!null 1157 │ │ └── filters 1158 │ │ └── col1:7 > 8.27 1159 │ └── col4:4 1160 └── (col3:3 <= 5) AND ((col3:3 >= 7) AND (col3:3 <= 9)) 1161 1162 # Multiple subqueries in same projection list. 1163 build 1164 SELECT (SELECT 1 a), (SELECT 2 b) 1165 ---- 1166 project 1167 ├── columns: a:3 b:4 1168 ├── values 1169 │ └── () 1170 └── projections 1171 ├── subquery [as=a:3] 1172 │ └── max1-row 1173 │ ├── columns: a:1!null 1174 │ └── project 1175 │ ├── columns: a:1!null 1176 │ ├── values 1177 │ │ └── () 1178 │ └── projections 1179 │ └── 1 [as=a:1] 1180 └── subquery [as=b:4] 1181 └── max1-row 1182 ├── columns: b:2!null 1183 └── project 1184 ├── columns: b:2!null 1185 ├── values 1186 │ └── () 1187 └── projections 1188 └── 2 [as=b:2] 1189 1190 # Reuse duplicate subquery columns. 1191 build 1192 SELECT (SELECT 1 a), (SELECT a FROM abc), (SELECT 1 a), (SELECT a FROM abc) 1193 ---- 1194 project 1195 ├── columns: a:9 a:10 a:9 a:10 1196 ├── values 1197 │ └── () 1198 └── projections 1199 ├── subquery [as=a:9] 1200 │ └── max1-row 1201 │ ├── columns: a:1!null 1202 │ └── project 1203 │ ├── columns: a:1!null 1204 │ ├── values 1205 │ │ └── () 1206 │ └── projections 1207 │ └── 1 [as=a:1] 1208 └── subquery [as=a:10] 1209 └── max1-row 1210 ├── columns: abc.a:2!null 1211 └── project 1212 ├── columns: abc.a:2!null 1213 └── scan abc 1214 └── columns: abc.a:2!null b:3 c:4 1215 1216 # Multiple nested subqueries in same column list. 1217 build 1218 SELECT (SELECT (SELECT 1 AS x) AS a) AS r, (SELECT (SELECT 1 AS x) AS a) AS s, (SELECT 1 AS x) AS t 1219 ---- 1220 project 1221 ├── columns: r:6 s:6 t:7 1222 ├── values 1223 │ └── () 1224 └── projections 1225 ├── subquery [as=r:6] 1226 │ └── max1-row 1227 │ ├── columns: a:2 1228 │ └── project 1229 │ ├── columns: a:2 1230 │ ├── values 1231 │ │ └── () 1232 │ └── projections 1233 │ └── subquery [as=a:2] 1234 │ └── max1-row 1235 │ ├── columns: x:1!null 1236 │ └── project 1237 │ ├── columns: x:1!null 1238 │ ├── values 1239 │ │ └── () 1240 │ └── projections 1241 │ └── 1 [as=x:1] 1242 └── subquery [as=t:7] 1243 └── max1-row 1244 ├── columns: x:5!null 1245 └── project 1246 ├── columns: x:5!null 1247 ├── values 1248 │ └── () 1249 └── projections 1250 └── 1 [as=x:5] 1251 1252 # Test that the source name is found correctly in the subquery. 1253 build 1254 SELECT (SELECT akv.k) FROM kv akv 1255 ---- 1256 project 1257 ├── columns: k:4 1258 ├── scan akv 1259 │ └── columns: akv.k:1!null v:2 1260 └── projections 1261 └── subquery [as=k:4] 1262 └── max1-row 1263 ├── columns: k:3 1264 └── project 1265 ├── columns: k:3 1266 ├── values 1267 │ └── () 1268 └── projections 1269 └── akv.k:1 [as=k:3] 1270 1271 exec-ddl 1272 CREATE TABLE db1.kv (k INT PRIMARY KEY, v INT) 1273 ---- 1274 1275 build fully-qualify-names 1276 SELECT (SELECT t.kv.k) FROM db1.kv, kv 1277 ---- 1278 project 1279 ├── columns: k:6 1280 ├── inner-join (cross) 1281 │ ├── columns: db1.public.kv.k:1!null db1.public.kv.v:2 t.public.kv.k:3!null t.public.kv.v:4 1282 │ ├── scan db1.public.kv 1283 │ │ └── columns: db1.public.kv.k:1!null db1.public.kv.v:2 1284 │ ├── scan t.public.kv 1285 │ │ └── columns: t.public.kv.k:3!null t.public.kv.v:4 1286 │ └── filters (true) 1287 └── projections 1288 └── subquery [as=k:6] 1289 └── max1-row 1290 ├── columns: k:5 1291 └── project 1292 ├── columns: k:5 1293 ├── values 1294 │ └── () 1295 └── projections 1296 └── t.public.kv.k:3 [as=k:5] 1297 1298 # Ambiguity in parent scope. 1299 build fully-qualify-names 1300 SELECT (SELECT kv.k) FROM db1.kv, kv 1301 ---- 1302 error (42P09): ambiguous source name: "kv" 1303 1304 # Name not found after searching multiple scopes. 1305 build fully-qualify-names 1306 SELECT (SELECT kv1.k) FROM db1.kv, kv 1307 ---- 1308 error (42P01): no data source matches prefix: kv1 1309 1310 build fully-qualify-names 1311 SELECT (SELECT kv1.k) FROM db1.kv AS kv1, kv 1312 ---- 1313 project 1314 ├── columns: k:6 1315 ├── inner-join (cross) 1316 │ ├── columns: db1.public.kv.k:1!null db1.public.kv.v:2 t.public.kv.k:3!null t.public.kv.v:4 1317 │ ├── scan db1.public.kv 1318 │ │ └── columns: db1.public.kv.k:1!null db1.public.kv.v:2 1319 │ ├── scan t.public.kv 1320 │ │ └── columns: t.public.kv.k:3!null t.public.kv.v:4 1321 │ └── filters (true) 1322 └── projections 1323 └── subquery [as=k:6] 1324 └── max1-row 1325 ├── columns: k:5 1326 └── project 1327 ├── columns: k:5 1328 ├── values 1329 │ └── () 1330 └── projections 1331 └── db1.public.kv.k:1 [as=k:5] 1332 1333 # Check that the inner kv is chosen when there are matching names in both 1334 # scopes. 1335 build fully-qualify-names 1336 SELECT (SELECT kv.k FROM db1.kv) FROM kv 1337 ---- 1338 project 1339 ├── columns: k:5 1340 ├── scan t.public.kv 1341 │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 1342 └── projections 1343 └── subquery [as=k:5] 1344 └── max1-row 1345 ├── columns: db1.public.kv.k:3!null 1346 └── project 1347 ├── columns: db1.public.kv.k:3!null 1348 └── scan db1.public.kv 1349 └── columns: db1.public.kv.k:3!null db1.public.kv.v:4 1350 1351 # 2 nested scopes, mixed scope references. 1352 build fully-qualify-names 1353 SELECT (SELECT (SELECT t.kv.k + k AS r) FROM db1.kv) FROM kv 1354 ---- 1355 project 1356 ├── columns: r:7 1357 ├── scan t.public.kv 1358 │ └── columns: t.public.kv.k:1!null t.public.kv.v:2 1359 └── projections 1360 └── subquery [as=r:7] 1361 └── max1-row 1362 ├── columns: r:6 1363 └── project 1364 ├── columns: r:6 1365 ├── scan db1.public.kv 1366 │ └── columns: db1.public.kv.k:3!null db1.public.kv.v:4 1367 └── projections 1368 └── subquery [as=r:6] 1369 └── max1-row 1370 ├── columns: r:5 1371 └── project 1372 ├── columns: r:5 1373 ├── values 1374 │ └── () 1375 └── projections 1376 └── t.public.kv.k:1 + db1.public.kv.k:3 [as=r:5] 1377 1378 build 1379 SELECT (SELECT k FROM kv ORDER BY v) 1380 ---- 1381 project 1382 ├── columns: k:3 1383 ├── values 1384 │ └── () 1385 └── projections 1386 └── subquery [as=k:3] 1387 └── max1-row 1388 ├── columns: kv.k:1!null 1389 └── project 1390 ├── columns: kv.k:1!null 1391 └── scan kv 1392 └── columns: kv.k:1!null v:2 1393 1394 exec-ddl 1395 CREATE TABLE t1 (a INT, b INT) 1396 ---- 1397 1398 exec-ddl 1399 CREATE TABLE t2 (a INT, b INT) 1400 ---- 1401 1402 exec-ddl 1403 CREATE TABLE t3 (a INT, b INT) 1404 ---- 1405 1406 build 1407 SELECT (SELECT (SELECT DISTINCT t3.a FROM t1) FROM t2) FROM t3 1408 ---- 1409 project 1410 ├── columns: a:12 1411 ├── scan t3 1412 │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1413 └── projections 1414 └── subquery [as=a:12] 1415 └── max1-row 1416 ├── columns: a:11 1417 └── project 1418 ├── columns: a:11 1419 ├── scan t2 1420 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1421 └── projections 1422 └── subquery [as=a:11] 1423 └── max1-row 1424 ├── columns: a:10 1425 └── distinct-on 1426 ├── columns: a:10 1427 ├── grouping columns: a:10 1428 └── project 1429 ├── columns: a:10 1430 ├── scan t1 1431 │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1432 └── projections 1433 └── t3.a:1 [as=a:10] 1434 1435 build 1436 SELECT (SELECT (SELECT count(*) FROM t1 GROUP BY t3.a) FROM t2) FROM t3 1437 ---- 1438 project 1439 ├── columns: count:13 1440 ├── scan t3 1441 │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1442 └── projections 1443 └── subquery [as=count:13] 1444 └── max1-row 1445 ├── columns: count:12 1446 └── project 1447 ├── columns: count:12 1448 ├── scan t2 1449 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1450 └── projections 1451 └── subquery [as=count:12] 1452 └── max1-row 1453 ├── columns: count_rows:10!null 1454 └── project 1455 ├── columns: count_rows:10!null 1456 └── group-by 1457 ├── columns: count_rows:10!null a:11 1458 ├── grouping columns: a:11 1459 ├── project 1460 │ ├── columns: a:11 1461 │ ├── scan t1 1462 │ │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1463 │ └── projections 1464 │ └── t3.a:1 [as=a:11] 1465 └── aggregations 1466 └── count-rows [as=count_rows:10] 1467 1468 build 1469 SELECT (SELECT (SELECT t2.a + t3.a AS x FROM t1 GROUP BY t2.a + t3.a) AS y FROM t2) AS z FROM t3 1470 ---- 1471 project 1472 ├── columns: z:12 1473 ├── scan t3 1474 │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1475 └── projections 1476 └── subquery [as=z:12] 1477 └── max1-row 1478 ├── columns: y:11 1479 └── project 1480 ├── columns: y:11 1481 ├── scan t2 1482 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1483 └── projections 1484 └── subquery [as=y:11] 1485 └── max1-row 1486 ├── columns: column10:10 1487 └── group-by 1488 ├── columns: column10:10 1489 ├── grouping columns: column10:10 1490 └── project 1491 ├── columns: column10:10 1492 ├── scan t1 1493 │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1494 └── projections 1495 └── t2.a:4 + t3.a:1 [as=column10:10] 1496 1497 build 1498 SELECT (SELECT (SELECT t2.a + t3.a AS r FROM t1 GROUP BY t2.a, t3.a HAVING t2.a > t3.a) FROM t2) FROM t3 1499 ---- 1500 project 1501 ├── columns: r:14 1502 ├── scan t3 1503 │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1504 └── projections 1505 └── subquery [as=r:14] 1506 └── max1-row 1507 ├── columns: r:13 1508 └── project 1509 ├── columns: r:13 1510 ├── scan t2 1511 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1512 └── projections 1513 └── subquery [as=r:13] 1514 └── max1-row 1515 ├── columns: r:12!null 1516 └── project 1517 ├── columns: r:12!null 1518 ├── select 1519 │ ├── columns: a:10!null a:11!null 1520 │ ├── group-by 1521 │ │ ├── columns: a:10 a:11 1522 │ │ ├── grouping columns: a:10 a:11 1523 │ │ └── project 1524 │ │ ├── columns: a:10 a:11 1525 │ │ ├── scan t1 1526 │ │ │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1527 │ │ └── projections 1528 │ │ ├── t2.a:4 [as=a:10] 1529 │ │ └── t3.a:1 [as=a:11] 1530 │ └── filters 1531 │ └── a:10 > a:11 1532 └── projections 1533 └── a:10 + a:11 [as=r:12] 1534 1535 build 1536 SELECT (SELECT ARRAY[count(*), t1.a, t2.a] FROM t1 GROUP BY t1.a, t2.a HAVING t2.a > 5) FROM t2 1537 ---- 1538 project 1539 ├── columns: array:10 1540 ├── scan t2 1541 │ └── columns: t2.a:1 t2.b:2 t2.rowid:3!null 1542 └── projections 1543 └── subquery [as=array:10] 1544 └── max1-row 1545 ├── columns: array:9 1546 └── project 1547 ├── columns: array:9 1548 ├── select 1549 │ ├── columns: t1.a:4 count_rows:7!null a:8!null 1550 │ ├── group-by 1551 │ │ ├── columns: t1.a:4 count_rows:7!null a:8 1552 │ │ ├── grouping columns: t1.a:4 a:8 1553 │ │ ├── project 1554 │ │ │ ├── columns: a:8 t1.a:4 1555 │ │ │ ├── scan t1 1556 │ │ │ │ └── columns: t1.a:4 t1.b:5 t1.rowid:6!null 1557 │ │ │ └── projections 1558 │ │ │ └── t2.a:1 [as=a:8] 1559 │ │ └── aggregations 1560 │ │ └── count-rows [as=count_rows:7] 1561 │ └── filters 1562 │ └── a:8 > 5 1563 └── projections 1564 └── ARRAY[count_rows:7, t1.a:4, a:8] [as=array:9] 1565 1566 build 1567 SELECT (SELECT (SELECT max(t3.a) / min(t3.a) AS r FROM t1 GROUP BY t2.a) FROM t2) FROM t3 1568 ---- 1569 project 1570 ├── columns: r:17 1571 ├── scalar-group-by 1572 │ ├── columns: max:11 min:13 1573 │ ├── project 1574 │ │ ├── columns: a:10 a:12 1575 │ │ ├── scan t3 1576 │ │ │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1577 │ │ └── projections 1578 │ │ ├── t3.a:1 [as=a:10] 1579 │ │ └── t3.a:1 [as=a:12] 1580 │ └── aggregations 1581 │ ├── max [as=max:11] 1582 │ │ └── a:10 1583 │ └── min [as=min:13] 1584 │ └── a:12 1585 └── projections 1586 └── subquery [as=r:17] 1587 └── max1-row 1588 ├── columns: r:16 1589 └── project 1590 ├── columns: r:16 1591 ├── scan t2 1592 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1593 └── projections 1594 └── subquery [as=r:16] 1595 └── max1-row 1596 ├── columns: r:15 1597 └── project 1598 ├── columns: r:15 1599 ├── group-by 1600 │ ├── columns: a:14 1601 │ ├── grouping columns: a:14 1602 │ └── project 1603 │ ├── columns: a:14 1604 │ ├── scan t1 1605 │ │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1606 │ └── projections 1607 │ └── t2.a:4 [as=a:14] 1608 └── projections 1609 └── max:11 / min:13 [as=r:15] 1610 1611 exec-ddl 1612 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON) 1613 ---- 1614 1615 # Regression test for #27330. Ensure that the subquery only returns one column 1616 # and is correctly typed. 1617 build 1618 SELECT * 1619 FROM a 1620 WHERE 'bar'=(SELECT max(s) FROM a GROUP BY i ORDER BY i LIMIT 1) 1621 ---- 1622 select 1623 ├── columns: k:1!null i:2 f:3 s:4 j:5 1624 ├── scan a 1625 │ └── columns: k:1!null i:2 f:3 s:4 j:5 1626 └── filters 1627 └── eq 1628 ├── 'bar' 1629 └── subquery 1630 └── max1-row 1631 ├── columns: max:11 1632 └── project 1633 ├── columns: max:11 1634 └── limit 1635 ├── columns: i:7 max:11 1636 ├── internal-ordering: +7 1637 ├── sort 1638 │ ├── columns: i:7 max:11 1639 │ ├── ordering: +7 1640 │ ├── limit hint: 1.00 1641 │ └── group-by 1642 │ ├── columns: i:7 max:11 1643 │ ├── grouping columns: i:7 1644 │ ├── project 1645 │ │ ├── columns: i:7 s:9 1646 │ │ └── scan a 1647 │ │ └── columns: k:6!null i:7 f:8 s:9 j:10 1648 │ └── aggregations 1649 │ └── max [as=max:11] 1650 │ └── s:9 1651 └── 1 1652 1653 exec-ddl 1654 CREATE TABLE t (a string) 1655 ---- 1656 1657 exec-ddl 1658 CREATE TABLE u (b string) 1659 ---- 1660 1661 # Regression test for #27846. Ensure that an aggregate combined with ANY does 1662 # not cause a panic. 1663 build 1664 SELECT max(a) FROM t HAVING max(a) < ANY(SELECT b FROM u) 1665 ---- 1666 select 1667 ├── columns: max:3 1668 ├── scalar-group-by 1669 │ ├── columns: max:3 1670 │ ├── project 1671 │ │ ├── columns: a:1 1672 │ │ └── scan t 1673 │ │ └── columns: a:1 t.rowid:2!null 1674 │ └── aggregations 1675 │ └── max [as=max:3] 1676 │ └── a:1 1677 └── filters 1678 └── any: lt 1679 ├── project 1680 │ ├── columns: b:4 1681 │ └── scan u 1682 │ └── columns: b:4 u.rowid:5!null 1683 └── max:3 1684 1685 build 1686 SELECT min(a) IN (SELECT b FROM u) FROM t 1687 ---- 1688 project 1689 ├── columns: "?column?":6 1690 ├── scalar-group-by 1691 │ ├── columns: min:3 1692 │ ├── project 1693 │ │ ├── columns: a:1 1694 │ │ └── scan t 1695 │ │ └── columns: a:1 t.rowid:2!null 1696 │ └── aggregations 1697 │ └── min [as=min:3] 1698 │ └── a:1 1699 └── projections 1700 └── any: eq [as="?column?":6] 1701 ├── project 1702 │ ├── columns: b:4 1703 │ └── scan u 1704 │ └── columns: b:4 u.rowid:5!null 1705 └── min:3 1706 1707 # Regression test for #28240. Make sure that the tuple labels are stripped from 1708 # the subquery. 1709 build 1710 SELECT (1, 2, 3) IN (SELECT 1, 2, 3) 1711 ---- 1712 project 1713 ├── columns: "?column?":5 1714 ├── values 1715 │ └── () 1716 └── projections 1717 └── any: eq [as="?column?":5] 1718 ├── project 1719 │ ├── columns: column4:4!null 1720 │ ├── project 1721 │ │ ├── columns: "?column?":1!null "?column?":2!null "?column?":3!null 1722 │ │ ├── values 1723 │ │ │ └── () 1724 │ │ └── projections 1725 │ │ ├── 1 [as="?column?":1] 1726 │ │ ├── 2 [as="?column?":2] 1727 │ │ └── 3 [as="?column?":3] 1728 │ └── projections 1729 │ └── ("?column?":1, "?column?":2, "?column?":3) [as=column4:4] 1730 └── (1, 2, 3) 1731 1732 # Test aggregates at different scoping levels. 1733 build 1734 SELECT (SELECT (SELECT max(t3.a) FROM t1) FROM t2) FROM t3 1735 ---- 1736 project 1737 ├── columns: max:14 1738 ├── scalar-group-by 1739 │ ├── columns: max:11 1740 │ ├── project 1741 │ │ ├── columns: a:10 1742 │ │ ├── scan t3 1743 │ │ │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1744 │ │ └── projections 1745 │ │ └── t3.a:1 [as=a:10] 1746 │ └── aggregations 1747 │ └── max [as=max:11] 1748 │ └── a:10 1749 └── projections 1750 └── subquery [as=max:14] 1751 └── max1-row 1752 ├── columns: max:13 1753 └── project 1754 ├── columns: max:13 1755 ├── scan t2 1756 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1757 └── projections 1758 └── subquery [as=max:13] 1759 └── max1-row 1760 ├── columns: max:12 1761 └── project 1762 ├── columns: max:12 1763 ├── scan t1 1764 │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1765 └── projections 1766 └── max:11 [as=max:12] 1767 1768 build 1769 SELECT ( 1770 SELECT (SELECT row(max(t1.a), max(t2.a), max(t1.a + t3.a)) FROM t1) 1771 FROM t2 1772 ) 1773 FROM t3; 1774 ---- 1775 project 1776 ├── columns: row:17 1777 ├── scan t3 1778 │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 1779 └── projections 1780 └── subquery [as=row:17] 1781 └── max1-row 1782 ├── columns: row:16 1783 └── project 1784 ├── columns: row:16 1785 ├── scalar-group-by 1786 │ ├── columns: max:12 1787 │ ├── project 1788 │ │ ├── columns: a:11 1789 │ │ ├── scan t2 1790 │ │ │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 1791 │ │ └── projections 1792 │ │ └── t2.a:4 [as=a:11] 1793 │ └── aggregations 1794 │ └── max [as=max:12] 1795 │ └── a:11 1796 └── projections 1797 └── subquery [as=row:16] 1798 └── max1-row 1799 ├── columns: row:15 1800 └── project 1801 ├── columns: row:15 1802 ├── scalar-group-by 1803 │ ├── columns: max:10 max:14 1804 │ ├── project 1805 │ │ ├── columns: column13:13 t1.a:7 1806 │ │ ├── scan t1 1807 │ │ │ └── columns: t1.a:7 t1.b:8 t1.rowid:9!null 1808 │ │ └── projections 1809 │ │ └── t1.a:7 + t3.a:1 [as=column13:13] 1810 │ └── aggregations 1811 │ ├── max [as=max:10] 1812 │ │ └── t1.a:7 1813 │ └── max [as=max:14] 1814 │ └── column13:13 1815 └── projections 1816 └── (max:10, max:12, max:14) [as=row:15] 1817 1818 build 1819 SELECT (SELECT row(max(t1.a), max(t2.a), max(t1.a + t2.a)) FROM t1) FROM t2; 1820 ---- 1821 error (42803): subquery uses ungrouped column "a" from outer query 1822 1823 build 1824 SELECT (SELECT row(max(t1.a), max(t2.a), max(t1.a + t2.a)) FROM t1) FROM t2 GROUP BY t2.a; 1825 ---- 1826 project 1827 ├── columns: row:13 1828 ├── group-by 1829 │ ├── columns: t2.a:1 max:9 1830 │ ├── grouping columns: t2.a:1 1831 │ ├── project 1832 │ │ ├── columns: a:8 t2.a:1 1833 │ │ ├── scan t2 1834 │ │ │ └── columns: t2.a:1 t2.b:2 t2.rowid:3!null 1835 │ │ └── projections 1836 │ │ └── t2.a:1 [as=a:8] 1837 │ └── aggregations 1838 │ └── max [as=max:9] 1839 │ └── a:8 1840 └── projections 1841 └── subquery [as=row:13] 1842 └── max1-row 1843 ├── columns: row:12 1844 └── project 1845 ├── columns: row:12 1846 ├── scalar-group-by 1847 │ ├── columns: max:7 max:11 1848 │ ├── project 1849 │ │ ├── columns: column10:10 t1.a:4 1850 │ │ ├── scan t1 1851 │ │ │ └── columns: t1.a:4 t1.b:5 t1.rowid:6!null 1852 │ │ └── projections 1853 │ │ └── t1.a:4 + t2.a:1 [as=column10:10] 1854 │ └── aggregations 1855 │ ├── max [as=max:7] 1856 │ │ └── t1.a:4 1857 │ └── max [as=max:11] 1858 │ └── column10:10 1859 └── projections 1860 └── (max:7, max:9, max:11) [as=row:12] 1861 1862 build 1863 SELECT 1864 (SELECT max(t1.a) FROM t1 GROUP BY t2.b), 1865 (SELECT max(t2.a) FROM t1 GROUP BY t2.b) 1866 FROM 1867 t2; 1868 ---- 1869 error (42803): subquery uses ungrouped column "b" from outer query 1870 1871 build 1872 SELECT 1873 ARRAY (SELECT max(t1.a) FROM t1 GROUP BY t2.b), 1874 ARRAY (SELECT max(t2.a) FROM t1 GROUP BY t2.b) 1875 FROM 1876 t2; 1877 ---- 1878 error (42803): subquery uses ungrouped column "b" from outer query 1879 1880 build 1881 SELECT 1882 5 IN (SELECT max(t1.a) FROM t1 GROUP BY t2.b), 1883 100 < ANY (SELECT max(t2.a) FROM t1 GROUP BY t2.b) 1884 FROM 1885 t2; 1886 ---- 1887 error (42803): subquery uses ungrouped column "b" from outer query 1888 1889 build 1890 SELECT 1891 (SELECT max(t1.a) FROM t1 GROUP BY t1.b), 1892 (SELECT max(t2.a) FROM t1 GROUP BY t1.b) 1893 FROM 1894 t2; 1895 ---- 1896 project 1897 ├── columns: max:14 max:15 1898 ├── scalar-group-by 1899 │ ├── columns: max:12 1900 │ ├── project 1901 │ │ ├── columns: a:11 1902 │ │ ├── scan t2 1903 │ │ │ └── columns: t2.a:1 t2.b:2 t2.rowid:3!null 1904 │ │ └── projections 1905 │ │ └── t2.a:1 [as=a:11] 1906 │ └── aggregations 1907 │ └── max [as=max:12] 1908 │ └── a:11 1909 └── projections 1910 ├── subquery [as=max:14] 1911 │ └── max1-row 1912 │ ├── columns: max:7 1913 │ └── project 1914 │ ├── columns: max:7 1915 │ └── group-by 1916 │ ├── columns: t1.b:5 max:7 1917 │ ├── grouping columns: t1.b:5 1918 │ ├── project 1919 │ │ ├── columns: t1.a:4 t1.b:5 1920 │ │ └── scan t1 1921 │ │ └── columns: t1.a:4 t1.b:5 t1.rowid:6!null 1922 │ └── aggregations 1923 │ └── max [as=max:7] 1924 │ └── t1.a:4 1925 └── subquery [as=max:15] 1926 └── max1-row 1927 ├── columns: max:13 1928 └── project 1929 ├── columns: max:13 1930 ├── group-by 1931 │ ├── columns: t1.b:9 1932 │ ├── grouping columns: t1.b:9 1933 │ └── project 1934 │ ├── columns: t1.b:9 1935 │ └── scan t1 1936 │ └── columns: t1.a:8 t1.b:9 t1.rowid:10!null 1937 └── projections 1938 └── max:12 [as=max:13] 1939 1940 build 1941 SELECT 1942 ARRAY (SELECT max(t1.a) FROM t1), 1943 ARRAY (SELECT max(t2.a) FROM t1) 1944 FROM 1945 t2 1946 GROUP BY t2.b; 1947 ---- 1948 project 1949 ├── columns: array:14 array:15 1950 ├── group-by 1951 │ ├── columns: t2.b:2 max:12 1952 │ ├── grouping columns: t2.b:2 1953 │ ├── project 1954 │ │ ├── columns: a:11 t2.b:2 1955 │ │ ├── scan t2 1956 │ │ │ └── columns: t2.a:1 t2.b:2 t2.rowid:3!null 1957 │ │ └── projections 1958 │ │ └── t2.a:1 [as=a:11] 1959 │ └── aggregations 1960 │ └── max [as=max:12] 1961 │ └── a:11 1962 └── projections 1963 ├── array-flatten [as=array:14] 1964 │ └── scalar-group-by 1965 │ ├── columns: max:7 1966 │ ├── project 1967 │ │ ├── columns: t1.a:4 1968 │ │ └── scan t1 1969 │ │ └── columns: t1.a:4 t1.b:5 t1.rowid:6!null 1970 │ └── aggregations 1971 │ └── max [as=max:7] 1972 │ └── t1.a:4 1973 └── array-flatten [as=array:15] 1974 └── project 1975 ├── columns: max:13 1976 ├── scan t1 1977 │ └── columns: t1.a:8 t1.b:9 t1.rowid:10!null 1978 └── projections 1979 └── max:12 [as=max:13] 1980 1981 build 1982 SELECT 1983 ARRAY (SELECT max(t1.a) FROM t1 GROUP BY t2.b), 1984 ARRAY (SELECT max(t2.a) FROM t1 GROUP BY t2.b), 1985 ARRAY (SELECT max(t1.a + t2.a) FROM t1 GROUP BY t2.b) 1986 FROM 1987 t2 1988 GROUP BY t2.a, t2.b; 1989 ---- 1990 project 1991 ├── columns: array:22 array:23 array:24 1992 ├── group-by 1993 │ ├── columns: t2.a:1 t2.b:2 max:13 1994 │ ├── grouping columns: t2.a:1 t2.b:2 1995 │ ├── project 1996 │ │ ├── columns: a:12 t2.a:1 t2.b:2 1997 │ │ ├── scan t2 1998 │ │ │ └── columns: t2.a:1 t2.b:2 t2.rowid:3!null 1999 │ │ └── projections 2000 │ │ └── t2.a:1 [as=a:12] 2001 │ └── aggregations 2002 │ └── max [as=max:13] 2003 │ └── a:12 2004 └── projections 2005 ├── array-flatten [as=array:22] 2006 │ └── project 2007 │ ├── columns: max:7 2008 │ └── group-by 2009 │ ├── columns: max:7 b:8 2010 │ ├── grouping columns: b:8 2011 │ ├── project 2012 │ │ ├── columns: b:8 t1.a:4 2013 │ │ ├── scan t1 2014 │ │ │ └── columns: t1.a:4 t1.b:5 t1.rowid:6!null 2015 │ │ └── projections 2016 │ │ └── t2.b:2 [as=b:8] 2017 │ └── aggregations 2018 │ └── max [as=max:7] 2019 │ └── t1.a:4 2020 ├── array-flatten [as=array:23] 2021 │ └── project 2022 │ ├── columns: max:15 2023 │ ├── group-by 2024 │ │ ├── columns: b:14 2025 │ │ ├── grouping columns: b:14 2026 │ │ └── project 2027 │ │ ├── columns: b:14 2028 │ │ ├── scan t1 2029 │ │ │ └── columns: t1.a:9 t1.b:10 t1.rowid:11!null 2030 │ │ └── projections 2031 │ │ └── t2.b:2 [as=b:14] 2032 │ └── projections 2033 │ └── max:13 [as=max:15] 2034 └── array-flatten [as=array:24] 2035 └── project 2036 ├── columns: max:20 2037 └── group-by 2038 ├── columns: max:20 b:21 2039 ├── grouping columns: b:21 2040 ├── project 2041 │ ├── columns: column19:19 b:21 2042 │ ├── scan t1 2043 │ │ └── columns: t1.a:16 t1.b:17 t1.rowid:18!null 2044 │ └── projections 2045 │ ├── t1.a:16 + t2.a:1 [as=column19:19] 2046 │ └── t2.b:2 [as=b:21] 2047 └── aggregations 2048 └── max [as=max:20] 2049 └── column19:19 2050 2051 build 2052 SELECT 2053 (SELECT (SELECT (SELECT (max(t1.a * t2.a), max(t3.a)) FROM t1 GROUP BY t3.b) FROM t2)) 2054 FROM 2055 t3; 2056 ---- 2057 error (42803): subquery uses ungrouped column "b" from outer query 2058 2059 build 2060 SELECT 2061 ( 2062 SELECT 2063 (t2.a < ALL (SELECT max((SELECT max(a) FROM t1 GROUP BY t3.b)) FROM t1), max(t3.a)) 2064 FROM 2065 t2 2066 ) 2067 FROM 2068 t3; 2069 ---- 2070 project 2071 ├── columns: "?column?":21 2072 ├── scalar-group-by 2073 │ ├── columns: max:8 max:18 2074 │ ├── project 2075 │ │ ├── columns: a:7 column17:17 2076 │ │ ├── scan t3 2077 │ │ │ └── columns: t3.a:1 t3.b:2 t3.rowid:3!null 2078 │ │ └── projections 2079 │ │ ├── t3.a:1 [as=a:7] 2080 │ │ └── subquery [as=column17:17] 2081 │ │ └── max1-row 2082 │ │ ├── columns: max:15 2083 │ │ └── project 2084 │ │ ├── columns: max:15 2085 │ │ └── group-by 2086 │ │ ├── columns: max:15 b:16 2087 │ │ ├── grouping columns: b:16 2088 │ │ ├── project 2089 │ │ │ ├── columns: b:16 t1.a:12 2090 │ │ │ ├── scan t1 2091 │ │ │ │ └── columns: t1.a:12 t1.b:13 t1.rowid:14!null 2092 │ │ │ └── projections 2093 │ │ │ └── t3.b:2 [as=b:16] 2094 │ │ └── aggregations 2095 │ │ └── max [as=max:15] 2096 │ │ └── t1.a:12 2097 │ └── aggregations 2098 │ ├── max [as=max:8] 2099 │ │ └── a:7 2100 │ └── max [as=max:18] 2101 │ └── column17:17 2102 └── projections 2103 └── subquery [as="?column?":21] 2104 └── max1-row 2105 ├── columns: "?column?":20 2106 └── project 2107 ├── columns: "?column?":20 2108 ├── scan t2 2109 │ └── columns: t2.a:4 t2.b:5 t2.rowid:6!null 2110 └── projections 2111 └── tuple [as="?column?":20] 2112 ├── not 2113 │ └── any: ge 2114 │ ├── project 2115 │ │ ├── columns: max:19 2116 │ │ ├── scan t1 2117 │ │ │ └── columns: t1.a:9 t1.b:10 t1.rowid:11!null 2118 │ │ └── projections 2119 │ │ └── max:18 [as=max:19] 2120 │ └── t2.a:4 2121 └── max:8 2122 2123 exec-ddl 2124 CREATE TABLE v (x INT) 2125 ---- 2126 2127 exec-ddl 2128 CREATE TABLE w (y INT[]) 2129 ---- 2130 2131 # Regression test for #30191. Ensure ArrayFlatten returns correct type. 2132 build 2133 SELECT * FROM w WHERE y = ARRAY(SELECT x FROM v ORDER BY x) 2134 ---- 2135 project 2136 ├── columns: y:1!null 2137 └── select 2138 ├── columns: y:1!null w.rowid:2!null 2139 ├── scan w 2140 │ └── columns: y:1 w.rowid:2!null 2141 └── filters 2142 └── eq 2143 ├── y:1 2144 └── array-flatten 2145 └── sort 2146 ├── columns: x:3 2147 ├── ordering: +3 2148 └── project 2149 ├── columns: x:3 2150 └── scan v 2151 └── columns: x:3 v.rowid:4!null 2152 2153 # Regression test for #30424. Aggregate function in the WHERE subquery 2154 # is aggregated in the outer scope, so it's not allowed. 2155 build 2156 SELECT s FROM a WHERE (SELECT count(i) >= 100) GROUP BY s 2157 ---- 2158 error (42803): aggregate functions are not allowed in WHERE 2159 2160 # Aggregate function in the WHERE subquery is aggregated in the subquery scope, 2161 # so it's allowed. 2162 build 2163 SELECT s FROM a WHERE (SELECT count(i) >= 100 FROM a) GROUP BY s 2164 ---- 2165 group-by 2166 ├── columns: s:4 2167 ├── grouping columns: s:4 2168 └── project 2169 ├── columns: s:4 2170 └── select 2171 ├── columns: k:1!null i:2 f:3 s:4 j:5 2172 ├── scan a 2173 │ └── columns: k:1!null i:2 f:3 s:4 j:5 2174 └── filters 2175 └── subquery 2176 └── max1-row 2177 ├── columns: "?column?":12!null 2178 └── project 2179 ├── columns: "?column?":12!null 2180 ├── scalar-group-by 2181 │ ├── columns: count:11!null 2182 │ ├── project 2183 │ │ ├── columns: i:7 2184 │ │ └── scan a 2185 │ │ └── columns: k:6!null i:7 f:8 s:9 j:10 2186 │ └── aggregations 2187 │ └── count [as=count:11] 2188 │ └── i:7 2189 └── projections 2190 └── count:11 >= 100 [as="?column?":12] 2191 2192 exec-ddl 2193 CREATE TABLE xyzs (x INT PRIMARY KEY, y INT, z FLOAT NOT NULL, s STRING, UNIQUE (s DESC, z)); 2194 ---- 2195 2196 exec-ddl 2197 CREATE TABLE kuv (k INT PRIMARY KEY, u FLOAT, v STRING); 2198 ---- 2199 2200 # Regression test for #29114. 2201 build 2202 SELECT * FROM xyzs WHERE (SELECT sum(x) FROM (SELECT u FROM kuv) GROUP BY u) > 100; 2203 ---- 2204 error (42803): aggregate functions are not allowed in WHERE 2205 2206 # Regression test for #37263. 2207 build 2208 SELECT 3::decimal IN (SELECT 1) 2209 ---- 2210 project 2211 ├── columns: "?column?":2 2212 ├── values 2213 │ └── () 2214 └── projections 2215 └── any: eq [as="?column?":2] 2216 ├── project 2217 │ ├── columns: "?column?":1!null 2218 │ ├── values 2219 │ │ └── () 2220 │ └── projections 2221 │ └── 1 [as="?column?":1] 2222 └── 3::DECIMAL 2223 2224 build 2225 SELECT 3::decimal IN (SELECT 1::int) 2226 ---- 2227 error (22023): unsupported comparison operator: <decimal> IN <tuple{int}> 2228 2229 # Regression test for #47467 - we weren't correctly identifying a.x as an outer column. 2230 build 2231 SELECT 1 FROM (VALUES (1)) AS a(x) HAVING (SELECT true FROM (VALUES (a.x)) AS b(y)) 2232 ---- 2233 error (42803): subquery uses ungrouped column "x" from outer query