github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/join (about) 1 # The join condition logic is tricky to get right with NULL 2 # values. Simple implementations can deal well with NULLs on the first 3 # or last row but fail to handle them in the middle. So the test table 4 # must contain at least 3 rows with a null in the middle. This test 5 # table also contains the pair 44/42 so that a test with a non-trivial 6 # ON condition can be written. 7 statement ok 8 CREATE TABLE onecolumn (x INT); INSERT INTO onecolumn(x) VALUES (44), (NULL), (42) 9 10 query II colnames,rowsort 11 SELECT * FROM onecolumn AS a(x) CROSS JOIN onecolumn AS b(y) 12 ---- 13 x y 14 44 44 15 44 NULL 16 44 42 17 NULL 44 18 NULL NULL 19 NULL 42 20 42 44 21 42 NULL 22 42 42 23 24 # Check that name resolution chokes on ambiguity when it needs to. 25 query error column reference "x" is ambiguous \(candidates: a.x, b.x\) 26 SELECT x FROM onecolumn AS a, onecolumn AS b 27 28 query II colnames,rowsort 29 SELECT * FROM onecolumn AS a(x) JOIN onecolumn AS b(y) ON a.x = b.y 30 ---- 31 x y 32 44 44 33 42 42 34 35 query I colnames 36 SELECT * FROM onecolumn AS a JOIN onecolumn as b USING(x) ORDER BY x 37 ---- 38 x 39 42 40 44 41 42 query I colnames,rowsort 43 SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn as b 44 ---- 45 x 46 44 47 42 48 49 query II colnames,rowsort 50 SELECT * FROM onecolumn AS a(x) LEFT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 51 ---- 52 x y 53 44 44 54 NULL NULL 55 42 42 56 57 query I colnames 58 SELECT * FROM onecolumn AS a LEFT OUTER JOIN onecolumn AS b USING(x) ORDER BY x 59 ---- 60 x 61 NULL 62 42 63 44 64 65 # Check that ORDER BY chokes on ambiguity if no table less columns 66 # were introduced by USING. (#12239) 67 query error ORDER BY "x" is ambiguous 68 SELECT * FROM onecolumn AS a, onecolumn AS b ORDER BY x 69 70 query I colnames,rowsort 71 SELECT * FROM onecolumn AS a NATURAL LEFT OUTER JOIN onecolumn AS b 72 ---- 73 x 74 44 75 NULL 76 42 77 78 query II colnames,rowsort 79 SELECT * FROM onecolumn AS a(x) RIGHT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 80 ---- 81 x y 82 44 44 83 42 42 84 NULL NULL 85 86 query I colnames 87 SELECT * FROM onecolumn AS a RIGHT OUTER JOIN onecolumn AS b USING(x) ORDER BY x 88 ---- 89 x 90 NULL 91 42 92 44 93 94 query I colnames,rowsort 95 SELECT * FROM onecolumn AS a NATURAL RIGHT OUTER JOIN onecolumn AS b 96 ---- 97 x 98 44 99 42 100 NULL 101 102 statement ok 103 CREATE TABLE onecolumn_w(w INT); INSERT INTO onecolumn_w(w) VALUES (42),(43) 104 105 query II colnames,rowsort 106 SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn_w as b 107 ---- 108 x w 109 44 42 110 44 43 111 NULL 42 112 NULL 43 113 42 42 114 42 43 115 116 statement ok 117 CREATE TABLE othercolumn (x INT); INSERT INTO othercolumn(x) VALUES (43),(42),(16) 118 119 query II colnames 120 SELECT * FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b ON a.x = b.x ORDER BY a.x,b.x 121 ---- 122 x x 123 NULL NULL 124 NULL 16 125 NULL 43 126 42 42 127 44 NULL 128 129 query I colnames 130 SELECT * FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b USING(x) ORDER BY x 131 ---- 132 x 133 NULL 134 16 135 42 136 43 137 44 138 139 # Check that the source columns can be selected separately from the 140 # USING column (#12033). 141 query III colnames 142 SELECT x AS s, a.x, b.x FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b USING(x) ORDER BY s 143 ---- 144 s x x 145 NULL NULL NULL 146 16 NULL 16 147 42 42 42 148 43 NULL 43 149 44 44 NULL 150 151 query I colnames 152 SELECT * FROM onecolumn AS a NATURAL FULL OUTER JOIN othercolumn AS b ORDER BY x 153 ---- 154 x 155 NULL 156 16 157 42 158 43 159 44 160 161 # Check that a limit on the JOIN's result do not cause rows from the 162 # JOIN operands to become invisible to the JOIN. 163 query I colnames 164 SELECT * FROM (SELECT x FROM onecolumn ORDER BY x DESC) NATURAL JOIN (VALUES (42)) AS v(x) LIMIT 1 165 ---- 166 x 167 42 168 169 statement ok 170 CREATE TABLE empty (x INT) 171 172 query II 173 SELECT * FROM onecolumn AS a(x) CROSS JOIN empty AS b(y) 174 ---- 175 176 query II 177 SELECT * FROM empty AS a CROSS JOIN onecolumn AS b 178 ---- 179 180 query II 181 SELECT * FROM onecolumn AS a(x) JOIN empty AS b(y) ON a.x = b.y 182 ---- 183 184 query I 185 SELECT * FROM onecolumn AS a JOIN empty AS b USING(x) 186 ---- 187 188 query II 189 SELECT * FROM empty AS a(x) JOIN onecolumn AS b(y) ON a.x = b.y 190 ---- 191 192 query I 193 SELECT * FROM empty AS a JOIN onecolumn AS b USING(x) 194 ---- 195 196 query II colnames 197 SELECT * FROM onecolumn AS a(x) LEFT OUTER JOIN empty AS b(y) ON a.x = b.y ORDER BY a.x 198 ---- 199 x y 200 NULL NULL 201 42 NULL 202 44 NULL 203 204 query I colnames 205 SELECT * FROM onecolumn AS a LEFT OUTER JOIN empty AS b USING(x) ORDER BY x 206 ---- 207 x 208 NULL 209 42 210 44 211 212 query II 213 SELECT * FROM empty AS a(x) LEFT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 214 ---- 215 216 query I 217 SELECT * FROM empty AS a LEFT OUTER JOIN onecolumn AS b USING(x) 218 ---- 219 220 query II 221 SELECT * FROM onecolumn AS a(x) RIGHT OUTER JOIN empty AS b(y) ON a.x = b.y 222 ---- 223 224 query I 225 SELECT * FROM onecolumn AS a RIGHT OUTER JOIN empty AS b USING(x) 226 ---- 227 228 query II colnames 229 SELECT * FROM empty AS a(x) FULL OUTER JOIN onecolumn AS b(y) ON a.x = b.y ORDER BY b.y 230 ---- 231 x y 232 NULL NULL 233 NULL 42 234 NULL 44 235 236 query I colnames 237 SELECT * FROM empty AS a FULL OUTER JOIN onecolumn AS b USING(x) ORDER BY x 238 ---- 239 x 240 NULL 241 42 242 44 243 244 query II colnames 245 SELECT * FROM onecolumn AS a(x) FULL OUTER JOIN empty AS b(y) ON a.x = b.y ORDER BY a.x 246 ---- 247 x y 248 NULL NULL 249 42 NULL 250 44 NULL 251 252 query I colnames 253 SELECT * FROM onecolumn AS a FULL OUTER JOIN empty AS b USING(x) ORDER BY x 254 ---- 255 x 256 NULL 257 42 258 44 259 260 query II colnames 261 SELECT * FROM empty AS a(x) FULL OUTER JOIN onecolumn AS b(y) ON a.x = b.y ORDER BY b.y 262 ---- 263 x y 264 NULL NULL 265 NULL 42 266 NULL 44 267 268 query I colnames 269 SELECT * FROM empty AS a FULL OUTER JOIN onecolumn AS b USING(x) ORDER BY x 270 ---- 271 x 272 NULL 273 42 274 44 275 276 statement ok 277 CREATE TABLE twocolumn (x INT, y INT); INSERT INTO twocolumn(x, y) VALUES (44,51), (NULL,52), (42,53), (45,45) 278 279 # Natural joins with partial match 280 query II colnames,rowsort 281 SELECT * FROM onecolumn NATURAL JOIN twocolumn 282 ---- 283 x y 284 44 51 285 42 53 286 287 query IIII rowsort 288 SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = a.y 289 ---- 290 45 45 44 51 291 45 45 NULL 52 292 45 45 42 53 293 45 45 45 45 294 295 # Inner join with filter predicate 296 query II 297 SELECT o.x, t.y FROM onecolumn o INNER JOIN twocolumn t ON (o.x=t.x AND t.y=53) 298 ---- 299 42 53 300 301 # Outer joins with filter predicate 302 query II rowsort 303 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.y=53) 304 ---- 305 44 NULL 306 NULL NULL 307 42 53 308 309 query II rowsort 310 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND o.x=44) 311 ---- 312 44 51 313 NULL NULL 314 42 NULL 315 316 query II rowsort 317 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.x=44) 318 ---- 319 44 51 320 NULL NULL 321 42 NULL 322 323 # Computed columns with NATURAL FULL JOIN. 324 query III rowsort 325 SELECT * FROM (SELECT x, 2 two FROM onecolumn) NATURAL FULL JOIN (SELECT x, y+1 plus1 FROM twocolumn) 326 ---- 327 NULL NULL 53 328 NULL 2 NULL 329 45 NULL 46 330 44 2 52 331 42 2 54 332 333 ## Simple test cases for inner, left, right, and outer joins 334 335 statement ok 336 CREATE TABLE a (i int); INSERT INTO a VALUES (1), (2), (3) 337 338 statement ok 339 CREATE TABLE b (i int, b bool); INSERT INTO b VALUES (2, true), (3, true), (4, false) 340 341 query IIB rowsort 342 SELECT * FROM a INNER JOIN b ON a.i = b.i 343 ---- 344 2 2 true 345 3 3 true 346 347 query IIB rowsort 348 SELECT * FROM a LEFT OUTER JOIN b ON a.i = b.i 349 ---- 350 1 NULL NULL 351 2 2 true 352 3 3 true 353 354 query IIB rowsort 355 SELECT * FROM a RIGHT OUTER JOIN b ON a.i = b.i 356 ---- 357 2 2 true 358 3 3 true 359 NULL 4 false 360 361 query IIB rowsort 362 SELECT * FROM a FULL OUTER JOIN b ON a.i = b.i 363 ---- 364 1 NULL NULL 365 2 2 true 366 3 3 true 367 NULL 4 false 368 369 # Full outer join with filter predicate 370 query IIB 371 SELECT * FROM a FULL OUTER JOIN b ON (a.i = b.i and a.i>2) ORDER BY a.i, b.i 372 ---- 373 NULL 2 true 374 NULL 4 false 375 1 NULL NULL 376 2 NULL NULL 377 3 3 true 378 379 # Duplicate right matches for a single left row 380 statement ok 381 INSERT INTO b VALUES (3, false) 382 383 query IIB 384 SELECT * FROM a RIGHT OUTER JOIN b ON a.i=b.i ORDER BY b.i, b.b 385 ---- 386 2 2 true 387 3 3 false 388 3 3 true 389 NULL 4 false 390 391 query IIB 392 SELECT * FROM a FULL OUTER JOIN b ON a.i=b.i ORDER BY b.i, b.b 393 ---- 394 1 NULL NULL 395 2 2 true 396 3 3 false 397 3 3 true 398 NULL 4 false 399 400 401 # Check column orders and names. 402 query IIIIII colnames 403 SELECT * FROM (onecolumn CROSS JOIN twocolumn JOIN onecolumn AS a(b) ON a.b=twocolumn.x JOIN twocolumn AS c(d,e) ON a.b=c.d AND c.d=onecolumn.x) ORDER BY 1 LIMIT 1 404 ---- 405 x x y b d e 406 42 42 53 42 42 53 407 408 # Check sub-queries in ON conditions. 409 query III colnames 410 SELECT * FROM onecolumn JOIN twocolumn ON twocolumn.x = onecolumn.x AND onecolumn.x IN (SELECT x FROM twocolumn WHERE y >= 52) 411 ---- 412 x x y 413 42 42 53 414 415 # Check sub-queries as data sources. 416 query I colnames 417 SELECT * FROM onecolumn JOIN (VALUES (41),(42),(43)) AS a(x) USING(x) 418 ---- 419 x 420 42 421 422 query I colnames 423 SELECT * FROM onecolumn JOIN (SELECT x + 2 AS x FROM onecolumn) USING(x) 424 ---- 425 x 426 44 427 428 # Check that a single column can have multiple table aliases. 429 query IIII colnames 430 SELECT * FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY x LIMIT 1 431 ---- 432 x y y y 433 42 53 53 53 434 435 query IIIIII colnames 436 SELECT a.x AS s, b.x, c.x, a.y, b.y, c.y FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY s 437 ---- 438 s x x y y y 439 42 42 42 53 53 53 440 44 44 44 51 51 51 441 45 45 45 45 45 45 442 443 query error pgcode 42703 column "y" specified in USING clause does not exist 444 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(y)) 445 446 query error pgcode 42701 column name "x" appears more than once in USING clause 447 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(x, x)) 448 449 statement ok 450 CREATE TABLE othertype (x TEXT) 451 452 query error pgcode 42804 JOIN/USING types.*cannot be matched 453 SELECT * FROM (onecolumn AS a JOIN othertype AS b USING(x)) 454 455 query error pgcode 42712 source name "onecolumn" specified more than once \(missing AS clause\) 456 SELECT * FROM (onecolumn JOIN onecolumn USING(x)) 457 458 query error pgcode 42712 source name "onecolumn" specified more than once \(missing AS clause\) 459 SELECT * FROM (onecolumn JOIN twocolumn USING(x) JOIN onecolumn USING(x)) 460 461 # Check that star expansion works across anonymous sources. 462 query II rowsort 463 SELECT * FROM (SELECT * FROM onecolumn), (SELECT * FROM onecolumn) 464 ---- 465 42 42 466 42 44 467 42 NULL 468 44 42 469 44 44 470 44 NULL 471 NULL 42 472 NULL 44 473 NULL NULL 474 475 # Check that anonymous sources are properly looked up without ambiguity. 476 query I 477 SELECT x FROM (onecolumn JOIN othercolumn USING (x)) JOIN (onecolumn AS a JOIN othercolumn AS b USING(x)) USING(x) 478 ---- 479 42 480 481 # Check that multiple anonymous sources cause proper ambiguity errors. 482 query error column reference "x" is ambiguous \(candidates: <anonymous>\.x\) 483 SELECT x FROM (SELECT * FROM onecolumn), (SELECT * FROM onecolumn) 484 485 query error column reference "x" is ambiguous \(candidates: a\.x, b\.x\) 486 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON x > 32) 487 488 query error column "a.y" does not exist 489 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON a.y > y) 490 491 statement ok 492 CREATE TABLE s(x INT); INSERT INTO s(x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) 493 494 # Ensure that large cross-joins are optimized somehow (#10633) 495 statement ok 496 CREATE TABLE customers(id INT PRIMARY KEY NOT NULL); CREATE TABLE orders(id INT, cust INT REFERENCES customers(id)) 497 498 query TTTTTTTTIIITTI 499 SELECT NULL::text AS pktable_cat, 500 pkn.nspname AS pktable_schem, 501 pkc.relname AS pktable_name, 502 pka.attname AS pkcolumn_name, 503 NULL::text AS fktable_cat, 504 fkn.nspname AS fktable_schem, 505 fkc.relname AS fktable_name, 506 fka.attname AS fkcolumn_name, 507 pos.n AS key_seq, 508 CASE con.confupdtype 509 WHEN 'c' THEN 0 510 WHEN 'n' THEN 2 511 WHEN 'd' THEN 4 512 WHEN 'r' THEN 1 513 WHEN 'a' THEN 3 514 ELSE NULL 515 END AS update_rule, 516 CASE con.confdeltype 517 WHEN 'c' THEN 0 518 WHEN 'n' THEN 2 519 WHEN 'd' THEN 4 520 WHEN 'r' THEN 1 521 WHEN 'a' THEN 3 522 ELSE NULL 523 END AS delete_rule, 524 con.conname AS fk_name, 525 pkic.relname AS pk_name, 526 CASE 527 WHEN con.condeferrable 528 AND con.condeferred THEN 5 529 WHEN con.condeferrable THEN 6 530 ELSE 7 531 END AS deferrability 532 FROM pg_catalog.pg_namespace pkn, 533 pg_catalog.pg_class pkc, 534 pg_catalog.pg_attribute pka, 535 pg_catalog.pg_namespace fkn, 536 pg_catalog.pg_class fkc, 537 pg_catalog.pg_attribute fka, 538 pg_catalog.pg_constraint con, 539 pg_catalog.generate_series(1, 32) pos(n), 540 pg_catalog.pg_depend dep, 541 pg_catalog.pg_class pkic 542 WHERE pkn.oid = pkc.relnamespace 543 AND pkc.oid = pka.attrelid 544 AND pka.attnum = con.confkey[pos.n] 545 AND con.confrelid = pkc.oid 546 AND fkn.oid = fkc.relnamespace 547 AND fkc.oid = fka.attrelid 548 AND fka.attnum = con.conkey[pos.n] 549 AND con.conrelid = fkc.oid 550 AND con.contype = 'f' 551 AND con.oid = dep.objid 552 AND pkic.oid = dep.refobjid 553 AND pkic.relkind = 'i' 554 AND fkn.nspname = 'public' 555 AND fkc.relname = 'orders' 556 ORDER BY pkn.nspname, 557 pkc.relname, 558 con.conname, 559 pos.n 560 ---- 561 NULL public customers id NULL public orders cust 1 3 3 fk_cust_ref_customers primary 7 562 563 564 # Tests for filter propagation through joins. 565 566 statement ok 567 CREATE TABLE square (n INT PRIMARY KEY, sq INT) 568 569 statement ok 570 INSERT INTO square VALUES (1,1), (2,4), (3,9), (4,16), (5,25), (6,36) 571 572 statement ok 573 CREATE TABLE pairs (a INT, b INT) 574 575 statement ok 576 INSERT INTO pairs VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,3), (2,4), (2,5), (2,6), (3,4), (3,5), (3,6), (4,5), (4,6) 577 578 query IIII rowsort 579 SELECT * FROM pairs, square WHERE pairs.b = square.n 580 ---- 581 1 1 1 1 582 1 2 2 4 583 1 3 3 9 584 1 4 4 16 585 1 5 5 25 586 1 6 6 36 587 2 3 3 9 588 2 4 4 16 589 2 5 5 25 590 2 6 6 36 591 3 4 4 16 592 3 5 5 25 593 3 6 6 36 594 4 5 5 25 595 4 6 6 36 596 597 query IIII rowsort 598 SELECT * FROM pairs, square WHERE pairs.a + pairs.b = square.sq 599 ---- 600 1 3 2 4 601 3 6 3 9 602 4 5 3 9 603 604 query IIII rowsort 605 SELECT a, b, n, sq FROM (SELECT a, b, a * b / 2 AS div, n, sq FROM pairs, square) WHERE div = sq 606 ---- 607 2 4 2 4 608 3 6 3 9 609 1 2 1 1 610 611 query IIII rowsort 612 SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq 613 ---- 614 1 1 NULL NULL 615 1 2 NULL NULL 616 1 3 2 4 617 1 4 NULL NULL 618 1 5 NULL NULL 619 1 6 NULL NULL 620 2 3 NULL NULL 621 2 4 NULL NULL 622 2 5 NULL NULL 623 2 6 NULL NULL 624 3 4 NULL NULL 625 3 5 NULL NULL 626 3 6 3 9 627 4 5 3 9 628 4 6 NULL NULL 629 NULL NULL 1 1 630 NULL NULL 4 16 631 NULL NULL 5 25 632 NULL NULL 6 36 633 634 query IIII rowsort 635 SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq WHERE pairs.b%2 <> square.sq%2 636 ---- 637 1 3 2 4 638 3 6 3 9 639 640 # Filter propagation through outer joins. 641 642 query IIII rowsort 643 SELECT * 644 FROM (SELECT * FROM pairs LEFT JOIN square ON b = sq AND a > 1 AND n < 6) 645 WHERE b > 1 AND (n IS NULL OR n > 1) AND (n IS NULL OR a < sq) 646 ---- 647 1 2 NULL NULL 648 1 3 NULL NULL 649 1 4 NULL NULL 650 1 5 NULL NULL 651 1 6 NULL NULL 652 2 3 NULL NULL 653 2 4 2 4 654 2 5 NULL NULL 655 2 6 NULL NULL 656 3 4 2 4 657 3 5 NULL NULL 658 3 6 NULL NULL 659 4 5 NULL NULL 660 4 6 NULL NULL 661 662 query IIII rowsort 663 SELECT * 664 FROM (SELECT * FROM pairs RIGHT JOIN square ON b = sq AND a > 1 AND n < 6) 665 WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq) 666 ---- 667 3 4 2 4 668 NULL NULL 3 9 669 NULL NULL 4 16 670 NULL NULL 5 25 671 NULL NULL 6 36 672 673 674 statement ok 675 CREATE TABLE t1 (col1 INT, x INT, col2 INT, y INT) 676 677 statement ok 678 CREATE TABLE t2 (col3 INT, y INT, x INT, col4 INT) 679 680 statement ok 681 INSERT INTO t1 VALUES (10, 1, 11, 1), (20, 2, 21, 1), (30, 3, 31, 1) 682 683 statement ok 684 INSERT INTO t2 VALUES (100, 1, 1, 101), (200, 1, 201, 2), (400, 1, 401, 4) 685 686 query IIIIIII 687 SELECT * FROM t1 JOIN t2 USING(x) 688 ---- 689 1 10 11 1 100 1 101 690 691 query IIIIII 692 SELECT * FROM t1 NATURAL JOIN t2 693 ---- 694 1 1 10 11 100 101 695 696 query IIIIIIII 697 SELECT * FROM t1 JOIN t2 ON t2.x=t1.x 698 ---- 699 10 1 11 1 100 1 1 101 700 701 query IIIIIII rowsort 702 SELECT * FROM t1 FULL OUTER JOIN t2 USING(x) 703 ---- 704 1 10 11 1 100 1 101 705 2 20 21 1 NULL NULL NULL 706 3 30 31 1 NULL NULL NULL 707 201 NULL NULL NULL 200 1 2 708 401 NULL NULL NULL 400 1 4 709 710 query IIIIII rowsort 711 SELECT * FROM t1 NATURAL FULL OUTER JOIN t2 712 ---- 713 1 1 10 11 100 101 714 2 1 20 21 NULL NULL 715 3 1 30 31 NULL NULL 716 201 1 NULL NULL 200 2 717 401 1 NULL NULL 400 4 718 719 query IIIIIIII rowsort 720 SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.x=t2.x 721 ---- 722 10 1 11 1 100 1 1 101 723 20 2 21 1 NULL NULL NULL NULL 724 30 3 31 1 NULL NULL NULL NULL 725 NULL NULL NULL NULL 200 1 201 2 726 NULL NULL NULL NULL 400 1 401 4 727 728 query III 729 SELECT t2.x, t1.x, x FROM t1 JOIN t2 USING(x) 730 ---- 731 1 1 1 732 733 query III rowsort 734 SELECT t2.x, t1.x, x FROM t1 FULL OUTER JOIN t2 USING(x) 735 ---- 736 1 1 1 737 NULL 2 2 738 NULL 3 3 739 201 NULL 201 740 401 NULL 401 741 742 # Test for #19536. 743 query I 744 SELECT x FROM t1 NATURAL JOIN (SELECT * FROM t2) 745 ---- 746 1 747 748 # Tests for merge join ordering information. 749 statement ok 750 CREATE TABLE pkBA (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a)) 751 752 statement ok 753 CREATE TABLE pkBC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,c)) 754 755 statement ok 756 CREATE TABLE pkBAC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,c)) 757 758 statement ok 759 CREATE TABLE pkBAD (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,d)) 760 761 # Tests with joins with merged columns of collated string type. 762 statement ok 763 CREATE TABLE str1 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 764 765 statement ok 766 INSERT INTO str1 VALUES (1, 'a' COLLATE en_u_ks_level1), (2, 'A' COLLATE en_u_ks_level1), (3, 'c' COLLATE en_u_ks_level1), (4, 'D' COLLATE en_u_ks_level1) 767 768 statement ok 769 CREATE TABLE str2 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 770 771 statement ok 772 INSERT INTO str2 VALUES (1, 'A' COLLATE en_u_ks_level1), (2, 'B' COLLATE en_u_ks_level1), (3, 'C' COLLATE en_u_ks_level1), (4, 'E' COLLATE en_u_ks_level1) 773 774 query TTT rowsort 775 SELECT s, str1.s, str2.s FROM str1 INNER JOIN str2 USING(s) 776 ---- 777 a a A 778 A A A 779 c c C 780 781 query TTT rowsort 782 SELECT s, str1.s, str2.s FROM str1 LEFT OUTER JOIN str2 USING(s) 783 ---- 784 a a A 785 A A A 786 c c C 787 D D NULL 788 789 query TTT rowsort 790 SELECT s, str1.s, str2.s FROM str1 RIGHT OUTER JOIN str2 USING(s) 791 ---- 792 a a A 793 A A A 794 c c C 795 B NULL B 796 E NULL E 797 798 query TTT rowsort 799 SELECT s, str1.s, str2.s FROM str1 FULL OUTER JOIN str2 USING(s) 800 ---- 801 a a A 802 A A A 803 c c C 804 D D NULL 805 E NULL E 806 B NULL B 807 808 809 statement ok 810 CREATE TABLE xyu (x INT, y INT, u INT, PRIMARY KEY(x,y,u)) 811 812 statement ok 813 INSERT INTO xyu VALUES (0, 0, 0), (1, 1, 1), (3, 1, 31), (3, 2, 32), (4, 4, 44) 814 815 statement ok 816 CREATE TABLE xyv (x INT, y INT, v INT, PRIMARY KEY(x,y,v)) 817 818 statement ok 819 INSERT INTO xyv VALUES (1, 1, 1), (2, 2, 2), (3, 1, 31), (3, 3, 33), (5, 5, 55) 820 821 query IIII 822 SELECT * FROM xyu INNER JOIN xyv USING(x, y) WHERE x > 2 823 ---- 824 3 1 31 31 825 826 query IIII rowsort 827 SELECT * FROM xyu LEFT OUTER JOIN xyv USING(x, y) WHERE x > 2 828 ---- 829 3 1 31 31 830 3 2 32 NULL 831 4 4 44 NULL 832 833 query IIII rowsort 834 SELECT * FROM xyu RIGHT OUTER JOIN xyv USING(x, y) WHERE x > 2 835 ---- 836 3 1 31 31 837 3 3 NULL 33 838 5 5 NULL 55 839 840 query IIII rowsort 841 SELECT * FROM xyu FULL OUTER JOIN xyv USING(x, y) WHERE x > 2 842 ---- 843 3 1 31 31 844 3 2 32 NULL 845 4 4 44 NULL 846 3 3 NULL 33 847 5 5 NULL 55 848 849 query IIIIII 850 SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y WHERE xyu.x = 1 AND xyu.y < 10 851 ---- 852 1 1 1 1 1 1 853 854 query IIIIII 855 SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 856 ---- 857 1 1 1 1 1 1 858 859 query IIIIII rowsort 860 SELECT * FROM xyu LEFT OUTER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 861 ---- 862 0 0 0 NULL NULL NULL 863 1 1 1 1 1 1 864 3 1 31 NULL NULL NULL 865 3 2 32 NULL NULL NULL 866 4 4 44 NULL NULL NULL 867 868 query IIIIII rowsort 869 SELECT * FROM xyu RIGHT OUTER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 870 ---- 871 1 1 1 1 1 1 872 NULL NULL NULL 3 1 31 873 NULL NULL NULL 3 3 33 874 NULL NULL NULL 5 5 55 875 NULL NULL NULL 2 2 2 876 877 878 # Test OUTER joins that are run in the distSQL merge joiner 879 880 query IIII rowsort 881 SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu LEFT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv USING(x, y) WHERE x > 2 882 ---- 883 3 1 31 31 884 3 2 32 NULL 885 4 4 44 NULL 886 887 query IIII rowsort 888 SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu RIGHT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv USING(x, y) WHERE x > 2 889 ---- 890 3 1 31 31 891 3 3 NULL 33 892 5 5 NULL 55 893 894 query IIII rowsort 895 SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu FULL OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv USING(x, y) WHERE x > 2 896 ---- 897 3 1 31 31 898 3 2 32 NULL 899 4 4 44 NULL 900 3 3 NULL 33 901 5 5 NULL 55 902 903 query IIIIII rowsort 904 SELECT * FROM (SELECT * FROM xyu ORDER BY x, y) AS xyu LEFT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 905 ---- 906 0 0 0 NULL NULL NULL 907 1 1 1 1 1 1 908 3 1 31 NULL NULL NULL 909 3 2 32 NULL NULL NULL 910 4 4 44 NULL NULL NULL 911 912 query IIIIII rowsort 913 SELECT * FROM xyu RIGHT OUTER JOIN (SELECT * FROM xyv ORDER BY x, y) AS xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 914 ---- 915 1 1 1 1 1 1 916 NULL NULL NULL 3 1 31 917 NULL NULL NULL 3 3 33 918 NULL NULL NULL 5 5 55 919 NULL NULL NULL 2 2 2 920 921 922 # Regression test for #20858. 923 924 statement ok 925 CREATE TABLE l (a INT PRIMARY KEY) 926 927 statement ok 928 CREATE TABLE r (a INT PRIMARY KEY) 929 930 statement ok 931 INSERT INTO l VALUES (1), (2), (3) 932 933 statement ok 934 INSERT INTO r VALUES (2), (3), (4) 935 936 query I 937 SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 1 938 ---- 939 1 940 941 query I 942 SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 2 943 ---- 944 2 945 946 query I 947 SELECT * FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 3 948 ---- 949 3 950 951 query I 952 SELECT * FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 4 953 ---- 954 4 955 956 957 # Regression tests for mixed-type equality columns (#22514). 958 statement ok 959 CREATE TABLE foo ( 960 a INT, 961 b INT, 962 c FLOAT, 963 d FLOAT 964 ) 965 966 statement ok 967 INSERT INTO foo VALUES 968 (1, 1, 1, 1), 969 (2, 2, 2, 2), 970 (3, 3, 3, 3) 971 972 statement ok 973 CREATE TABLE bar ( 974 a INT, 975 b FLOAT, 976 c FLOAT, 977 d INT 978 ) 979 980 statement ok 981 INSERT INTO bar VALUES 982 (1, 1, 1, 1), 983 (2, 2, 2, 2), 984 (3, 3, 3, 3) 985 986 query IIRR rowsort 987 SELECT * FROM foo NATURAL JOIN bar 988 ---- 989 1 1 1 1 990 2 2 2 2 991 3 3 3 3 992 993 query IIRRIRI rowsort 994 SELECT * FROM foo JOIN bar USING (b) 995 ---- 996 1 1 1 1 1 1 1 997 2 2 2 2 2 2 2 998 3 3 3 3 3 3 3 999 1000 query IIRRRI rowsort 1001 SELECT * FROM foo JOIN bar USING (a, b) 1002 ---- 1003 1 1 1 1 1 1 1004 2 2 2 2 2 2 1005 3 3 3 3 3 3 1006 1007 query IIRRI rowsort 1008 SELECT * FROM foo JOIN bar USING (a, b, c) 1009 ---- 1010 1 1 1 1 1 1011 2 2 2 2 2 1012 3 3 3 3 3 1013 1014 query IIRRIRRI rowsort 1015 SELECT * FROM foo JOIN bar ON foo.b = bar.b 1016 ---- 1017 1 1 1 1 1 1 1 1 1018 2 2 2 2 2 2 2 2 1019 3 3 3 3 3 3 3 3 1020 1021 query IIRRIRRI rowsort 1022 SELECT * FROM foo JOIN bar ON foo.a = bar.a AND foo.b = bar.b 1023 ---- 1024 1 1 1 1 1 1 1 1 1025 2 2 2 2 2 2 2 2 1026 3 3 3 3 3 3 3 3 1027 1028 query IIRRIRRI rowsort 1029 SELECT * FROM foo, bar WHERE foo.b = bar.b 1030 ---- 1031 1 1 1 1 1 1 1 1 1032 2 2 2 2 2 2 2 2 1033 3 3 3 3 3 3 3 3 1034 1035 query IIRRIRRI rowsort 1036 SELECT * FROM foo, bar WHERE foo.a = bar.a AND foo.b = bar.b 1037 ---- 1038 1 1 1 1 1 1 1 1 1039 2 2 2 2 2 2 2 2 1040 3 3 3 3 3 3 3 3 1041 1042 query IIRRRI rowsort 1043 SELECT * FROM foo JOIN bar USING (a, b) WHERE foo.c = bar.c AND foo.d = bar.d 1044 ---- 1045 1 1 1 1 1 1 1046 2 2 2 2 2 2 1047 3 3 3 3 3 3 1048 1049 # Regression test for 23664. 1050 query III rowsort 1051 SELECT * FROM onecolumn AS a(x) RIGHT JOIN twocolumn ON false 1052 ---- 1053 NULL 44 51 1054 NULL NULL 52 1055 NULL 42 53 1056 NULL 45 45 1057 1058 # Regression test for #23609: make sure that the type of the merged column 1059 # is int (not unknown). 1060 query II rowsort 1061 SELECT column1, column1+1 1062 FROM 1063 (SELECT * FROM 1064 (VALUES (NULL, NULL)) AS t 1065 NATURAL FULL OUTER JOIN 1066 (VALUES (1, 1)) AS u) 1067 ---- 1068 1 2 1069 NULL NULL 1070 1071 # Regression test for #28817. Do not allow special functions in ON clause. 1072 query error generator functions are not allowed in ON 1073 SELECT * FROM foo JOIN bar ON generate_series(0, 1) < 2 1074 1075 query error aggregate functions are not allowed in JOIN conditions 1076 SELECT * FROM foo JOIN bar ON max(foo.c) < 2 1077 1078 # Regression test for #44029 (outer join on two single-row clauses, with two 1079 # results). 1080 query IIII 1081 SELECT * FROM (VALUES (1, 2)) a(a1,a2) FULL JOIN (VALUES (3, 4)) b(b1,b2) ON a1=b1 ORDER BY a2 1082 ---- 1083 NULL NULL 3 4 1084 1 2 NULL NULL 1085 1086 # Regression test for #44746 (internal error for particular condition). 1087 statement ok 1088 CREATE TABLE t44746_0(c0 INT) 1089 1090 statement ok 1091 CREATE TABLE t44746_1(c1 INT) 1092 1093 # Note: an "error parsing regexp" would also be acceptable here. 1094 statement ok 1095 SELECT * FROM t44746_0 FULL JOIN t44746_1 ON (SUBSTRING('', ')') = '') = (c1 > 0) 1096 1097 # Regression test for #49630. 1098 statement ok 1099 DROP TABLE empty; 1100 CREATE TABLE xy (x INT PRIMARY KEY, y INT); 1101 CREATE TABLE fk_ref (r INT NOT NULL REFERENCES xy (x)); 1102 CREATE TABLE empty (v INT); 1103 INSERT INTO xy (VALUES (1, 1)); 1104 INSERT INTO fk_ref (VALUES (1)); 1105 1106 query IIII 1107 SELECT * FROM fk_ref LEFT JOIN (SELECT * FROM xy INNER JOIN empty ON True) ON r = x 1108 ---- 1109 1 NULL NULL NULL 1110 1111 statement ok 1112 DROP TABLE empty; 1113 DROP TABLE fk_ref; 1114 DROP TABLE xy;