github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/tuple (about) 1 statement ok 2 CREATE TABLE tb(unused INT); INSERT INTO tb VALUES (1) 3 4 subtest empty_tuple 5 6 query B 7 SELECT 1 IN (SELECT * FROM tb LIMIT 0) 8 ---- 9 false 10 11 query B 12 SELECT 1 IN () 13 ---- 14 false 15 16 query B 17 SELECT 1 = ANY () 18 ---- 19 false 20 21 subtest unlabeled_tuple 22 23 # TODO(bram): We don't pretty print tuples the same way as postgres. See #25522. 24 query TT colnames 25 SELECT (1, 2, 'hello', NULL, NULL) AS t, (true, NULL, (false, 6.6, false)) AS u FROM tb 26 ---- 27 t u 28 (1,2,hello,,) (t,,"(f,6.6,f)") 29 30 query T 31 SELECT (1, e'hello\nworld') 32 ---- 33 (1,"hello 34 world") 35 36 query BBBBBBBBB colnames 37 SELECT 38 (2, 2) < (1, 1) AS a, 39 (2, 2) < (1, 2) AS b, 40 (2, 2) < (1, 3) AS c, 41 (2, 2) < (2, 1) AS d, 42 (2, 2) < (2, 2) AS e, 43 (2, 2) < (2, 3) AS f, 44 (2, 2) < (3, 1) AS g, 45 (2, 2) < (3, 2) AS h, 46 (2, 2) < (3, 3) AS i 47 FROM tb 48 ---- 49 a b c d e f g h i 50 false false false false false true true true true 51 52 query BBBBBBBBB colnames 53 SELECT 54 (2, 2) > (1, 1) AS a, 55 (2, 2) > (1, 2) AS b, 56 (2, 2) > (1, 3) AS c, 57 (2, 2) > (2, 1) AS d, 58 (2, 2) > (2, 2) AS e, 59 (2, 2) > (2, 3) AS f, 60 (2, 2) > (3, 1) AS g, 61 (2, 2) > (3, 2) AS h, 62 (2, 2) > (3, 3) AS i 63 FROM tb 64 ---- 65 a b c d e f g h i 66 true true true true false false false false false 67 68 query BBBBBBBBB colnames 69 SELECT 70 (2, 2) <= (1, 1) AS a, 71 (2, 2) <= (1, 2) AS b, 72 (2, 2) <= (1, 3) AS c, 73 (2, 2) <= (2, 1) AS d, 74 (2, 2) <= (2, 2) AS e, 75 (2, 2) <= (2, 3) AS f, 76 (2, 2) <= (3, 1) AS g, 77 (2, 2) <= (3, 2) AS h, 78 (2, 2) <= (3, 3) AS i 79 FROM tb 80 ---- 81 a b c d e f g h i 82 false false false false true true true true true 83 84 query BBBBBBBBB colnames 85 SELECT 86 (2, 2) >= (1, 1) AS a, 87 (2, 2) >= (1, 2) AS b, 88 (2, 2) >= (1, 3) AS c, 89 (2, 2) >= (2, 1) AS d, 90 (2, 2) >= (2, 2) AS e, 91 (2, 2) >= (2, 3) AS f, 92 (2, 2) >= (3, 1) AS g, 93 (2, 2) >= (3, 2) AS h, 94 (2, 2) >= (3, 3) AS i 95 FROM tb 96 ---- 97 a b c d e f g h i 98 true true true true true false false false false 99 100 query BBBBBBBBB colnames 101 SELECT 102 (2, 2) = (1, 1) AS a, 103 (2, 2) = (1, 2) AS b, 104 (2, 2) = (1, 3) AS c, 105 (2, 2) = (2, 1) AS d, 106 (2, 2) = (2, 2) AS e, 107 (2, 2) = (2, 3) AS f, 108 (2, 2) = (3, 1) AS g, 109 (2, 2) = (3, 2) AS h, 110 (2, 2) = (3, 3) AS i 111 FROM tb 112 ---- 113 a b c d e f g h i 114 false false false false true false false false false 115 116 query BBBBBBBBB colnames 117 SELECT 118 (2, 2) != (1, 1) AS a, 119 (2, 2) != (1, 2) AS b, 120 (2, 2) != (1, 3) AS c, 121 (2, 2) != (2, 1) AS d, 122 (2, 2) != (2, 2) AS e, 123 (2, 2) != (2, 3) AS f, 124 (2, 2) != (3, 1) AS g, 125 (2, 2) != (3, 2) AS h, 126 (2, 2) != (3, 3) AS i 127 FROM tb 128 ---- 129 a b c d e f g h i 130 true true true true false true true true true 131 132 query BBBB colnames 133 SELECT 134 (1, 1) > (0, NULL) AS a, 135 (1, 1) > (1, NULL) AS b, 136 (1, 1) > (2, NULL) AS c, 137 (1, 1) > (NULL, 0) AS d 138 FROM tb 139 ---- 140 a b c d 141 true NULL false NULL 142 143 statement error pq: tuples \(1, 2\), \(1, 'hi'\) are not comparable at index 2: unsupported comparison operator 144 SELECT (1, 2) > (1, 'hi') FROM tb 145 146 statement error pq: expected tuple \(1, 2, 3\) to have a length of 2 147 SELECT (1, 2) > (1, 2, 3) FROM tb 148 149 statement ok 150 CREATE TABLE t (a int, b int, c int) 151 152 statement ok 153 INSERT INTO t VALUES (1, 2, 3), (2, 3, 1), (3, 1, 2) 154 155 query III colnames 156 SELECT * FROM t ORDER BY a, b, c 157 ---- 158 a b c 159 1 2 3 160 2 3 1 161 3 1 2 162 163 query III colnames 164 SELECT * FROM t WHERE (a, b, c) > (1, 2, 3) AND (a, b, c) < (8, 9, 10) ORDER BY a, b, c 165 ---- 166 a b c 167 2 3 1 168 3 1 2 169 170 query T colnames,rowsort 171 SELECT (t.*) AS a FROM t 172 ---- 173 a 174 (2,3,1) 175 (3,1,2) 176 (1,2,3) 177 178 query BB colnames 179 SELECT ((1, 2), 'equal') = ((1, 2.0), 'equal') AS a, 180 ((1, 2), 'equal') = ((1, 2.0), 'not equal') AS b 181 FROM tb 182 ---- 183 a b 184 true false 185 186 query B colnames 187 SELECT ((1, 2), 'equal') = ((1, 2.1), 'equal') AS a 188 FROM tb 189 ---- 190 a 191 false 192 193 query B colnames 194 SELECT (ROW(pow(1, 10.0) + 9), 'a' || 'b') = (ROW(sqrt(100.0)), 'ab') AS a 195 FROM tb 196 ---- 197 a 198 true 199 200 query B colnames 201 SELECT (ROW(sqrt(100.0)), 'ab') = (ROW(pow(1, 10.0) + 9), 'a' || 'b') AS a 202 FROM tb 203 ---- 204 a 205 true 206 207 query error pq: tuples \(\(1, 2\), 'equal'\), \(\(1, 'huh'\), 'equal'\) are not comparable at index 1: tuples \(1, 2\), \(1, 'huh'\) are not comparable at index 2: unsupported comparison operator 208 SELECT ((1, 2), 'equal') = ((1, 'huh'), 'equal') FROM tb 209 210 # Issue #3568 211 212 statement ok 213 CREATE TABLE kv ( 214 k INT PRIMARY KEY, 215 v INT 216 ) 217 218 statement ok 219 INSERT INTO kv VALUES (1, 2) 220 221 query II colnames 222 SELECT k, v FROM kv WHERE (k, v) = (1, 100) 223 ---- 224 k v 225 226 query II colnames 227 SELECT k, v FROM kv WHERE (k, v) IN ((1, 100)) 228 ---- 229 k v 230 231 statement ok 232 DROP TABLE kv 233 234 # Issue #12295 235 236 query B colnames 237 SELECT 'foo' IN (x, 'aaa') AS r FROM (SELECT 'foo' AS x FROM tb) 238 ---- 239 r 240 true 241 242 query B colnames 243 SELECT 'foo' IN (x, 'zzz') AS r FROM (SELECT 'foo' AS x FROM tb) 244 ---- 245 r 246 true 247 248 # Subquery tuples are already sorted 249 250 query B colnames 251 SELECT 3 IN (SELECT c FROM t ORDER BY 1 ASC) AS r 252 ---- 253 r 254 true 255 256 query B colnames 257 SELECT 4 IN (SELECT c FROM t ORDER BY 1 DESC) AS r 258 ---- 259 r 260 false 261 262 query B colnames 263 SELECT (1, 2) IN (SELECT a, b FROM t ORDER BY 1 ASC, 2 ASC) AS r 264 ---- 265 r 266 true 267 268 query B colnames 269 SELECT (1, 2) IN (SELECT a, b FROM t ORDER BY 1 DESC, 2 DESC) AS r 270 ---- 271 r 272 true 273 274 statement ok 275 DROP TABLE t 276 277 # Issue #12302 278 279 query B colnames 280 SELECT 1 IN (2, NULL) AS r 281 FROM tb 282 ---- 283 r 284 NULL 285 286 query B colnames 287 SELECT 1 IN (2, x) AS r FROM (SELECT NULL AS x FROM tb) 288 ---- 289 r 290 NULL 291 292 # Issue 10407: tuple comparisons should not require homogeneous types 293 query B colnames 294 SELECT (now(), 2) = (now() :: timestamp, 2) AS r 295 FROM tb 296 ---- 297 r 298 true 299 300 query B colnames 301 SELECT (1, 2) > (1.0, 2.0) AS r 302 FROM tb 303 ---- 304 r 305 false 306 307 statement ok 308 CREATE TABLE uvw ( 309 u INT, 310 v INT, 311 w INT, 312 INDEX (u,v,w) 313 ) 314 315 statement ok 316 INSERT INTO uvw SELECT u, v, w FROM 317 generate_series(0, 3) AS u, 318 generate_series(0, 3) AS v, 319 generate_series(0, 3) AS w; 320 UPDATE uvw SET u = NULL WHERE u = 0; 321 UPDATE uvw SET v = NULL WHERE v = 0; 322 UPDATE uvw SET w = NULL WHERE w = 0 323 324 query III colnames 325 SELECT * FROM uvw ORDER BY u, v, w 326 ---- 327 u v w 328 NULL NULL NULL 329 NULL NULL 1 330 NULL NULL 2 331 NULL NULL 3 332 NULL 1 NULL 333 NULL 1 1 334 NULL 1 2 335 NULL 1 3 336 NULL 2 NULL 337 NULL 2 1 338 NULL 2 2 339 NULL 2 3 340 NULL 3 NULL 341 NULL 3 1 342 NULL 3 2 343 NULL 3 3 344 1 NULL NULL 345 1 NULL 1 346 1 NULL 2 347 1 NULL 3 348 1 1 NULL 349 1 1 1 350 1 1 2 351 1 1 3 352 1 2 NULL 353 1 2 1 354 1 2 2 355 1 2 3 356 1 3 NULL 357 1 3 1 358 1 3 2 359 1 3 3 360 2 NULL NULL 361 2 NULL 1 362 2 NULL 2 363 2 NULL 3 364 2 1 NULL 365 2 1 1 366 2 1 2 367 2 1 3 368 2 2 NULL 369 2 2 1 370 2 2 2 371 2 2 3 372 2 3 NULL 373 2 3 1 374 2 3 2 375 2 3 3 376 3 NULL NULL 377 3 NULL 1 378 3 NULL 2 379 3 NULL 3 380 3 1 NULL 381 3 1 1 382 3 1 2 383 3 1 3 384 3 2 NULL 385 3 2 1 386 3 2 2 387 3 2 3 388 3 3 NULL 389 3 3 1 390 3 3 2 391 3 3 3 392 393 query III colnames 394 SELECT * FROM uvw WHERE (u, v, w) >= (1, 2, 3) ORDER BY u, v, w 395 ---- 396 u v w 397 1 2 3 398 1 3 NULL 399 1 3 1 400 1 3 2 401 1 3 3 402 2 NULL NULL 403 2 NULL 1 404 2 NULL 2 405 2 NULL 3 406 2 1 NULL 407 2 1 1 408 2 1 2 409 2 1 3 410 2 2 NULL 411 2 2 1 412 2 2 2 413 2 2 3 414 2 3 NULL 415 2 3 1 416 2 3 2 417 2 3 3 418 3 NULL NULL 419 3 NULL 1 420 3 NULL 2 421 3 NULL 3 422 3 1 NULL 423 3 1 1 424 3 1 2 425 3 1 3 426 3 2 NULL 427 3 2 1 428 3 2 2 429 3 2 3 430 3 3 NULL 431 3 3 1 432 3 3 2 433 3 3 3 434 435 query III colnames 436 SELECT * FROM uvw WHERE (u, v, w) > (2, 1, 1) ORDER BY u, v, w 437 ---- 438 u v w 439 2 1 2 440 2 1 3 441 2 2 NULL 442 2 2 1 443 2 2 2 444 2 2 3 445 2 3 NULL 446 2 3 1 447 2 3 2 448 2 3 3 449 3 NULL NULL 450 3 NULL 1 451 3 NULL 2 452 3 NULL 3 453 3 1 NULL 454 3 1 1 455 3 1 2 456 3 1 3 457 3 2 NULL 458 3 2 1 459 3 2 2 460 3 2 3 461 3 3 NULL 462 3 3 1 463 3 3 2 464 3 3 3 465 466 query III colnames 467 SELECT * FROM uvw WHERE (u, v, w) <= (2, 3, 1) ORDER BY u, v, w 468 ---- 469 u v w 470 1 NULL NULL 471 1 NULL 1 472 1 NULL 2 473 1 NULL 3 474 1 1 NULL 475 1 1 1 476 1 1 2 477 1 1 3 478 1 2 NULL 479 1 2 1 480 1 2 2 481 1 2 3 482 1 3 NULL 483 1 3 1 484 1 3 2 485 1 3 3 486 2 1 NULL 487 2 1 1 488 2 1 2 489 2 1 3 490 2 2 NULL 491 2 2 1 492 2 2 2 493 2 2 3 494 2 3 1 495 496 query III colnames 497 SELECT * FROM uvw WHERE (u, v, w) < (2, 2, 2) ORDER BY u, v, w 498 ---- 499 u v w 500 1 NULL NULL 501 1 NULL 1 502 1 NULL 2 503 1 NULL 3 504 1 1 NULL 505 1 1 1 506 1 1 2 507 1 1 3 508 1 2 NULL 509 1 2 1 510 1 2 2 511 1 2 3 512 1 3 NULL 513 1 3 1 514 1 3 2 515 1 3 3 516 2 1 NULL 517 2 1 1 518 2 1 2 519 2 1 3 520 2 2 1 521 522 query III colnames 523 SELECT * FROM uvw WHERE (u, v, w) != (1, 2, 3) ORDER BY u, v, w 524 ---- 525 u v w 526 NULL NULL 1 527 NULL NULL 2 528 NULL 1 NULL 529 NULL 1 1 530 NULL 1 2 531 NULL 1 3 532 NULL 2 1 533 NULL 2 2 534 NULL 3 NULL 535 NULL 3 1 536 NULL 3 2 537 NULL 3 3 538 1 NULL 1 539 1 NULL 2 540 1 1 NULL 541 1 1 1 542 1 1 2 543 1 1 3 544 1 2 1 545 1 2 2 546 1 3 NULL 547 1 3 1 548 1 3 2 549 1 3 3 550 2 NULL NULL 551 2 NULL 1 552 2 NULL 2 553 2 NULL 3 554 2 1 NULL 555 2 1 1 556 2 1 2 557 2 1 3 558 2 2 NULL 559 2 2 1 560 2 2 2 561 2 2 3 562 2 3 NULL 563 2 3 1 564 2 3 2 565 2 3 3 566 3 NULL NULL 567 3 NULL 1 568 3 NULL 2 569 3 NULL 3 570 3 1 NULL 571 3 1 1 572 3 1 2 573 3 1 3 574 3 2 NULL 575 3 2 1 576 3 2 2 577 3 2 3 578 3 3 NULL 579 3 3 1 580 3 3 2 581 3 3 3 582 583 query III colnames 584 SELECT * FROM uvw WHERE (u, v, w) >= (1, NULL, 3) ORDER BY u, v, w 585 ---- 586 u v w 587 2 NULL NULL 588 2 NULL 1 589 2 NULL 2 590 2 NULL 3 591 2 1 NULL 592 2 1 1 593 2 1 2 594 2 1 3 595 2 2 NULL 596 2 2 1 597 2 2 2 598 2 2 3 599 2 3 NULL 600 2 3 1 601 2 3 2 602 2 3 3 603 3 NULL NULL 604 3 NULL 1 605 3 NULL 2 606 3 NULL 3 607 3 1 NULL 608 3 1 1 609 3 1 2 610 3 1 3 611 3 2 NULL 612 3 2 1 613 3 2 2 614 3 2 3 615 3 3 NULL 616 3 3 1 617 3 3 2 618 3 3 3 619 620 query III colnames 621 SELECT * FROM uvw WHERE (u, v, w) < (2, NULL, 3) ORDER BY u, v, w 622 ---- 623 u v w 624 1 NULL NULL 625 1 NULL 1 626 1 NULL 2 627 1 NULL 3 628 1 1 NULL 629 1 1 1 630 1 1 2 631 1 1 3 632 1 2 NULL 633 1 2 1 634 1 2 2 635 1 2 3 636 1 3 NULL 637 1 3 1 638 1 3 2 639 1 3 3 640 641 statement ok 642 DROP TABLE uvw 643 644 subtest tuple_placeholders 645 646 statement ok 647 PREPARE x AS SELECT $1 = (1,2) AS r FROM tb 648 649 statement ok 650 PREPARE y AS SELECT (1,2) = $1 AS r FROM tb 651 652 query B colnames 653 EXECUTE x((1,2)) 654 ---- 655 r 656 true 657 658 query B colnames 659 EXECUTE y((1,2)) 660 ---- 661 r 662 true 663 664 query error expected EXECUTE parameter expression to have type tuple\{int, int\}, but '\(1, 2, 3\)' has type tuple\{int, int, int\} 665 EXECUTE x((1,2,3)) 666 667 subtest labeled_tuple 668 669 # Selecting two tuples 670 query TT colnames 671 SELECT ((1, 2, 'hello', NULL, NULL) AS a1, b2, c3, d4, e5) AS r, 672 ((true, NULL, (false, 6.6, false)) AS a1, b2, c3) AS s 673 FROM tb 674 ---- 675 r s 676 (1,2,hello,,) (t,,"(f,6.6,f)") 677 678 # Duplicate tuple labels are allowed (but access fails when a duplicated label is accessed, 679 # see the labeled_tuple_column_access_errors subtest) 680 query T colnames 681 SELECT ((1, '2') AS a, a) FROM tb 682 ---- 683 ?column? 684 (1,2) 685 686 query T 687 SELECT ((1, '2', true) AS a, a, b) FROM tb 688 ---- 689 (1,2,t) 690 691 query T 692 SELECT ((1, '2', true) AS a, b, a) FROM tb 693 ---- 694 (1,2,t) 695 696 query T 697 SELECT ((1, 'asd', true) AS b, a, a) FROM tb 698 ---- 699 (1,asd,t) 700 701 query TT colnames 702 SELECT ((1, 2, 'hello', NULL, NULL) AS a, a, a, a, a) AS r, 703 ((true, NULL, (false, 6.6, false)) AS a, a, a) AS s 704 FROM tb 705 ---- 706 r s 707 (1,2,hello,,) (t,,"(f,6.6,f)") 708 709 # Comparing tuples 710 query BBB colnames 711 SELECT ((2, 2) AS a, b) < ((1, 1) AS c, d) AS r 712 ,((2, 2) AS a, b) < (1, 2) AS s 713 ,(2, 2) < ((1, 3) AS c, d) AS t 714 FROM tb 715 ---- 716 r s t 717 false false false 718 719 statement error pq: tuples \(\(1, 2\) AS a, b\), \(\(1, 'hi'\) AS c, d\) are not comparable at index 2: unsupported comparison operator: <int> > <string> 720 SELECT ((1, 2) AS a, b) > ((1, 'hi') AS c, d) FROM tb 721 722 statement error pq: expected tuple \(\(1, 2, 3\) AS a, b, c\) to have a length of 2 723 SELECT ((1, 2) AS a, b, c) > ((1, 2, 3) AS a, b, c) FROM tb 724 725 query BBBBBBBBBBBBBBBB colnames 726 SELECT ((((1, 2) AS a, b), 'value') AS c, d) = ((((1, 2) AS e, f), 'value') AS g, h) AS nnnn 727 ,((((1, 2) AS a, b), 'value') AS c, d) = (((1, 2) AS e, f), 'value') AS nnnu 728 ,((((1, 2) AS a, b), 'value') AS c, d) = (((1, 2), 'value') AS g, h) AS nnun 729 ,((((1, 2) AS a, b), 'value') AS c, d) = ((1, 2), 'value') AS nnuu 730 ,(((1, 2) AS a, b), 'value') = ((((1, 2) AS e, f), 'value') AS g, h) AS nunn 731 ,(((1, 2) AS a, b), 'value') = (((1, 2) AS e, f), 'value') AS nunu 732 ,(((1, 2) AS a, b), 'value') = (((1, 2), 'value') AS g, h) AS nuun 733 ,(((1, 2) AS a, b), 'value') = ((1, 2), 'value') AS nuuu 734 ,(((1, 2), 'value') AS c, d) = ((((1, 2) AS e, f), 'value') AS g, h) AS unnn 735 ,(((1, 2), 'value') AS c, d) = (((1, 2) AS e, f), 'value') AS unnu 736 ,(((1, 2), 'value') AS c, d) = (((1, 2), 'value') AS g, h) AS unun 737 ,(((1, 2), 'value') AS c, d) = ((1, 2), 'value') AS unuu 738 ,((1, 2), 'value') = ((((1, 2) AS e, f), 'value') AS g, h) AS uunn 739 ,((1, 2), 'value') = (((1, 2) AS e, f), 'value') AS uunu 740 ,((1, 2), 'value') = (((1, 2), 'value') AS g, h) AS uuun 741 ,((1, 2), 'value') = ((1, 2), 'value') AS uuuu 742 FROM tb 743 ---- 744 nnnn nnnu nnun nnuu nunn nunu nuun nuuu unnn unnu unun unuu uunn uunu uuun uuuu 745 true true true true true true true true true true true true true true true true 746 747 query BB colnames 748 SELECT (((ROW(pow(1, 10.0) + 9) AS t1), 'a' || 'b') AS t2, t3) = (((ROW(sqrt(100.0)) AS t4), 'ab') AS t5, t6) AS a 749 ,(ROW(pow(1, 10.0) + 9), 'a' || 'b') = (((ROW(sqrt(100.0)) AS t4), 'ab') AS t5, t6) AS b 750 FROM tb 751 ---- 752 a b 753 true true 754 755 subtest labeled_tuple_errors 756 757 query error pq: tuples \(\(\(\(1, 2\) AS a, b\), 'equal'\) AS c, d\), \(\(\(\(1, 'huh'\) AS e, f\), 'equal'\) AS g, h\) are not comparable at index 1: tuples \(\(1, 2\) AS a, b\), \(\(1, 'huh'\) AS e, f\) are not comparable at index 2: unsupported comparison operator: <int> = <string> 758 SELECT ((((1, 2) AS a, b), 'equal') AS c, d) = ((((1, 'huh') AS e, f), 'equal') AS g, h) FROM tb 759 760 # Ensure the number of labels matches the number of expressions 761 query error pq: mismatch in tuple definition: 2 expressions, 1 labels 762 SELECT ((1, '2') AS a) FROM tb 763 764 query error pq: mismatch in tuple definition: 1 expressions, 2 labels 765 SELECT (ROW(1) AS a, b) FROM tb 766 767 # But inner tuples can reuse labels 768 query T colnames 769 SELECT (( 770 ( 771 (((1, '2', 3) AS a, b, c), 772 ((4,'5') AS a, b), 773 (ROW(6) AS a)) 774 AS a, b, c), 775 ((7, 8) AS a, b), 776 (ROW('9') AS a)) 777 AS a, b, c 778 ) AS r 779 FROM tb 780 ---- 781 r 782 ("(""(1,2,3)"",""(4,5)"",""(6)"")","(7,8)","(9)") 783 784 subtest labeled_tuple_column_access 785 786 ## base working case 787 788 # Accessing a specific column 789 query error pq: could not identify column "x" in tuple{int AS a, int AS b, int AS c} 790 SELECT (((1,2,3) AS a,b,c)).x FROM tb 791 792 query ITBITB colnames 793 SELECT (((1,'2',true) AS a,b,c)).a 794 ,(((1,'2',true) AS a,b,c)).b 795 ,(((1,'2',true) AS a,b,c)).c 796 ,((ROW(1,'2',true) AS a,b,c)).a 797 ,((ROW(1,'2',true) AS a,b,c)).b 798 ,((ROW(1,'2',true) AS a,b,c)).c 799 FROM tb 800 ---- 801 a b c a b c 802 1 2 true 1 2 true 803 804 subtest labeled_tuple_column_access_errors 805 806 # column doesn't exist 807 query error pq: could not identify column "x" in tuple{int AS a, int AS b, int AS c} 808 SELECT (((1,2,3) AS a,b,c)).x FROM tb 809 810 # Missing extra parentheses 811 query error at or near ".": syntax error 812 SELECT ((1,2,3) AS a,b,c).x FROM tb 813 814 query error at or near ".": syntax error 815 SELECT ((1,2,3) AS a,b,c).* FROM tb 816 817 # Accessing duplicate labels 818 query error pq: column reference "a" is ambiguous 819 SELECT (((1,2,3) AS a,b,a)).a FROM tb 820 821 query error pq: column reference "unnest" is ambiguous 822 SELECT ((unnest(ARRAY[1,2], ARRAY[1,2]))).unnest; 823 824 # No labels 825 query error pq: type tuple{int, int, int} is not composite 826 SELECT ((1,2,3)).x FROM tb 827 828 query I colnames 829 SELECT ((1,2,3)).@2 FROM tb 830 ---- 831 ?column? 832 2 833 834 query III colnames 835 SELECT ((1,2,3)).* FROM tb 836 ---- 837 ?column? ?column? ?column? 838 1 2 3 839 840 # Accessing all the columns 841 842 query ITB colnames 843 SELECT (((1,'2',true) AS a,b,c)).* FROM tb 844 ---- 845 a b c 846 1 2 true 847 848 query ITB colnames 849 SELECT ((ROW(1,'2',true) AS a,b,c)).* FROM tb 850 ---- 851 a b c 852 1 2 true 853 854 query T 855 SELECT (((ROW(1,'2',true) AS a,b,c)).*, 456) FROM tb 856 ---- 857 ("(1,2,t)",456) 858 859 query I colnames 860 SELECT ((ROW(1) AS a)).* FROM tb 861 ---- 862 a 863 1 864 865 866 subtest literal_labeled_tuple_in_subquery 867 868 query ITB colnames 869 SELECT (x).e, (x).f, (x).g 870 FROM ( 871 SELECT ((1,'2',true) AS e,f,g) AS x FROM tb 872 ) 873 ---- 874 e f g 875 1 2 true 876 877 query ITB colnames 878 SELECT (x).* 879 FROM ( 880 SELECT ((1,'2',true) AS e,f,g) AS x FROM tb 881 ) 882 ---- 883 e f g 884 1 2 true 885 886 subtest labeled_tuples_derived_from_relational_subquery_schema 887 888 query IT 889 SELECT (x).a, (x).b 890 FROM (SELECT (ROW(a, b) AS a, b) AS x FROM (VALUES (1, 'one')) AS t(a, b)) 891 ---- 892 1 one 893 894 statement ok 895 CREATE TABLE t (a int, b string) 896 897 statement ok 898 INSERT INTO t VALUES (1, 'one'), (2, 'two') 899 900 query IT 901 SELECT (x).a, (x).b 902 FROM (SELECT (ROW(a, b) AS a, b) AS x FROM t) 903 ORDER BY 1 904 LIMIT 1 905 ---- 906 1 one 907 908 subtest labeled_column_access_from_table 909 910 query IT colnames 911 SELECT (t.*).* FROM t ORDER BY 1,2 912 ---- 913 a b 914 1 one 915 2 two 916 917 918 # Pending #26719 919 query error pq: column "t" does not exist 920 SELECT (t).a FROM t 921 922 statement ok 923 DROP TABLE t 924 925 query B 926 SELECT (1, 2, 3) IS NULL AS r 927 ---- 928 false 929 930 subtest regression_for_34262 931 932 query B 933 SELECT () = () 934 ---- 935 true