github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/select (about) 1 # tests adapted from logictest -- select 2 3 # These statements must be first - the numeric reference tests assume that 4 # these are the first tables defined. Cockroach numeric references start after 5 # 53 for user tables. See opt/testutils/testcat/create_table.go:117 for more 6 # info on 53 as a magic number. 7 8 exec-ddl 9 CREATE TABLE tab53 (a INT PRIMARY KEY, y INT, b INT, c INT, INDEX bc (b,c)) 10 ---- 11 12 exec-ddl 13 CREATE TABLE tab54 (x INT, y INT) 14 ---- 15 16 exec-ddl 17 CREATE TABLE tab55 (a INT PRIMARY KEY, b INT NOT NULL, CONSTRAINT foo CHECK (a+b < 10)) 18 ---- 19 20 # SELECT with no table. 21 22 build 23 SELECT 1 24 ---- 25 project 26 ├── columns: "?column?":1!null 27 ├── values 28 │ └── () 29 └── projections 30 └── 1 [as="?column?":1] 31 32 build 33 SELECT NULL 34 ---- 35 project 36 ├── columns: "?column?":1 37 ├── values 38 │ └── () 39 └── projections 40 └── NULL [as="?column?":1] 41 42 build 43 SELECT 1+1 AS two, 2+2 AS four 44 ---- 45 project 46 ├── columns: two:1!null four:2!null 47 ├── values 48 │ └── () 49 └── projections 50 ├── 2 [as=two:1] 51 └── 4 [as=four:2] 52 53 # SELECT expression tests. 54 55 exec-ddl 56 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 57 ---- 58 59 build 60 SELECT * FROM abc WHERE 'hello' 61 ---- 62 error (22P02): could not parse "hello" as type bool: invalid bool value 63 64 build 65 SELECT * FROM abc 66 ---- 67 scan abc 68 └── columns: a:1!null b:2 c:3 69 70 build 71 SELECT NULL AS r, * FROM abc 72 ---- 73 project 74 ├── columns: r:4 a:1!null b:2 c:3 75 ├── scan abc 76 │ └── columns: a:1!null b:2 c:3 77 └── projections 78 └── NULL [as=r:4] 79 80 81 # synonym for SELECT * FROM abc 82 build 83 TABLE abc 84 ---- 85 scan abc 86 └── columns: a:1!null b:2 c:3 87 88 build 89 SELECT * FROM abc WHERE NULL 90 ---- 91 select 92 ├── columns: a:1!null b:2 c:3 93 ├── scan abc 94 │ └── columns: a:1!null b:2 c:3 95 └── filters 96 └── NULL::BOOL 97 98 build 99 SELECT * FROM abc WHERE a = NULL 100 ---- 101 select 102 ├── columns: a:1!null b:2 c:3 103 ├── scan abc 104 │ └── columns: a:1!null b:2 c:3 105 └── filters 106 └── NULL::BOOL 107 108 build 109 SELECT *,* FROM abc 110 ---- 111 scan abc 112 └── columns: a:1!null b:2 c:3 a:1!null b:2 c:3 113 114 build 115 SELECT a,a,a,a FROM abc 116 ---- 117 project 118 ├── columns: a:1!null a:1!null a:1!null a:1!null 119 └── scan abc 120 └── columns: a:1!null b:2 c:3 121 122 build 123 SELECT a,c FROM abc 124 ---- 125 project 126 ├── columns: a:1!null c:3 127 └── scan abc 128 └── columns: a:1!null b:2 c:3 129 130 build 131 SELECT a+b+c AS foo FROM abc 132 ---- 133 project 134 ├── columns: foo:4 135 ├── scan abc 136 │ └── columns: a:1!null b:2 c:3 137 └── projections 138 └── (a:1 + b:2) + c:3 [as=foo:4] 139 140 build 141 SELECT a,b FROM abc WHERE CASE WHEN a != 0 THEN b/a > 1.5 ELSE false END 142 ---- 143 project 144 ├── columns: a:1!null b:2 145 └── select 146 ├── columns: a:1!null b:2 c:3 147 ├── scan abc 148 │ └── columns: a:1!null b:2 c:3 149 └── filters 150 └── CASE WHEN a:1 != 0 THEN (b:2 / a:1) > 1.5 ELSE false END 151 152 # SELECT of NULL value. 153 154 exec-ddl 155 CREATE TABLE kv (k CHAR PRIMARY KEY, v CHAR) 156 ---- 157 158 build 159 SELECT * FROM kv 160 ---- 161 scan kv 162 └── columns: k:1!null v:2 163 164 build 165 SELECT k,v FROM kv 166 ---- 167 scan kv 168 └── columns: k:1!null v:2 169 170 build 171 SELECT v||'foo' AS r FROM kv 172 ---- 173 project 174 ├── columns: r:3 175 ├── scan kv 176 │ └── columns: k:1!null v:2 177 └── projections 178 └── v:2 || 'foo' [as=r:3] 179 180 build 181 SELECT lower(v) FROM kv 182 ---- 183 project 184 ├── columns: lower:3 185 ├── scan kv 186 │ └── columns: k:1!null v:2 187 └── projections 188 └── lower(v:2) [as=lower:3] 189 190 build 191 SELECT k FROM kv 192 ---- 193 project 194 ├── columns: k:1!null 195 └── scan kv 196 └── columns: k:1!null v:2 197 198 build 199 SELECT kv.K,KV.v FROM kv 200 ---- 201 scan kv 202 └── columns: k:1!null v:2 203 204 build 205 SELECT kv.* FROM kv 206 ---- 207 scan kv 208 └── columns: k:1!null v:2 209 210 build 211 SELECT (kv.*) AS r FROM kv 212 ---- 213 project 214 ├── columns: r:3 215 ├── scan kv 216 │ └── columns: k:1!null v:2 217 └── projections 218 └── ((k:1, v:2) AS k, v) [as=r:3] 219 220 build 221 SELECT (SELECT t.*) FROM (VALUES (1)) AS t(x) 222 ---- 223 project 224 ├── columns: "?column?":3 225 ├── values 226 │ ├── columns: column1:1!null 227 │ └── (1,) 228 └── projections 229 └── subquery [as="?column?":3] 230 └── max1-row 231 ├── columns: x:2 232 └── project 233 ├── columns: x:2 234 ├── values 235 │ └── () 236 └── projections 237 └── column1:1 [as=x:2] 238 239 build 240 SELECT foo.* FROM kv 241 ---- 242 error (42P01): no data source matches pattern: foo.* 243 244 build 245 SELECT * 246 ---- 247 error (42602): cannot use "*" without a FROM clause 248 249 build 250 SELECT kv.* AS foo FROM kv 251 ---- 252 error (42601): "kv.*" cannot be aliased 253 254 build 255 SELECT bar.kv.* FROM kv 256 ---- 257 error (42P01): no data source matches pattern: bar.kv.* 258 259 # Don't panic with invalid names (#8024) 260 build 261 SELECT kv.*[1] FROM kv 262 ---- 263 error (42804): cannot subscript type tuple{char AS k, char AS v} because it is not an array 264 265 build 266 SELECT ARRAY[] 267 ---- 268 error (42P18): cannot determine type of empty array. Consider annotating with the desired type, for example ARRAY[]:::int[] 269 270 build 271 SELECT FOO.k FROM kv AS foo WHERE foo.k = 'a' 272 ---- 273 project 274 ├── columns: k:1!null 275 └── select 276 ├── columns: k:1!null v:2 277 ├── scan foo 278 │ └── columns: k:1!null v:2 279 └── filters 280 └── k:1 = 'a' 281 282 build 283 SELECT "foo"."v" FROM kv AS foo WHERE foo.k = 'a' 284 ---- 285 project 286 ├── columns: v:2 287 └── select 288 ├── columns: k:1!null v:2 289 ├── scan foo 290 │ └── columns: k:1!null v:2 291 └── filters 292 └── k:1 = 'a' 293 294 exec-ddl 295 CREATE TABLE kw ("from" INT PRIMARY KEY) 296 ---- 297 298 build 299 SELECT *, "from", kw."from" FROM kw 300 ---- 301 scan kw 302 └── columns: from:1!null from:1!null from:1!null 303 304 exec-ddl 305 CREATE TABLE xyzw ( 306 x INT PRIMARY KEY, 307 y INT, 308 z INT, 309 w INT, 310 INDEX foo (z, y) 311 ) 312 ---- 313 314 # SELECT with index hints. 315 316 build 317 SELECT * FROM xyzw@primary 318 ---- 319 scan xyzw 320 ├── columns: x:1!null y:2 z:3 w:4 321 └── flags: force-index=primary 322 323 build 324 SELECT * FROM xyzw@foo 325 ---- 326 scan xyzw 327 ├── columns: x:1!null y:2 z:3 w:4 328 └── flags: force-index=foo 329 330 build 331 SELECT * FROM xyzw@{FORCE_INDEX=foo,ASC} 332 ---- 333 scan xyzw 334 ├── columns: x:1!null y:2 z:3 w:4 335 └── flags: force-index=foo,fwd 336 337 build 338 SELECT * FROM xyzw@{FORCE_INDEX=foo,DESC} 339 ---- 340 scan xyzw,rev 341 ├── columns: x:1!null y:2 z:3 w:4 342 └── flags: force-index=foo,rev 343 344 build 345 SELECT * FROM xyzw@{NO_INDEX_JOIN} 346 ---- 347 scan xyzw 348 ├── columns: x:1!null y:2 z:3 w:4 349 └── flags: no-index-join 350 351 build 352 SELECT * FROM xyzw LIMIT x 353 ---- 354 error (42703): column "x" does not exist 355 356 build 357 SELECT * FROM xyzw OFFSET 1 + y 358 ---- 359 error (42703): column "y" does not exist 360 361 build 362 SELECT * FROM xyzw LIMIT 3.3 363 ---- 364 error (42804): argument of LIMIT must be type int, not type decimal 365 366 build 367 SELECT * FROM xyzw ORDER BY 1 LIMIT '1' 368 ---- 369 limit 370 ├── columns: x:1!null y:2 z:3 w:4 371 ├── internal-ordering: +1 372 ├── ordering: +1 373 ├── scan xyzw 374 │ ├── columns: x:1!null y:2 z:3 w:4 375 │ ├── ordering: +1 376 │ └── limit hint: 1.00 377 └── 1 378 379 build 380 SELECT * FROM xyzw OFFSET 1.5 381 ---- 382 error (42804): argument of OFFSET must be type int, not type decimal 383 384 # At execution time, this will cause the error: negative value for LIMIT 385 build 386 SELECT * FROM xyzw LIMIT -100 387 ---- 388 limit 389 ├── columns: x:1!null y:2 z:3 w:4 390 ├── scan xyzw 391 │ ├── columns: x:1!null y:2 z:3 w:4 392 │ └── limit hint: 1.00 393 └── -100 394 395 # At execution time, this will cause the error: negative value for OFFSET 396 build 397 SELECT * FROM xyzw OFFSET -100 398 ---- 399 offset 400 ├── columns: x:1!null y:2 z:3 w:4 401 ├── scan xyzw 402 │ └── columns: x:1!null y:2 z:3 w:4 403 └── -100 404 405 build 406 SELECT * FROM xyzw ORDER BY x OFFSET 1 + 0.0 407 ---- 408 offset 409 ├── columns: x:1!null y:2 z:3 w:4 410 ├── internal-ordering: +1 411 ├── ordering: +1 412 ├── scan xyzw 413 │ ├── columns: x:1!null y:2 z:3 w:4 414 │ └── ordering: +1 415 └── 1 416 417 build 418 SELECT (x,y) AS r FROM xyzw 419 ---- 420 project 421 ├── columns: r:5 422 ├── scan xyzw 423 │ └── columns: x:1!null y:2 z:3 w:4 424 └── projections 425 └── (x:1, y:2) [as=r:5] 426 427 build 428 SELECT * FROM xyzw LIMIT 0 429 ---- 430 limit 431 ├── columns: x:1!null y:2 z:3 w:4 432 ├── scan xyzw 433 │ ├── columns: x:1!null y:2 z:3 w:4 434 │ └── limit hint: 1.00 435 └── 0 436 437 build 438 SELECT * FROM xyzw ORDER BY x LIMIT 1 439 ---- 440 limit 441 ├── columns: x:1!null y:2 z:3 w:4 442 ├── internal-ordering: +1 443 ├── ordering: +1 444 ├── scan xyzw 445 │ ├── columns: x:1!null y:2 z:3 w:4 446 │ ├── ordering: +1 447 │ └── limit hint: 1.00 448 └── 1 449 450 build 451 SELECT * FROM xyzw ORDER BY x LIMIT 1 OFFSET 1 452 ---- 453 limit 454 ├── columns: x:1!null y:2 z:3 w:4 455 ├── internal-ordering: +1 456 ├── ordering: +1 457 ├── offset 458 │ ├── columns: x:1!null y:2 z:3 w:4 459 │ ├── internal-ordering: +1 460 │ ├── ordering: +1 461 │ ├── limit hint: 1.00 462 │ ├── scan xyzw 463 │ │ ├── columns: x:1!null y:2 z:3 w:4 464 │ │ ├── ordering: +1 465 │ │ └── limit hint: 2.00 466 │ └── 1 467 └── 1 468 469 build 470 SELECT * FROM xyzw ORDER BY y OFFSET 1 471 ---- 472 offset 473 ├── columns: x:1!null y:2 z:3 w:4 474 ├── internal-ordering: +2 475 ├── ordering: +2 476 ├── sort 477 │ ├── columns: x:1!null y:2 z:3 w:4 478 │ ├── ordering: +2 479 │ └── scan xyzw 480 │ └── columns: x:1!null y:2 z:3 w:4 481 └── 1 482 483 build 484 SELECT * FROM xyzw ORDER BY y OFFSET 1 LIMIT 1 485 ---- 486 limit 487 ├── columns: x:1!null y:2 z:3 w:4 488 ├── internal-ordering: +2 489 ├── ordering: +2 490 ├── offset 491 │ ├── columns: x:1!null y:2 z:3 w:4 492 │ ├── internal-ordering: +2 493 │ ├── ordering: +2 494 │ ├── limit hint: 1.00 495 │ ├── sort 496 │ │ ├── columns: x:1!null y:2 z:3 w:4 497 │ │ ├── ordering: +2 498 │ │ ├── limit hint: 2.00 499 │ │ └── scan xyzw 500 │ │ └── columns: x:1!null y:2 z:3 w:4 501 │ └── 1 502 └── 1 503 504 build 505 SELECT * FROM xyzw LIMIT (SELECT count(*) FROM abc) * 2 OFFSET (SELECT count(*) FROM abc) * 3 506 ---- 507 limit 508 ├── columns: x:1!null y:2 z:3 w:4 509 ├── offset 510 │ ├── columns: x:1!null y:2 z:3 w:4 511 │ ├── scan xyzw 512 │ │ └── columns: x:1!null y:2 z:3 w:4 513 │ └── mult 514 │ ├── subquery 515 │ │ └── max1-row 516 │ │ ├── columns: count_rows:8!null 517 │ │ └── scalar-group-by 518 │ │ ├── columns: count_rows:8!null 519 │ │ ├── project 520 │ │ │ └── scan abc 521 │ │ │ └── columns: a:5!null b:6 c:7 522 │ │ └── aggregations 523 │ │ └── count-rows [as=count_rows:8] 524 │ └── 3 525 └── mult 526 ├── subquery 527 │ └── max1-row 528 │ ├── columns: count_rows:12!null 529 │ └── scalar-group-by 530 │ ├── columns: count_rows:12!null 531 │ ├── project 532 │ │ └── scan abc 533 │ │ └── columns: a:9!null b:10 c:11 534 │ └── aggregations 535 │ └── count-rows [as=count_rows:12] 536 └── 2 537 538 build 539 ((SELECT x FROM xyzw LIMIT 1)) LIMIT 1 540 ---- 541 error (42601): multiple LIMIT clauses not allowed 542 543 build 544 SELECT * FROM (SELECT * FROM xyzw LIMIT 5) OFFSET 5 545 ---- 546 offset 547 ├── columns: x:1!null y:2 z:3 w:4 548 ├── limit 549 │ ├── columns: x:1!null y:2 z:3 w:4 550 │ ├── scan xyzw 551 │ │ ├── columns: x:1!null y:2 z:3 w:4 552 │ │ └── limit hint: 5.00 553 │ └── 5 554 └── 5 555 556 build 557 SELECT * FROM xyzw@foo 558 ---- 559 scan xyzw 560 ├── columns: x:1!null y:2 z:3 w:4 561 └── flags: force-index=foo 562 563 exec-ddl 564 CREATE TABLE boolean_table ( 565 id INTEGER PRIMARY KEY NOT NULL, 566 value BOOLEAN 567 ) 568 ---- 569 570 build 571 SELECT value FROM boolean_table 572 ---- 573 project 574 ├── columns: value:2 575 └── scan boolean_table 576 └── columns: id:1!null value:2 577 578 build 579 SELECT CASE WHEN NULL THEN 1 ELSE 2 END 580 ---- 581 project 582 ├── columns: case:1 583 ├── values 584 │ └── () 585 └── projections 586 └── CASE WHEN NULL THEN 1 ELSE 2 END [as=case:1] 587 588 build 589 SELECT 0 * b AS r, b % 1 AS s, 0 % b AS t from abc 590 ---- 591 project 592 ├── columns: r:4 s:5 t:6 593 ├── scan abc 594 │ └── columns: a:1!null b:2 c:3 595 └── projections 596 ├── 0 * b:2 [as=r:4] 597 ├── b:2 % 1 [as=s:5] 598 └── 0 % b:2 [as=t:6] 599 600 # Regression tests for #22670. 601 build 602 SELECT 1 IN (1, 2) AS r 603 ---- 604 project 605 ├── columns: r:1!null 606 ├── values 607 │ └── () 608 └── projections 609 └── 1 IN (1, 2) [as=r:1] 610 611 build 612 SELECT NULL IN (1, 2) AS r 613 ---- 614 project 615 ├── columns: r:1 616 ├── values 617 │ └── () 618 └── projections 619 └── NULL IN (1, 2) [as=r:1] 620 621 build 622 SELECT 1 IN (NULL, 2) AS r 623 ---- 624 project 625 ├── columns: r:1 626 ├── values 627 │ └── () 628 └── projections 629 └── 1 IN (NULL, 2) [as=r:1] 630 631 build 632 SELECT (1, NULL) IN ((1, 1)) AS r 633 ---- 634 project 635 ├── columns: r:1 636 ├── values 637 │ └── () 638 └── projections 639 └── (1, NULL) IN ((1, 1),) [as=r:1] 640 641 # Tests with a tuple coming from a subquery. 642 build 643 SELECT NULL::int IN (SELECT * FROM (VALUES (1)) AS t(a)) AS r 644 ---- 645 project 646 ├── columns: r:2 647 ├── values 648 │ └── () 649 └── projections 650 └── any: eq [as=r:2] 651 ├── values 652 │ ├── columns: column1:1!null 653 │ └── (1,) 654 └── NULL::INT8 655 656 build 657 SELECT (1, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) AS r 658 ---- 659 project 660 ├── columns: r:4 661 ├── values 662 │ └── () 663 └── projections 664 └── any: eq [as=r:4] 665 ├── project 666 │ ├── columns: column3:3!null 667 │ ├── values 668 │ │ ├── columns: column1:1!null column2:2!null 669 │ │ └── (1, 1) 670 │ └── projections 671 │ └── (column1:1, column2:2) [as=column3:3] 672 └── (1, NULL::INT8) 673 674 build 675 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a)) AS r 676 ---- 677 project 678 ├── columns: r:2 679 ├── values 680 │ └── () 681 └── projections 682 └── not [as=r:2] 683 └── any: eq 684 ├── values 685 │ ├── columns: column1:1!null 686 │ └── (1,) 687 └── NULL::INT8 688 689 build 690 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) AS r 691 ---- 692 project 693 ├── columns: r:4 694 ├── values 695 │ └── () 696 └── projections 697 └── not [as=r:4] 698 └── any: eq 699 ├── project 700 │ ├── columns: column3:3!null 701 │ ├── values 702 │ │ ├── columns: column1:1!null column2:2!null 703 │ │ └── (1, 1) 704 │ └── projections 705 │ └── (column1:1, column2:2) [as=column3:3] 706 └── (1, NULL::INT8) 707 708 # Tests with an empty IN tuple. 709 build 710 SELECT NULL::int IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) AS r 711 ---- 712 project 713 ├── columns: r:2 714 ├── values 715 │ └── () 716 └── projections 717 └── any: eq [as=r:2] 718 ├── select 719 │ ├── columns: column1:1!null 720 │ ├── values 721 │ │ ├── columns: column1:1!null 722 │ │ └── (1,) 723 │ └── filters 724 │ └── column1:1 > 1 725 └── NULL::INT8 726 727 build 728 SELECT (1, NULL::int) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) AS r 729 ---- 730 project 731 ├── columns: r:4 732 ├── values 733 │ └── () 734 └── projections 735 └── any: eq [as=r:4] 736 ├── project 737 │ ├── columns: column3:3!null 738 │ ├── select 739 │ │ ├── columns: column1:1!null column2:2!null 740 │ │ ├── values 741 │ │ │ ├── columns: column1:1!null column2:2!null 742 │ │ │ └── (1, 1) 743 │ │ └── filters 744 │ │ └── column1:1 > 1 745 │ └── projections 746 │ └── (column1:1, column2:2) [as=column3:3] 747 └── (1, NULL::INT8) 748 749 build 750 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) AS r 751 ---- 752 project 753 ├── columns: r:2 754 ├── values 755 │ └── () 756 └── projections 757 └── not [as=r:2] 758 └── any: eq 759 ├── select 760 │ ├── columns: column1:1!null 761 │ ├── values 762 │ │ ├── columns: column1:1!null 763 │ │ └── (1,) 764 │ └── filters 765 │ └── column1:1 > 1 766 └── NULL::INT8 767 768 build 769 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) AS r 770 ---- 771 project 772 ├── columns: r:4 773 ├── values 774 │ └── () 775 └── projections 776 └── not [as=r:4] 777 └── any: eq 778 ├── project 779 │ ├── columns: column3:3!null 780 │ ├── select 781 │ │ ├── columns: column1:1!null column2:2!null 782 │ │ ├── values 783 │ │ │ ├── columns: column1:1!null column2:2!null 784 │ │ │ └── (1, 1) 785 │ │ └── filters 786 │ │ └── column1:1 > 1 787 │ └── projections 788 │ └── (column1:1, column2:2) [as=column3:3] 789 └── (1, NULL::INT8) 790 791 build 792 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) AS r 793 ---- 794 project 795 ├── columns: r:2 796 ├── values 797 │ └── () 798 └── projections 799 └── not [as=r:2] 800 └── any: eq 801 ├── select 802 │ ├── columns: column1:1!null 803 │ ├── values 804 │ │ ├── columns: column1:1!null 805 │ │ └── (1,) 806 │ └── filters 807 │ └── column1:1 > 1 808 └── NULL::INT8 809 810 build 811 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) AS r 812 ---- 813 project 814 ├── columns: r:4 815 ├── values 816 │ └── () 817 └── projections 818 └── not [as=r:4] 819 └── any: eq 820 ├── project 821 │ ├── columns: column3:3!null 822 │ ├── select 823 │ │ ├── columns: column1:1!null column2:2!null 824 │ │ ├── values 825 │ │ │ ├── columns: column1:1!null column2:2!null 826 │ │ │ └── (1, 1) 827 │ │ └── filters 828 │ │ └── column1:1 > 1 829 │ └── projections 830 │ └── (column1:1, column2:2) [as=column3:3] 831 └── (1, NULL::INT8) 832 833 build 834 SELECT NULL::int NOT IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) AS r 835 ---- 836 project 837 ├── columns: r:2 838 ├── values 839 │ └── () 840 └── projections 841 └── not [as=r:2] 842 └── any: eq 843 ├── select 844 │ ├── columns: column1:1!null 845 │ ├── values 846 │ │ ├── columns: column1:1!null 847 │ │ └── (1,) 848 │ └── filters 849 │ └── column1:1 > 1 850 └── NULL::INT8 851 852 build 853 SELECT (1, NULL::int) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) AS r 854 ---- 855 project 856 ├── columns: r:4 857 ├── values 858 │ └── () 859 └── projections 860 └── not [as=r:4] 861 └── any: eq 862 ├── project 863 │ ├── columns: column3:3!null 864 │ ├── select 865 │ │ ├── columns: column1:1!null column2:2!null 866 │ │ ├── values 867 │ │ │ ├── columns: column1:1!null column2:2!null 868 │ │ │ └── (1, 1) 869 │ │ └── filters 870 │ │ └── column1:1 > 1 871 │ └── projections 872 │ └── (column1:1, column2:2) [as=column3:3] 873 └── (1, NULL::INT8) 874 875 exec-ddl 876 CREATE TABLE a (x INT PRIMARY KEY, y FLOAT) 877 ---- 878 879 build 880 SELECT * FROM a 881 ---- 882 scan a 883 └── columns: x:1!null y:2 884 885 build 886 SELECT * FROM a WHERE x > 10 887 ---- 888 select 889 ├── columns: x:1!null y:2 890 ├── scan a 891 │ └── columns: x:1!null y:2 892 └── filters 893 └── x:1 > 10 894 895 build 896 SELECT * FROM a WHERE (x > 10 AND (x < 20 AND x != 13)) 897 ---- 898 select 899 ├── columns: x:1!null y:2 900 ├── scan a 901 │ └── columns: x:1!null y:2 902 └── filters 903 └── (x:1 > 10) AND ((x:1 < 20) AND (x:1 != 13)) 904 905 build 906 SELECT * FROM a WHERE x IN (1, 2, 3) 907 ---- 908 select 909 ├── columns: x:1!null y:2 910 ├── scan a 911 │ └── columns: x:1!null y:2 912 └── filters 913 └── x:1 IN (1, 2, 3) 914 915 build 916 SELECT * FROM a AS A(X, Y) 917 ---- 918 scan a 919 └── columns: x:1!null y:2 920 921 build 922 SELECT @1 AS r, @2 AS s FROM a 923 ---- 924 project 925 ├── columns: r:3!null s:4 926 ├── scan a 927 │ └── columns: x:1!null y:2 928 └── projections 929 ├── x:1 [as=r:3] 930 └── y:2 [as=s:4] 931 932 build 933 SELECT * FROM a WHERE (x > 10)::bool 934 ---- 935 select 936 ├── columns: x:1!null y:2 937 ├── scan a 938 │ └── columns: x:1!null y:2 939 └── filters 940 └── (x:1 > 10)::BOOL 941 942 build 943 SELECT * FROM a WHERE (x > 10)::INT[] 944 ---- 945 error (42846): invalid cast: bool -> int[] 946 947 build 948 SELECT * FROM a WHERE x = $1 949 ---- 950 select 951 ├── columns: x:1!null y:2 952 ├── scan a 953 │ └── columns: x:1!null y:2 954 └── filters 955 └── x:1 = $1 956 957 # This is slightly funky, because the AS OF SYSTEM TIME timestamp only gets 958 # interpreted by the executor, which obviously is not at play in these tests. 959 build 960 SELECT * FROM a AS OF SYSTEM TIME '-1000ms' 961 ---- 962 error (42601): AS OF SYSTEM TIME must be provided on a top-level statement 963 964 build 965 SELECT * FROM a AS t(a, b, c) 966 ---- 967 error (42P10): source "t" has 2 columns available but 3 columns specified 968 969 build 970 SELECT (x).e, (x).f, (x).g 971 FROM ( 972 SELECT ((1,'2',true) AS e,f,g) AS x 973 ) 974 ---- 975 project 976 ├── columns: e:2 f:3 g:4 977 ├── project 978 │ ├── columns: x:1!null 979 │ ├── values 980 │ │ └── () 981 │ └── projections 982 │ └── ((1, '2', true) AS e, f, g) [as=x:1] 983 └── projections 984 ├── (x:1).e [as=e:2] 985 ├── (x:1).f [as=f:3] 986 └── (x:1).g [as=g:4] 987 988 build 989 SELECT (((x, y) AS x, y)).x FROM a 990 ---- 991 project 992 ├── columns: x:1!null 993 └── scan a 994 └── columns: x:1!null y:2 995 996 997 # Numeric Reference Tests 998 # Cockroach numeric references start after 53 for user tables. 999 # See opt/testutils/testcat/create_table.go:117 for more info on 1000 # 53 as a magic number. 1001 1002 build 1003 SELECT * FROM [53 AS t] 1004 ---- 1005 scan t 1006 └── columns: a:1!null y:2 b:3 c:4 1007 1008 build 1009 SELECT * FROM [53(1) AS t] 1010 ---- 1011 scan t 1012 └── columns: a:1!null 1013 1014 build 1015 SELECT * FROM [53(1,2) AS t] 1016 ---- 1017 scan t 1018 └── columns: a:1!null y:2 1019 1020 build 1021 SELECT * FROM [53(4) AS t] 1022 ---- 1023 scan t 1024 └── columns: c:4 1025 1026 build 1027 SELECT * FROM [53(5) AS t] 1028 ---- 1029 error (42703): column [5] does not exist 1030 1031 build 1032 SELECT * FROM [53(2,4) AS t] 1033 ---- 1034 scan t 1035 └── columns: y:2 c:4 1036 1037 build 1038 SELECT * FROM [53(2,3) AS t(col1,col2)] 1039 ---- 1040 scan t 1041 └── columns: col1:2 col2:3 1042 1043 build 1044 SELECT * FROM [53() AS t] 1045 ---- 1046 error (42601): an explicit list of column IDs must include at least one column 1047 1048 # Test that hidden columns are not presented 1049 build 1050 SELECT * FROM [54 AS t] 1051 ---- 1052 project 1053 ├── columns: x:1 y:2 1054 └── scan t 1055 └── columns: x:1 y:2 rowid:3!null 1056 1057 # Verify that we force the given index. 1058 build 1059 SELECT * FROM [53 AS t]@[1] 1060 ---- 1061 scan t 1062 ├── columns: a:1!null y:2 b:3 c:4 1063 └── flags: force-index=primary 1064 1065 build 1066 SELECT * FROM [53 AS t]@[2] 1067 ---- 1068 scan t 1069 ├── columns: a:1!null y:2 b:3 c:4 1070 └── flags: force-index=bc 1071 1072 # Test that hidden columns are not presented. 1073 build 1074 SELECT * FROM [54(1,3) AS t] 1075 ---- 1076 project 1077 ├── columns: x:1 1078 └── scan t 1079 └── columns: x:1 rowid:3!null 1080 1081 build 1082 SELECT rowid FROM [54(3) as t] 1083 ---- 1084 scan t 1085 └── columns: rowid:3!null 1086 1087 1088 # Test that we don't error out due to check constraints that involve unselected 1089 # columns. 1090 build 1091 SELECT * FROM [55(1) as t(a)] 1092 ---- 1093 scan t 1094 ├── columns: a:1!null 1095 └── check constraint expressions 1096 └── (a:1 + b:2) < 10 1097 1098 # Regression test for #28388. Ensure that selecting from a table with no 1099 # columns does not cause a panic. 1100 exec-ddl 1101 CREATE TABLE no_cols_table () 1102 ---- 1103 1104 build 1105 SELECT * FROM no_cols_table 1106 ---- 1107 project 1108 └── scan no_cols_table 1109 └── columns: rowid:1!null 1110 1111 build 1112 SELECT * FROM [54(3) as t] 1113 ---- 1114 project 1115 └── scan t 1116 └── columns: rowid:3!null 1117 1118 # Non-referenced CTE with mutation. 1119 build 1120 WITH cte AS (SELECT b FROM [INSERT INTO abc VALUES (1) RETURNING *] LIMIT 1) SELECT * FROM abc 1121 ---- 1122 with &1 1123 ├── columns: a:9!null b:10 c:11 1124 ├── insert abc 1125 │ ├── columns: abc.a:1!null abc.b:2 abc.c:3 1126 │ ├── insert-mapping: 1127 │ │ ├── column1:4 => abc.a:1 1128 │ │ ├── column5:5 => abc.b:2 1129 │ │ └── column5:5 => abc.c:3 1130 │ └── project 1131 │ ├── columns: column5:5 column1:4!null 1132 │ ├── values 1133 │ │ ├── columns: column1:4!null 1134 │ │ └── (1,) 1135 │ └── projections 1136 │ └── NULL::INT8 [as=column5:5] 1137 └── with &2 (cte) 1138 ├── columns: abc.a:9!null abc.b:10 abc.c:11 1139 ├── limit 1140 │ ├── columns: b:7 1141 │ ├── project 1142 │ │ ├── columns: b:7 1143 │ │ ├── limit hint: 1.00 1144 │ │ └── with-scan &1 1145 │ │ ├── columns: a:6!null b:7 c:8 1146 │ │ ├── mapping: 1147 │ │ │ ├── abc.a:1 => a:6 1148 │ │ │ ├── abc.b:2 => b:7 1149 │ │ │ └── abc.c:3 => c:8 1150 │ │ └── limit hint: 1.00 1151 │ └── 1 1152 └── scan abc 1153 └── columns: abc.a:9!null abc.b:10 abc.c:11 1154 1155 # Tests for the square bracket syntax. 1156 build 1157 SELECT * FROM [SELECT * FROM abc] 1158 ---- 1159 with &1 1160 ├── columns: a:4!null b:5 c:6 1161 ├── scan abc 1162 │ └── columns: abc.a:1!null abc.b:2 abc.c:3 1163 └── with-scan &1 1164 ├── columns: a:4!null b:5 c:6 1165 └── mapping: 1166 ├── abc.a:1 => a:4 1167 ├── abc.b:2 => b:5 1168 └── abc.c:3 => c:6 1169 1170 build 1171 SELECT * FROM [INSERT INTO abc VALUES (1, 2, 3) RETURNING a] 1172 ---- 1173 with &1 1174 ├── columns: a:7!null 1175 ├── project 1176 │ ├── columns: abc.a:1!null 1177 │ └── insert abc 1178 │ ├── columns: abc.a:1!null b:2!null c:3!null 1179 │ ├── insert-mapping: 1180 │ │ ├── column1:4 => abc.a:1 1181 │ │ ├── column2:5 => b:2 1182 │ │ └── column3:6 => c:3 1183 │ └── values 1184 │ ├── columns: column1:4!null column2:5!null column3:6!null 1185 │ └── (1, 2, 3) 1186 └── with-scan &1 1187 ├── columns: a:7!null 1188 └── mapping: 1189 └── abc.a:1 => a:7 1190 1191 # Statement inside brackets cannot refer to outer column. 1192 build 1193 SELECT a, b FROM abc WHERE b = (SELECT x FROM [SELECT * FROM xyzw WHERE x = a]) 1194 ---- 1195 error (42703): column "a" does not exist 1196 1197 build 1198 SELECT a, b FROM abc, LATERAL (SELECT * FROM [SELECT * FROM xyzw WHERE a = x]) 1199 ---- 1200 error (42703): column "a" does not exist 1201 1202 # Statement inside brackets cannot refer to outer CTEs. 1203 build 1204 WITH cte AS (VALUES (1), (2)) 1205 SELECT * FROM (VALUES (3)) AS t (x), [SELECT * FROM cte] 1206 ---- 1207 error (42P01): no data source matches prefix: "cte" 1208 1209 # Projection list should still be able to refer to outer columns or CTEs. 1210 build 1211 WITH cte AS (SELECT 1) SELECT 1 + (SELECT * FROM cte) FROM [SELECT * from xyzw] 1212 ---- 1213 with &1 (cte) 1214 ├── columns: "?column?":11 1215 ├── project 1216 │ ├── columns: "?column?":1!null 1217 │ ├── values 1218 │ │ └── () 1219 │ └── projections 1220 │ └── 1 [as="?column?":1] 1221 └── with &2 1222 ├── columns: "?column?":11 1223 ├── scan xyzw 1224 │ └── columns: xyzw.x:2!null xyzw.y:3 xyzw.z:4 xyzw.w:5 1225 └── project 1226 ├── columns: "?column?":11 1227 ├── with-scan &2 1228 │ ├── columns: x:6!null y:7 z:8 w:9 1229 │ └── mapping: 1230 │ ├── xyzw.x:2 => x:6 1231 │ ├── xyzw.y:3 => y:7 1232 │ ├── xyzw.z:4 => z:8 1233 │ └── xyzw.w:5 => w:9 1234 └── projections 1235 └── plus [as="?column?":11] 1236 ├── 1 1237 └── subquery 1238 └── max1-row 1239 ├── columns: "?column?":10!null 1240 └── with-scan &1 (cte) 1241 ├── columns: "?column?":10!null 1242 └── mapping: 1243 └── "?column?":1 => "?column?":10 1244 1245 build 1246 SELECT a, (SELECT a+x FROM [SELECT * from xyzw]) FROM abc 1247 ---- 1248 with &1 1249 ├── columns: a:1!null "?column?":13 1250 ├── scan xyzw 1251 │ └── columns: xyzw.x:4!null xyzw.y:5 xyzw.z:6 xyzw.w:7 1252 └── project 1253 ├── columns: "?column?":13 a:1!null 1254 ├── scan abc 1255 │ └── columns: a:1!null b:2 c:3 1256 └── projections 1257 └── subquery [as="?column?":13] 1258 └── max1-row 1259 ├── columns: "?column?":12 1260 └── project 1261 ├── columns: "?column?":12 1262 ├── with-scan &1 1263 │ ├── columns: x:8!null y:9 z:10 w:11 1264 │ └── mapping: 1265 │ ├── xyzw.x:4 => x:8 1266 │ ├── xyzw.y:5 => y:9 1267 │ ├── xyzw.z:6 => z:10 1268 │ └── xyzw.w:7 => w:11 1269 └── projections 1270 └── a:1 + x:8 [as="?column?":12]