github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/join (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE onecolumn (x INT); INSERT INTO onecolumn(x) VALUES (44), (NULL), (42) 5 6 statement ok 7 CREATE TABLE twocolumn (x INT, y INT); INSERT INTO twocolumn(x, y) VALUES (44,51), (NULL,52), (42,53), (45,45) 8 9 ## Simple test cases for inner, left, right, and outer joins 10 11 query TTT 12 EXPLAIN SELECT * FROM onecolumn JOIN twocolumn USING(x) 13 ---- 14 · distributed false 15 · vectorized true 16 render · · 17 └── hash-join · · 18 │ type inner 19 │ equality (x) = (x) 20 ├── scan · · 21 │ table onecolumn@primary 22 │ spans FULL SCAN 23 └── scan · · 24 · table twocolumn@primary 25 · spans FULL SCAN 26 27 query TTT 28 EXPLAIN SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = b.y 29 ---- 30 · distributed false 31 · vectorized true 32 hash-join · · 33 │ type inner 34 │ equality (x) = (y) 35 ├── scan · · 36 │ table twocolumn@primary 37 │ spans FULL SCAN 38 └── scan · · 39 · table twocolumn@primary 40 · spans FULL SCAN 41 42 query TTT 43 EXPLAIN SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = 44 44 ---- 45 · distributed false 46 · vectorized true 47 render · · 48 └── cross-join · · 49 │ type cross 50 ├── scan · · 51 │ table twocolumn@primary 52 │ spans FULL SCAN 53 └── scan · · 54 · table twocolumn@primary 55 · spans FULL SCAN 56 · filter x = 44 57 58 query TTT 59 EXPLAIN SELECT * FROM onecolumn AS a JOIN twocolumn AS b ON ((a.x)) = ((b.y)) 60 ---- 61 · distributed false 62 · vectorized true 63 hash-join · · 64 │ type inner 65 │ equality (x) = (y) 66 ├── scan · · 67 │ table onecolumn@primary 68 │ spans FULL SCAN 69 └── scan · · 70 · table twocolumn@primary 71 · spans FULL SCAN 72 73 query TTT 74 EXPLAIN SELECT * FROM onecolumn JOIN twocolumn ON onecolumn.x = twocolumn.y 75 ---- 76 · distributed false 77 · vectorized true 78 hash-join · · 79 │ type inner 80 │ equality (x) = (y) 81 ├── scan · · 82 │ table onecolumn@primary 83 │ spans FULL SCAN 84 └── scan · · 85 · table twocolumn@primary 86 · spans FULL SCAN 87 88 query TTT 89 EXPLAIN SELECT * FROM 90 onecolumn 91 CROSS JOIN twocolumn 92 JOIN onecolumn AS a (b) ON a.b = twocolumn.x 93 JOIN twocolumn AS c (d, e) ON a.b = c.d AND c.d = onecolumn.x 94 LIMIT 1 95 ---- 96 · distributed false 97 · vectorized true 98 limit · · 99 │ count 1 100 └── hash-join · · 101 │ type inner 102 │ equality (x) = (x) 103 ├── hash-join · · 104 │ │ type inner 105 │ │ equality (x) = (x) 106 │ ├── scan · · 107 │ │ table onecolumn@primary 108 │ │ spans FULL SCAN 109 │ └── scan · · 110 │ table twocolumn@primary 111 │ spans FULL SCAN 112 └── hash-join · · 113 │ type inner 114 │ equality (x) = (x) 115 ├── scan · · 116 │ table onecolumn@primary 117 │ spans FULL SCAN 118 └── scan · · 119 · table twocolumn@primary 120 · spans FULL SCAN 121 122 # The following queries verify that only the necessary columns are scanned. 123 query TTTTT 124 EXPLAIN (VERBOSE) SELECT a.x, b.y FROM twocolumn AS a, twocolumn AS b 125 ---- 126 · distributed false · · 127 · vectorized true · · 128 cross-join · · (x, y) · 129 │ type cross · · 130 ├── scan · · (x) · 131 │ table twocolumn@primary · · 132 │ spans FULL SCAN · · 133 └── scan · · (y) · 134 · table twocolumn@primary · · 135 · spans FULL SCAN · · 136 137 query TTTTT 138 EXPLAIN (VERBOSE) SELECT b.y FROM (twocolumn AS a JOIN twocolumn AS b USING(x)) 139 ---- 140 · distributed false · · 141 · vectorized true · · 142 render · · (y) · 143 │ render 0 y · · 144 └── hash-join · · (x, x, y) · 145 │ type inner · · 146 │ equality (x) = (x) · · 147 ├── scan · · (x) · 148 │ table twocolumn@primary · · 149 │ spans FULL SCAN · · 150 └── scan · · (x, y) · 151 · table twocolumn@primary · · 152 · spans FULL SCAN · · 153 154 query TTTTT 155 EXPLAIN (VERBOSE) SELECT b.y FROM (twocolumn AS a JOIN twocolumn AS b ON a.x = b.x) 156 ---- 157 · distributed false · · 158 · vectorized true · · 159 render · · (y) · 160 │ render 0 y · · 161 └── hash-join · · (x, x, y) · 162 │ type inner · · 163 │ equality (x) = (x) · · 164 ├── scan · · (x) · 165 │ table twocolumn@primary · · 166 │ spans FULL SCAN · · 167 └── scan · · (x, y) · 168 · table twocolumn@primary · · 169 · spans FULL SCAN · · 170 171 query TTTTT 172 EXPLAIN (VERBOSE) SELECT a.x FROM (twocolumn AS a JOIN twocolumn AS b ON a.x < b.y) 173 ---- 174 · distributed false · · 175 · vectorized true · · 176 render · · (x) · 177 │ render 0 x · · 178 └── cross-join · · (x, y) · 179 │ type inner · · 180 │ pred x < y · · 181 ├── scan · · (x) · 182 │ table twocolumn@primary · · 183 │ spans FULL SCAN · · 184 └── scan · · (y) · 185 · table twocolumn@primary · · 186 · spans FULL SCAN · · 187 188 query TTTTT 189 EXPLAIN (VERBOSE) SELECT * FROM (SELECT x, 2 two FROM onecolumn) NATURAL FULL JOIN (SELECT x, y+1 plus1 FROM twocolumn) 190 ---- 191 · distributed false · · 192 · vectorized true · · 193 render · · (x, two, plus1) · 194 │ render 0 COALESCE(x, x) · · 195 │ render 1 two · · 196 │ render 2 plus1 · · 197 └── hash-join · · (two, x, plus1, x) · 198 │ type full outer · · 199 │ equality (x) = (x) · · 200 ├── render · · (two, x) · 201 │ │ render 0 2 · · 202 │ │ render 1 x · · 203 │ └── scan · · (x) · 204 │ table onecolumn@primary · · 205 │ spans FULL SCAN · · 206 └── render · · (plus1, x) · 207 │ render 0 y + 1 · · 208 │ render 1 x · · 209 └── scan · · (x, y) · 210 · table twocolumn@primary · · 211 · spans FULL SCAN · · 212 213 # Ensure that the ordering information for the result of joins is sane. (#12037) 214 query TTTTT 215 EXPLAIN (VERBOSE) SELECT * FROM (SELECT * FROM (VALUES (9, 1), (8, 2)) AS a (u, k) ORDER BY k) 216 INNER JOIN (VALUES (1, 1), (2, 2)) AS b (k, w) USING (k) ORDER BY u 217 ---- 218 · distributed false · · 219 · vectorized false · · 220 render · · (k, u, w) · 221 │ render 0 column2 · · 222 │ render 1 column1 · · 223 │ render 2 column2 · · 224 └── sort · · (column1, column2, column2) +column1 225 │ order +column1 · · 226 └── render · · (column1, column2, column2) · 227 │ render 0 column1 · · 228 │ render 1 column2 · · 229 │ render 2 column2 · · 230 └── hash-join · · (column1, column2, column1, column2) · 231 │ type inner · · 232 │ equality (column2) = (column1) · · 233 ├── values · · (column1, column2) · 234 │ size 2 columns, 2 rows · · 235 │ row 0, expr 0 9 · · 236 │ row 0, expr 1 1 · · 237 │ row 1, expr 0 8 · · 238 │ row 1, expr 1 2 · · 239 └── values · · (column1, column2) · 240 · size 2 columns, 2 rows · · 241 · row 0, expr 0 1 · · 242 · row 0, expr 1 1 · · 243 · row 1, expr 0 2 · · 244 · row 1, expr 1 2 · · 245 246 # Ensure that large cross-joins are optimized somehow (#10633) 247 statement ok 248 CREATE TABLE customers(id INT PRIMARY KEY NOT NULL); CREATE TABLE orders(id INT, cust INT REFERENCES customers(id)) 249 250 query ITTT 251 SELECT level, node_type, field, description FROM [EXPLAIN (VERBOSE) SELECT 252 NULL::text AS pktable_cat, 253 pkn.nspname AS pktable_schem, 254 pkc.relname AS pktable_name, 255 pka.attname AS pkcolumn_name, 256 NULL::text AS fktable_cat, 257 fkn.nspname AS fktable_schem, 258 fkc.relname AS fktable_name, 259 fka.attname AS fkcolumn_name, 260 pos.n AS key_seq, 261 CASE con.confupdtype 262 WHEN 'c' THEN 0 263 WHEN 'n' THEN 2 264 WHEN 'd' THEN 4 265 WHEN 'r' THEN 1 266 WHEN 'a' THEN 3 267 ELSE NULL 268 END AS update_rule, 269 CASE con.confdeltype 270 WHEN 'c' THEN 0 271 WHEN 'n' THEN 2 272 WHEN 'd' THEN 4 273 WHEN 'r' THEN 1 274 WHEN 'a' THEN 3 275 ELSE NULL 276 END AS delete_rule, 277 con.conname AS fk_name, 278 pkic.relname AS pk_name, 279 CASE 280 WHEN con.condeferrable 281 AND con.condeferred THEN 5 282 WHEN con.condeferrable THEN 6 283 ELSE 7 284 END AS deferrability 285 FROM pg_catalog.pg_namespace pkn, 286 pg_catalog.pg_class pkc, 287 pg_catalog.pg_attribute pka, 288 pg_catalog.pg_namespace fkn, 289 pg_catalog.pg_class fkc, 290 pg_catalog.pg_attribute fka, 291 pg_catalog.pg_constraint con, 292 pg_catalog.generate_series(1, 32) pos(n), 293 pg_catalog.pg_depend dep, 294 pg_catalog.pg_class pkic 295 WHERE pkn.oid = pkc.relnamespace 296 AND pkc.oid = pka.attrelid 297 AND pka.attnum = con.confkey[pos.n] 298 AND con.confrelid = pkc.oid 299 AND fkn.oid = fkc.relnamespace 300 AND fkc.oid = fka.attrelid 301 AND fka.attnum = con.conkey[pos.n] 302 AND con.conrelid = fkc.oid 303 AND con.contype = 'f' 304 AND con.oid = dep.objid 305 AND pkic.oid = dep.refobjid 306 AND pkic.relkind = 'i' 307 AND fkn.nspname = 'public' 308 AND fkc.relname = 'orders' 309 ORDER BY pkn.nspname, 310 pkc.relname, 311 con.conname, 312 pos.n 313 ] WHERE node_type <> 'values' AND field <> 'size' 314 ---- 315 0 · distributed false 316 0 · vectorized false 317 0 render · · 318 0 · render 0 pktable_cat 319 0 · render 1 nspname 320 0 · render 2 relname 321 0 · render 3 attname 322 0 · render 4 pktable_cat 323 0 · render 5 nspname 324 0 · render 6 relname 325 0 · render 7 attname 326 0 · render 8 generate_series 327 0 · render 9 update_rule 328 0 · render 10 delete_rule 329 0 · render 11 conname 330 0 · render 12 relname 331 0 · render 13 deferrability 332 1 sort · · 333 1 · order +nspname,+relname,+conname,+generate_series 334 2 render · · 335 2 · render 0 CAST(NULL AS STRING) 336 2 · render 1 CASE confupdtype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN 4 WHEN 'r' THEN 1 WHEN 'a' THEN 3 END 337 2 · render 2 CASE confdeltype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN 4 WHEN 'r' THEN 1 WHEN 'a' THEN 3 END 338 2 · render 3 CASE WHEN condeferrable AND condeferred THEN 5 WHEN condeferrable THEN 6 ELSE 7 END 339 2 · render 4 nspname 340 2 · render 5 relname 341 2 · render 6 attname 342 2 · render 7 nspname 343 2 · render 8 relname 344 2 · render 9 attname 345 2 · render 10 conname 346 2 · render 11 generate_series 347 2 · render 12 relname 348 3 hash-join · · 349 3 · type inner 350 3 · equality (refobjid) = (oid) 351 3 · right cols are key · 352 4 hash-join · · 353 4 · type inner 354 4 · equality (objid) = (oid) 355 5 render · · 356 5 · render 0 objid 357 5 · render 1 refobjid 358 6 virtual table · · 359 6 · source pg_depend@primary 360 5 cross-join · · 361 5 · type inner 362 5 · pred (attnum = confkey[generate_series]) AND (attnum = conkey[generate_series]) 363 6 hash-join · · 364 6 · type inner 365 6 · equality (oid, oid) = (confrelid, conrelid) 366 6 · right cols are key · 367 7 hash-join · · 368 7 · type inner 369 7 · equality (oid) = (attrelid) 370 7 · right cols are key · 371 8 hash-join · · 372 8 · type inner 373 8 · equality (oid) = (relnamespace) 374 9 cross-join · · 375 9 · type cross 376 10 hash-join · · 377 10 · type inner 378 10 · equality (oid) = (relnamespace) 379 11 render · · 380 11 · render 0 oid 381 11 · render 1 nspname 382 12 virtual table · · 383 12 · source pg_namespace@primary 384 11 merge-join · · 385 11 · type inner 386 11 · equality (oid) = (attrelid) 387 11 · left cols are key · 388 11 · right cols are key · 389 11 · mergeJoinOrder +"(oid=attrelid)" 390 12 sort · · 391 12 · order +oid 392 13 render · · 393 13 · render 0 oid 394 13 · render 1 relname 395 13 · render 2 relnamespace 396 14 virtual table · · 397 14 · source pg_class@pg_class_oid_idx 398 12 sort · · 399 12 · order +attrelid 400 13 render · · 401 13 · render 0 attrelid 402 13 · render 1 attname 403 13 · render 2 attnum 404 14 virtual table · · 405 14 · source pg_attribute@pg_attribute_attrelid_idx 406 10 filter · · 407 10 · filter nspname = 'public' 408 11 render · · 409 11 · render 0 oid 410 11 · render 1 nspname 411 12 virtual table · · 412 12 · source pg_namespace@primary 413 9 filter · · 414 9 · filter relname = 'orders' 415 10 render · · 416 10 · render 0 oid 417 10 · render 1 relname 418 10 · render 2 relnamespace 419 11 virtual table · · 420 11 · source pg_class@primary 421 8 render · · 422 8 · render 0 attrelid 423 8 · render 1 attname 424 8 · render 2 attnum 425 9 virtual table · · 426 9 · source pg_attribute@primary 427 7 filter · · 428 7 · filter contype = 'f' 429 8 render · · 430 8 · render 0 oid 431 8 · render 1 conname 432 8 · render 2 contype 433 8 · render 3 condeferrable 434 8 · render 4 condeferred 435 8 · render 5 conrelid 436 8 · render 6 confrelid 437 8 · render 7 confupdtype 438 8 · render 8 confdeltype 439 8 · render 9 conkey 440 8 · render 10 confkey 441 9 virtual table · · 442 9 · source pg_constraint@primary 443 6 project set · · 444 6 · render 0 generate_series(1, 32) 445 7 emptyrow · · 446 4 filter · · 447 4 · filter relkind = 'i' 448 5 render · · 449 5 · render 0 oid 450 5 · render 1 relname 451 5 · render 2 relkind 452 6 virtual table · · 453 6 · source pg_class@primary 454 455 # Ensure that left joins on non-null foreign keys turn into inner joins 456 statement ok 457 CREATE TABLE cards(id INT PRIMARY KEY, cust INT NOT NULL REFERENCES customers(id)) 458 459 query TTT 460 EXPLAIN SELECT * FROM cards LEFT OUTER JOIN customers ON customers.id = cards.cust 461 ---- 462 · distributed false 463 · vectorized true 464 merge-join · · 465 │ type inner 466 │ equality (cust) = (id) 467 │ right cols are key · 468 │ mergeJoinOrder +"(cust=id)" 469 ├── scan · · 470 │ table cards@cards_auto_index_fk_cust_ref_customers 471 │ spans FULL SCAN 472 └── scan · · 473 · table customers@primary 474 · spans FULL SCAN 475 476 # Tests for filter propagation through joins. 477 478 statement ok 479 CREATE TABLE square (n INT PRIMARY KEY, sq INT) 480 481 statement ok 482 CREATE TABLE pairs (a INT, b INT) 483 484 # The filter expression becomes an equality constraint. 485 query TTT 486 EXPLAIN SELECT * FROM pairs, square WHERE pairs.b = square.n 487 ---- 488 · distributed false 489 · vectorized true 490 hash-join · · 491 │ type inner 492 │ equality (b) = (n) 493 │ right cols are key · 494 ├── scan · · 495 │ table pairs@primary 496 │ spans FULL SCAN 497 └── scan · · 498 · table square@primary 499 · spans FULL SCAN 500 501 # The filter expression becomes an ON predicate. 502 query TTTTT 503 EXPLAIN (VERBOSE) SELECT * FROM pairs, square WHERE pairs.a + pairs.b = square.sq 504 ---- 505 · distributed false · · 506 · vectorized true · · 507 render · · (a, b, n, sq) · 508 │ render 0 a · · 509 │ render 1 b · · 510 │ render 2 n · · 511 │ render 3 sq · · 512 └── hash-join · · (column6, a, b, n, sq) · 513 │ type inner · · 514 │ equality (column6) = (sq) · · 515 ├── render · · (column6, a, b) · 516 │ │ render 0 a + b · · 517 │ │ render 1 a · · 518 │ │ render 2 b · · 519 │ └── scan · · (a, b) · 520 │ table pairs@primary · · 521 │ spans FULL SCAN · · 522 └── scan · · (n, sq) · 523 · table square@primary · · 524 · spans FULL SCAN · · 525 526 # Query similar to the one above, but the filter refers to a rendered 527 # expression and can't "break through". 528 query TTTTT 529 EXPLAIN (VERBOSE) SELECT a, b, n, sq FROM (SELECT a, b, a * b / 2 AS div, n, sq FROM pairs, square) WHERE div = sq 530 ---- 531 · distributed false · · 532 · vectorized true · · 533 render · · (a, b, n, sq) · 534 │ render 0 a · · 535 │ render 1 b · · 536 │ render 2 n · · 537 │ render 3 sq · · 538 └── filter · · (div, a, b, n, sq) · 539 │ filter div = sq · · 540 └── render · · (div, a, b, n, sq) · 541 │ render 0 (a * b) / 2 · · 542 │ render 1 a · · 543 │ render 2 b · · 544 │ render 3 n · · 545 │ render 4 sq · · 546 └── cross-join · · (a, b, n, sq) · 547 │ type cross · · 548 ├── scan · · (a, b) · 549 │ table pairs@primary · · 550 │ spans FULL SCAN · · 551 └── scan · · (n, sq) · 552 · table square@primary · · 553 · spans FULL SCAN · · 554 555 # The filter expression must stay on top of the outer join. 556 query TTTTT 557 EXPLAIN (VERBOSE) SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq 558 ---- 559 · distributed false · · 560 · vectorized true · · 561 render · · (a, b, n, sq) · 562 │ render 0 a · · 563 │ render 1 b · · 564 │ render 2 n · · 565 │ render 3 sq · · 566 └── hash-join · · (column6, a, b, n, sq) · 567 │ type full outer · · 568 │ equality (column6) = (sq) · · 569 ├── render · · (column6, a, b) · 570 │ │ render 0 a + b · · 571 │ │ render 1 a · · 572 │ │ render 2 b · · 573 │ └── scan · · (a, b) · 574 │ table pairs@primary · · 575 │ spans FULL SCAN · · 576 └── scan · · (n, sq) · 577 · table square@primary · · 578 · spans FULL SCAN · · 579 580 query TTTTT 581 EXPLAIN (VERBOSE) SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq WHERE pairs.b%2 <> square.sq%2 582 ---- 583 · distributed false · · 584 · vectorized true · · 585 render · · (a, b, n, sq) · 586 │ render 0 a · · 587 │ render 1 b · · 588 │ render 2 n · · 589 │ render 3 sq · · 590 └── filter · · (column6, a, b, n, sq) · 591 │ filter (b % 2) != (sq % 2) · · 592 └── hash-join · · (column6, a, b, n, sq) · 593 │ type full outer · · 594 │ equality (column6) = (sq) · · 595 ├── render · · (column6, a, b) · 596 │ │ render 0 a + b · · 597 │ │ render 1 a · · 598 │ │ render 2 b · · 599 │ └── scan · · (a, b) · 600 │ table pairs@primary · · 601 │ spans FULL SCAN · · 602 └── scan · · (n, sq) · 603 · table square@primary · · 604 · spans FULL SCAN · · 605 606 # Filter propagation through outer joins. 607 608 query TTT 609 SELECT tree, field, description FROM [ 610 EXPLAIN (VERBOSE) 611 SELECT * 612 FROM (SELECT * FROM pairs LEFT JOIN square ON b = sq AND a > 1 AND n < 6) 613 WHERE b > 1 AND (n IS NULL OR n > 1) AND (n IS NULL OR a < sq) 614 ] 615 ---- 616 · distributed false 617 · vectorized true 618 filter · · 619 │ filter ((n IS NULL) OR (n > 1)) AND ((n IS NULL) OR (a < sq)) 620 └── hash-join · · 621 │ type left outer 622 │ equality (b) = (sq) 623 │ pred a > 1 624 ├── scan · · 625 │ table pairs@primary 626 │ spans FULL SCAN 627 │ filter b > 1 628 └── scan · · 629 · table square@primary 630 · spans -/5/# 631 · filter sq > 1 632 633 query TTT 634 SELECT tree, field, description FROM [ 635 EXPLAIN (VERBOSE) 636 SELECT * 637 FROM (SELECT * FROM pairs RIGHT JOIN square ON b = sq AND a > 1 AND n < 6) 638 WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq) 639 ] 640 ---- 641 · distributed false 642 · vectorized true 643 render · · 644 │ render 0 a 645 │ render 1 b 646 │ render 2 n 647 │ render 3 sq 648 └── filter · · 649 │ filter ((a IS NULL) OR (a > 2)) AND ((a IS NULL) OR (a < sq)) 650 └── hash-join · · 651 │ type left outer 652 │ equality (sq) = (b) 653 │ pred n < 6 654 ├── scan · · 655 │ table square@primary 656 │ spans /2- 657 └── scan · · 658 · table pairs@primary 659 · spans FULL SCAN 660 · filter a > 1 661 662 # The simpler plan for an inner join, to compare. 663 query TTT 664 SELECT tree, field, description FROM [ 665 EXPLAIN (VERBOSE) 666 SELECT * 667 FROM (SELECT * FROM pairs JOIN square ON b = sq AND a > 1 AND n < 6) 668 WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq) 669 ] 670 ---- 671 · distributed false 672 · vectorized true 673 hash-join · · 674 │ type inner 675 │ equality (b) = (sq) 676 ├── scan · · 677 │ table pairs@primary 678 │ spans FULL SCAN 679 │ filter ((a > 1) AND ((a IS NULL) OR (a > 2))) AND ((a IS NULL) OR (a < b)) 680 └── scan · · 681 · table square@primary 682 · spans /2-/5/# 683 · parallel · 684 685 686 statement ok 687 CREATE TABLE t1 (col1 INT, x INT, col2 INT, y INT) 688 689 statement ok 690 CREATE TABLE t2 (col3 INT, y INT, x INT, col4 INT) 691 692 query TTTTT 693 EXPLAIN (VERBOSE) SELECT x FROM t1 NATURAL JOIN (SELECT * FROM t2) 694 ---- 695 · distributed false · · 696 · vectorized true · · 697 render · · (x) · 698 │ render 0 x · · 699 └── hash-join · · (x, y, y, x) · 700 │ type inner · · 701 │ equality (x, y) = (x, y) · · 702 ├── scan · · (x, y) · 703 │ table t1@primary · · 704 │ spans FULL SCAN · · 705 └── scan · · (y, x) · 706 · table t2@primary · · 707 · spans FULL SCAN · · 708 709 # Tests for merge join ordering information. 710 statement ok 711 CREATE TABLE pkBA (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a)) 712 713 statement ok 714 CREATE TABLE pkBC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,c)) 715 716 statement ok 717 CREATE TABLE pkBAC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,c)) 718 719 statement ok 720 CREATE TABLE pkBAD (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,d)) 721 722 query TTTTT 723 EXPLAIN (VERBOSE) SELECT * FROM pkBA AS l JOIN pkBC AS r ON l.a = r.a AND l.b = r.b AND l.c = r.c 724 ---- 725 · distributed false · · 726 · vectorized true · · 727 hash-join · · (a, b, c, d, a, b, c, d) · 728 │ type inner · · 729 │ equality (a, b, c) = (a, b, c) · · 730 │ left cols are key · · · 731 │ right cols are key · · · 732 ├── scan · · (a, b, c, d) · 733 │ table pkba@primary · · 734 │ spans FULL SCAN · · 735 └── scan · · (a, b, c, d) · 736 · table pkbc@primary · · 737 · spans FULL SCAN · · 738 739 query TTTTT 740 EXPLAIN (VERBOSE) SELECT * FROM pkBA NATURAL JOIN pkBAD 741 ---- 742 · distributed false · · 743 · vectorized true · · 744 render · · (a, b, c, d) · 745 │ render 0 a · · 746 │ render 1 b · · 747 │ render 2 c · · 748 │ render 3 d · · 749 └── hash-join · · (a, b, c, d, a, b, c, d) · 750 │ type inner · · 751 │ equality (a, b, c, d) = (a, b, c, d) · · 752 │ left cols are key · · · 753 │ right cols are key · · · 754 ├── scan · · (a, b, c, d) · 755 │ table pkba@primary · · 756 │ spans FULL SCAN · · 757 └── scan · · (a, b, c, d) · 758 · table pkbad@primary · · 759 · spans FULL SCAN · · 760 761 query TTTTT 762 EXPLAIN (VERBOSE) SELECT * FROM pkBAC AS l JOIN pkBAC AS r USING(a, b, c) 763 ---- 764 · distributed false · · 765 · vectorized true · · 766 render · · (a, b, c, d, d) · 767 │ render 0 a · · 768 │ render 1 b · · 769 │ render 2 c · · 770 │ render 3 d · · 771 │ render 4 d · · 772 └── merge-join · · (a, b, c, d, a, b, c, d) · 773 │ type inner · · 774 │ equality (b, a, c) = (b, a, c) · · 775 │ left cols are key · · · 776 │ right cols are key · · · 777 │ mergeJoinOrder +"(b=b)",+"(a=a)",+"(c=c)" · · 778 ├── scan · · (a, b, c, d) +b,+a,+c 779 │ table pkbac@primary · · 780 │ spans FULL SCAN · · 781 └── scan · · (a, b, c, d) +b,+a,+c 782 · table pkbac@primary · · 783 · spans FULL SCAN · · 784 785 query TTTTT 786 EXPLAIN (VERBOSE) SELECT * FROM pkBAC AS l JOIN pkBAD AS r ON l.c = r.d AND l.a = r.a AND l.b = r.b 787 ---- 788 · distributed false · · 789 · vectorized true · · 790 merge-join · · (a, b, c, d, a, b, c, d) · 791 │ type inner · · 792 │ equality (b, a, c) = (b, a, d) · · 793 │ left cols are key · · · 794 │ right cols are key · · · 795 │ mergeJoinOrder +"(b=b)",+"(a=a)",+"(c=d)" · · 796 ├── scan · · (a, b, c, d) +b,+a,+c 797 │ table pkbac@primary · · 798 │ spans FULL SCAN · · 799 └── scan · · (a, b, c, d) +b,+a,+d 800 · table pkbad@primary · · 801 · spans FULL SCAN · · 802 803 # Tests with joins with merged columns of collated string type. 804 statement ok 805 CREATE TABLE str1 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 806 807 statement ok 808 CREATE TABLE str2 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 809 810 query TTTTT 811 EXPLAIN (VERBOSE) SELECT s, str1.s, str2.s FROM str1 INNER JOIN str2 USING(s) 812 ---- 813 · distributed false · · 814 · vectorized true · · 815 render · · (s, s, s) · 816 │ render 0 s · · 817 │ render 1 s · · 818 │ render 2 s · · 819 └── hash-join · · (s, s) · 820 │ type inner · · 821 │ equality (s) = (s) · · 822 ├── scan · · (s) · 823 │ table str1@primary · · 824 │ spans FULL SCAN · · 825 └── scan · · (s) · 826 · table str2@primary · · 827 · spans FULL SCAN · · 828 829 query TTTTT 830 EXPLAIN (VERBOSE) SELECT s, str1.s, str2.s FROM str1 LEFT OUTER JOIN str2 USING(s) 831 ---- 832 · distributed false · · 833 · vectorized true · · 834 render · · (s, s, s) · 835 │ render 0 s · · 836 │ render 1 s · · 837 │ render 2 s · · 838 └── hash-join · · (s, s) · 839 │ type left outer · · 840 │ equality (s) = (s) · · 841 ├── scan · · (s) · 842 │ table str1@primary · · 843 │ spans FULL SCAN · · 844 └── scan · · (s) · 845 · table str2@primary · · 846 · spans FULL SCAN · · 847 848 query TTTTT 849 EXPLAIN (VERBOSE) SELECT s, str1.s, str2.s FROM str1 RIGHT OUTER JOIN str2 USING(s) 850 ---- 851 · distributed false · · 852 · vectorized true · · 853 render · · (s, s, s) · 854 │ render 0 COALESCE(s, s) · · 855 │ render 1 s · · 856 │ render 2 s · · 857 └── hash-join · · (s, s) · 858 │ type left outer · · 859 │ equality (s) = (s) · · 860 ├── scan · · (s) · 861 │ table str2@primary · · 862 │ spans FULL SCAN · · 863 └── scan · · (s) · 864 · table str1@primary · · 865 · spans FULL SCAN · · 866 867 query TTTTT 868 EXPLAIN (VERBOSE) SELECT s, str1.s, str2.s FROM str1 FULL OUTER JOIN str2 USING(s) 869 ---- 870 · distributed false · · 871 · vectorized true · · 872 render · · (s, s, s) · 873 │ render 0 COALESCE(s, s) · · 874 │ render 1 s · · 875 │ render 2 s · · 876 └── hash-join · · (s, s) · 877 │ type full outer · · 878 │ equality (s) = (s) · · 879 ├── scan · · (s) · 880 │ table str1@primary · · 881 │ spans FULL SCAN · · 882 └── scan · · (s) · 883 · table str2@primary · · 884 · spans FULL SCAN · · 885 886 # Verify that we resolve the merged column a to str2.a but use IFNULL for 887 # column s which is a collated string. 888 query TTTTT 889 EXPLAIN (VERBOSE) SELECT * FROM str1 RIGHT OUTER JOIN str2 USING(a, s) 890 ---- 891 · distributed false · · 892 · vectorized true · · 893 render · · (a, s) · 894 │ render 0 a · · 895 │ render 1 COALESCE(s, s) · · 896 └── hash-join · · (a, s, a, s) · 897 │ type left outer · · 898 │ equality (a, s) = (a, s) · · 899 │ left cols are key · · · 900 │ right cols are key · · · 901 ├── scan · · (a, s) · 902 │ table str2@primary · · 903 │ spans FULL SCAN · · 904 └── scan · · (a, s) · 905 · table str1@primary · · 906 · spans FULL SCAN · · 907 908 909 statement ok 910 CREATE TABLE xyu (x INT, y INT, u INT, PRIMARY KEY(x,y,u)) 911 912 statement ok 913 CREATE TABLE xyv (x INT, y INT, v INT, PRIMARY KEY(x,y,v)) 914 915 query TTTTT 916 EXPLAIN (VERBOSE) SELECT * FROM xyu INNER JOIN xyv USING(x, y) WHERE x > 2 917 ---- 918 · distributed false · · 919 · vectorized true · · 920 render · · (x, y, u, v) · 921 │ render 0 x · · 922 │ render 1 y · · 923 │ render 2 u · · 924 │ render 3 v · · 925 └── merge-join · · (x, y, u, x, y, v) · 926 │ type inner · · 927 │ equality (x, y) = (x, y) · · 928 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 929 ├── scan · · (x, y, u) +x,+y 930 │ table xyu@primary · · 931 │ spans /3- · · 932 └── scan · · (x, y, v) +x,+y 933 · table xyv@primary · · 934 · spans /3- · · 935 936 query TTTTT 937 EXPLAIN (VERBOSE) SELECT * FROM xyu LEFT OUTER JOIN xyv USING(x, y) WHERE x > 2 938 ---- 939 · distributed false · · 940 · vectorized true · · 941 render · · (x, y, u, v) · 942 │ render 0 x · · 943 │ render 1 y · · 944 │ render 2 u · · 945 │ render 3 v · · 946 └── merge-join · · (x, y, u, x, y, v) · 947 │ type left outer · · 948 │ equality (x, y) = (x, y) · · 949 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 950 ├── scan · · (x, y, u) +x,+y 951 │ table xyu@primary · · 952 │ spans /3- · · 953 └── scan · · (x, y, v) +x,+y 954 · table xyv@primary · · 955 · spans /3- · · 956 957 query TTTTT 958 EXPLAIN (VERBOSE) SELECT * FROM xyu RIGHT OUTER JOIN xyv USING(x, y) WHERE x > 2 959 ---- 960 · distributed false · · 961 · vectorized true · · 962 render · · (x, y, u, v) · 963 │ render 0 x · · 964 │ render 1 y · · 965 │ render 2 u · · 966 │ render 3 v · · 967 └── merge-join · · (x, y, v, x, y, u) · 968 │ type left outer · · 969 │ equality (x, y) = (x, y) · · 970 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 971 ├── scan · · (x, y, v) +x,+y 972 │ table xyv@primary · · 973 │ spans /3- · · 974 └── scan · · (x, y, u) +x,+y 975 · table xyu@primary · · 976 · spans /3- · · 977 978 query TTTTT 979 EXPLAIN (VERBOSE) SELECT * FROM xyu FULL OUTER JOIN xyv USING(x, y) WHERE x > 2 980 ---- 981 · distributed false · · 982 · vectorized true · · 983 filter · · (x, y, u, v) · 984 │ filter x > 2 · · 985 └── render · · (x, y, u, v) · 986 │ render 0 COALESCE(x, x) · · 987 │ render 1 COALESCE(y, y) · · 988 │ render 2 u · · 989 │ render 3 v · · 990 └── merge-join · · (x, y, u, x, y, v) · 991 │ type full outer · · 992 │ equality (x, y) = (x, y) · · 993 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 994 ├── scan · · (x, y, u) +x,+y 995 │ table xyu@primary · · 996 │ spans FULL SCAN · · 997 └── scan · · (x, y, v) +x,+y 998 · table xyv@primary · · 999 · spans FULL SCAN · · 1000 1001 # Verify that we transfer constraints between the two sides. 1002 query TTTTT 1003 EXPLAIN (VERBOSE) SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y WHERE xyu.x = 1 AND xyu.y < 10 1004 ---- 1005 · distributed false · · 1006 · vectorized true · · 1007 merge-join · · (x, y, u, x, y, v) · 1008 │ type inner · · 1009 │ equality (x, y) = (x, y) · · 1010 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1011 ├── scan · · (x, y, u) +y 1012 │ table xyu@primary · · 1013 │ spans /1-/1/10 · · 1014 └── scan · · (x, y, v) +y 1015 · table xyv@primary · · 1016 · spans /1-/1/10 · · 1017 1018 query TTTTT 1019 EXPLAIN (VERBOSE) SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 1020 ---- 1021 · distributed false · · 1022 · vectorized true · · 1023 merge-join · · (x, y, u, x, y, v) · 1024 │ type inner · · 1025 │ equality (x, y) = (x, y) · · 1026 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1027 ├── scan · · (x, y, u) +y 1028 │ table xyu@primary · · 1029 │ spans /1-/1/10 · · 1030 └── scan · · (x, y, v) +y 1031 · table xyv@primary · · 1032 · spans /1-/1/10 · · 1033 1034 query TTTTT 1035 EXPLAIN (VERBOSE) 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 1036 ---- 1037 · distributed false · · 1038 · vectorized true · · 1039 merge-join · · (x, y, u, x, y, v) · 1040 │ type left outer · · 1041 │ equality (x, y) = (x, y) · · 1042 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1043 ├── scan · · (x, y, u) +x,+y 1044 │ table xyu@primary · · 1045 │ spans FULL SCAN · · 1046 └── scan · · (x, y, v) +y 1047 · table xyv@primary · · 1048 · spans /1-/1/10 · · 1049 1050 query TTTTT 1051 EXPLAIN (VERBOSE) 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 1052 ---- 1053 · distributed false · · 1054 · vectorized true · · 1055 render · · (x, y, u, x, y, v) · 1056 │ render 0 x · · 1057 │ render 1 y · · 1058 │ render 2 u · · 1059 │ render 3 x · · 1060 │ render 4 y · · 1061 │ render 5 v · · 1062 └── merge-join · · (x, y, v, x, y, u) · 1063 │ type left outer · · 1064 │ equality (x, y) = (x, y) · · 1065 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1066 ├── scan · · (x, y, v) +x,+y 1067 │ table xyv@primary · · 1068 │ spans FULL SCAN · · 1069 └── scan · · (x, y, u) +y 1070 · table xyu@primary · · 1071 · spans /1-/1/10 · · 1072 1073 1074 # Test OUTER joins that are run in the distSQL merge joiner 1075 1076 query TTTTT 1077 EXPLAIN (VERBOSE) 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 1078 ---- 1079 · distributed false · · 1080 · vectorized true · · 1081 render · · (x, y, u, v) · 1082 │ render 0 x · · 1083 │ render 1 y · · 1084 │ render 2 u · · 1085 │ render 3 v · · 1086 └── merge-join · · (x, y, u, x, y, v) · 1087 │ type left outer · · 1088 │ equality (x, y) = (x, y) · · 1089 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1090 ├── scan · · (x, y, u) +x,+y 1091 │ table xyu@primary · · 1092 │ spans /3- · · 1093 └── scan · · (x, y, v) +x,+y 1094 · table xyv@primary · · 1095 · spans /3- · · 1096 1097 query TTTTT 1098 EXPLAIN (VERBOSE) 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 1099 ---- 1100 · distributed false · · 1101 · vectorized true · · 1102 render · · (x, y, u, v) · 1103 │ render 0 x · · 1104 │ render 1 y · · 1105 │ render 2 u · · 1106 │ render 3 v · · 1107 └── merge-join · · (x, y, v, x, y, u) · 1108 │ type left outer · · 1109 │ equality (x, y) = (x, y) · · 1110 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1111 ├── scan · · (x, y, v) +x,+y 1112 │ table xyv@primary · · 1113 │ spans /3- · · 1114 └── scan · · (x, y, u) +x,+y 1115 · table xyu@primary · · 1116 · spans /3- · · 1117 1118 query TTTTT 1119 EXPLAIN (VERBOSE) 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 1120 ---- 1121 · distributed false · · 1122 · vectorized true · · 1123 filter · · (x, y, u, v) · 1124 │ filter x > 2 · · 1125 └── render · · (x, y, u, v) · 1126 │ render 0 COALESCE(x, x) · · 1127 │ render 1 COALESCE(y, y) · · 1128 │ render 2 u · · 1129 │ render 3 v · · 1130 └── merge-join · · (x, y, u, x, y, v) · 1131 │ type full outer · · 1132 │ equality (x, y) = (x, y) · · 1133 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1134 ├── scan · · (x, y, u) +x,+y 1135 │ table xyu@primary · · 1136 │ spans FULL SCAN · · 1137 └── scan · · (x, y, v) +x,+y 1138 · table xyv@primary · · 1139 · spans FULL SCAN · · 1140 1141 query TTTTT 1142 EXPLAIN (VERBOSE) 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 1143 ---- 1144 · distributed false · · 1145 · vectorized true · · 1146 merge-join · · (x, y, u, x, y, v) · 1147 │ type left outer · · 1148 │ equality (x, y) = (x, y) · · 1149 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1150 ├── scan · · (x, y, u) +x,+y 1151 │ table xyu@primary · · 1152 │ spans FULL SCAN · · 1153 └── scan · · (x, y, v) +y 1154 · table xyv@primary · · 1155 · spans /1-/1/10 · · 1156 1157 query TTTTT 1158 EXPLAIN (VERBOSE) 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 1159 ---- 1160 · distributed false · · 1161 · vectorized true · · 1162 render · · (x, y, u, x, y, v) · 1163 │ render 0 x · · 1164 │ render 1 y · · 1165 │ render 2 u · · 1166 │ render 3 x · · 1167 │ render 4 y · · 1168 │ render 5 v · · 1169 └── merge-join · · (x, y, v, x, y, u) · 1170 │ type left outer · · 1171 │ equality (x, y) = (x, y) · · 1172 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1173 ├── scan · · (x, y, v) +x,+y 1174 │ table xyv@primary · · 1175 │ spans FULL SCAN · · 1176 └── scan · · (x, y, u) +y 1177 · table xyu@primary · · 1178 · spans /1-/1/10 · · 1179 1180 # Regression test for #20472: break up tuple inequalities. 1181 query TTTTT 1182 EXPLAIN (VERBOSE) SELECT * FROM xyu JOIN xyv USING(x, y) WHERE (x, y, u) > (1, 2, 3) 1183 ---- 1184 · distributed false · · 1185 · vectorized true · · 1186 render · · (x, y, u, v) · 1187 │ render 0 x · · 1188 │ render 1 y · · 1189 │ render 2 u · · 1190 │ render 3 v · · 1191 └── merge-join · · (x, y, u, x, y, v) · 1192 │ type inner · · 1193 │ equality (x, y) = (x, y) · · 1194 │ mergeJoinOrder +"(x=x)",+"(y=y)" · · 1195 ├── scan · · (x, y, u) +x,+y 1196 │ table xyu@primary · · 1197 │ spans /1/2/4- · · 1198 └── scan · · (x, y, v) +x,+y 1199 · table xyv@primary · · 1200 · spans FULL SCAN · · 1201 1202 1203 # Regression test for #20765/27431. 1204 # We push a filter on an equality column to both sides of a left or right outer 1205 # join. 1206 1207 statement ok 1208 CREATE TABLE l (a INT PRIMARY KEY) 1209 1210 statement ok 1211 CREATE TABLE r (a INT PRIMARY KEY) 1212 1213 query TTTTT 1214 EXPLAIN (VERBOSE) SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 3; 1215 ---- 1216 · distributed false · · 1217 · vectorized true · · 1218 render · · (a) · 1219 │ render 0 a · · 1220 └── merge-join · · (a, a) · 1221 │ type left outer · · 1222 │ equality (a) = (a) · · 1223 │ left cols are key · · · 1224 │ right cols are key · · · 1225 │ mergeJoinOrder +"(a=a)" · · 1226 ├── scan · · (a) · 1227 │ table l@primary · · 1228 │ spans /3-/3/# · · 1229 └── scan · · (a) · 1230 · table r@primary · · 1231 · spans /3-/3/# · · 1232 1233 query TTTTT 1234 EXPLAIN (VERBOSE) SELECT * FROM l LEFT OUTER JOIN r ON l.a = r.a WHERE l.a = 3; 1235 ---- 1236 · distributed false · · 1237 · vectorized true · · 1238 merge-join · · (a, a) · 1239 │ type left outer · · 1240 │ equality (a) = (a) · · 1241 │ left cols are key · · · 1242 │ right cols are key · · · 1243 │ mergeJoinOrder +"(a=a)" · · 1244 ├── scan · · (a) · 1245 │ table l@primary · · 1246 │ spans /3-/3/# · · 1247 └── scan · · (a) · 1248 · table r@primary · · 1249 · spans /3-/3/# · · 1250 1251 query TTTTT 1252 EXPLAIN (VERBOSE) SELECT * FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 3; 1253 ---- 1254 · distributed false · · 1255 · vectorized true · · 1256 render · · (a) · 1257 │ render 0 a · · 1258 └── merge-join · · (a, a) · 1259 │ type left outer · · 1260 │ equality (a) = (a) · · 1261 │ left cols are key · · · 1262 │ right cols are key · · · 1263 │ mergeJoinOrder +"(a=a)" · · 1264 ├── scan · · (a) · 1265 │ table r@primary · · 1266 │ spans /3-/3/# · · 1267 └── scan · · (a) · 1268 · table l@primary · · 1269 · spans /3-/3/# · · 1270 1271 query TTTTT 1272 EXPLAIN (VERBOSE) SELECT * FROM l RIGHT OUTER JOIN r ON l.a = r.a WHERE r.a = 3; 1273 ---- 1274 · distributed false · · 1275 · vectorized true · · 1276 render · · (a, a) · 1277 │ render 0 a · · 1278 │ render 1 a · · 1279 └── merge-join · · (a, a) · 1280 │ type left outer · · 1281 │ equality (a) = (a) · · 1282 │ left cols are key · · · 1283 │ right cols are key · · · 1284 │ mergeJoinOrder +"(a=a)" · · 1285 ├── scan · · (a) · 1286 │ table r@primary · · 1287 │ spans /3-/3/# · · 1288 └── scan · · (a) · 1289 · table l@primary · · 1290 · spans /3-/3/# · · 1291 1292 # Regression tests for #21243 1293 statement ok 1294 CREATE TABLE abcdef ( 1295 a INT NOT NULL, 1296 b INT NOT NULL, 1297 c INT NOT NULL, 1298 d INT NOT NULL, 1299 e INT NULL, 1300 f INT NULL, 1301 PRIMARY KEY (a ASC, b ASC, c DESC, d ASC) 1302 ) 1303 1304 statement ok 1305 CREATE TABLE abg ( 1306 a INT NOT NULL, 1307 b INT NOT NULL, 1308 g INT NULL, 1309 PRIMARY KEY (a ASC, b ASC) 1310 ); 1311 1312 query TTT 1313 EXPLAIN SELECT * FROM abcdef join (select * from abg) USING (a,b) WHERE ((a,b)>(1,2) OR ((a,b)=(1,2) AND c < 6) OR ((a,b,c)=(1,2,6) AND d > 8)) 1314 ---- 1315 · distributed false 1316 · vectorized true 1317 render · · 1318 └── merge-join · · 1319 │ type inner 1320 │ equality (a, b) = (a, b) 1321 │ right cols are key · 1322 │ mergeJoinOrder +"(a=a)",+"(b=b)" 1323 ├── scan · · 1324 │ table abcdef@primary 1325 │ spans /1/2/6/9- 1326 └── scan · · 1327 · table abg@primary 1328 · spans FULL SCAN 1329 1330 # Regression tests for mixed-type equality columns (#22514). 1331 statement ok 1332 CREATE TABLE foo ( 1333 a INT, 1334 b INT, 1335 c FLOAT, 1336 d FLOAT 1337 ) 1338 1339 statement ok 1340 CREATE TABLE bar ( 1341 a INT, 1342 b FLOAT, 1343 c FLOAT, 1344 d INT 1345 ) 1346 1347 # Only a and c can be equality columns. 1348 query TTT 1349 SELECT tree, field, description FROM [ 1350 EXPLAIN (VERBOSE) SELECT * FROM foo NATURAL JOIN bar 1351 ] 1352 ---- 1353 · distributed false 1354 · vectorized true 1355 render · · 1356 │ render 0 a 1357 │ render 1 b 1358 │ render 2 c 1359 │ render 3 d 1360 └── hash-join · · 1361 │ type inner 1362 │ equality (a, c) = (a, c) 1363 │ pred (b = b) AND (d = d) 1364 ├── scan · · 1365 │ table foo@primary 1366 │ spans FULL SCAN 1367 └── scan · · 1368 · table bar@primary 1369 · spans FULL SCAN 1370 1371 # b can't be an equality column. 1372 query TTT 1373 SELECT tree, field, description FROM [ 1374 EXPLAIN (VERBOSE) SELECT * FROM foo JOIN bar USING (b) 1375 ] 1376 ---- 1377 · distributed false 1378 · vectorized true 1379 render · · 1380 │ render 0 b 1381 │ render 1 a 1382 │ render 2 c 1383 │ render 3 d 1384 │ render 4 a 1385 │ render 5 c 1386 │ render 6 d 1387 └── cross-join · · 1388 │ type inner 1389 │ pred b = b 1390 ├── scan · · 1391 │ table foo@primary 1392 │ spans FULL SCAN 1393 └── scan · · 1394 · table bar@primary 1395 · spans FULL SCAN 1396 1397 # Only a can be an equality column. 1398 query TTT 1399 SELECT tree, field, description FROM [ 1400 EXPLAIN (VERBOSE) SELECT * FROM foo JOIN bar USING (a, b) 1401 ] 1402 ---- 1403 · distributed false 1404 · vectorized true 1405 render · · 1406 │ render 0 a 1407 │ render 1 b 1408 │ render 2 c 1409 │ render 3 d 1410 │ render 4 c 1411 │ render 5 d 1412 └── hash-join · · 1413 │ type inner 1414 │ equality (a) = (a) 1415 │ pred b = b 1416 ├── scan · · 1417 │ table foo@primary 1418 │ spans FULL SCAN 1419 └── scan · · 1420 · table bar@primary 1421 · spans FULL SCAN 1422 1423 # Only a and c can be equality columns. 1424 query TTT 1425 SELECT tree, field, description FROM [ 1426 EXPLAIN (VERBOSE) SELECT * FROM foo JOIN bar USING (a, b, c) 1427 ] 1428 ---- 1429 · distributed false 1430 · vectorized true 1431 render · · 1432 │ render 0 a 1433 │ render 1 b 1434 │ render 2 c 1435 │ render 3 d 1436 │ render 4 d 1437 └── hash-join · · 1438 │ type inner 1439 │ equality (a, c) = (a, c) 1440 │ pred b = b 1441 ├── scan · · 1442 │ table foo@primary 1443 │ spans FULL SCAN 1444 └── scan · · 1445 · table bar@primary 1446 · spans FULL SCAN 1447 1448 # b can't be an equality column. 1449 query TTT 1450 SELECT tree, field, description FROM [ 1451 EXPLAIN (VERBOSE) SELECT * FROM foo JOIN bar ON foo.b = bar.b 1452 ] 1453 ---- 1454 · distributed false 1455 · vectorized true 1456 cross-join · · 1457 │ type inner 1458 │ pred b = b 1459 ├── scan · · 1460 │ table foo@primary 1461 │ spans FULL SCAN 1462 └── scan · · 1463 · table bar@primary 1464 · spans FULL SCAN 1465 1466 # Only a can be an equality column. 1467 query TTT 1468 SELECT tree, field, description FROM [ 1469 EXPLAIN (VERBOSE) SELECT * FROM foo JOIN bar ON foo.a = bar.a AND foo.b = bar.b 1470 ] 1471 ---- 1472 · distributed false 1473 · vectorized true 1474 hash-join · · 1475 │ type inner 1476 │ equality (a) = (a) 1477 │ pred b = b 1478 ├── scan · · 1479 │ table foo@primary 1480 │ spans FULL SCAN 1481 └── scan · · 1482 · table bar@primary 1483 · spans FULL SCAN 1484 1485 query TTT 1486 SELECT tree, field, description FROM [ 1487 EXPLAIN (VERBOSE) SELECT * FROM foo, bar WHERE foo.b = bar.b 1488 ] 1489 ---- 1490 · distributed false 1491 · vectorized true 1492 cross-join · · 1493 │ type inner 1494 │ pred b = b 1495 ├── scan · · 1496 │ table foo@primary 1497 │ spans FULL SCAN 1498 └── scan · · 1499 · table bar@primary 1500 · spans FULL SCAN 1501 1502 # Only a can be an equality column. 1503 query TTT 1504 SELECT tree, field, description FROM [ 1505 EXPLAIN (VERBOSE) SELECT * FROM foo, bar WHERE foo.a = bar.a AND foo.b = bar.b 1506 ] 1507 ---- 1508 · distributed false 1509 · vectorized true 1510 hash-join · · 1511 │ type inner 1512 │ equality (a) = (a) 1513 │ pred b = b 1514 ├── scan · · 1515 │ table foo@primary 1516 │ spans FULL SCAN 1517 └── scan · · 1518 · table bar@primary 1519 · spans FULL SCAN 1520 1521 # Only a and c can be equality columns. 1522 query TTT 1523 EXPLAIN SELECT * FROM foo JOIN bar USING (a,b) WHERE foo.c = bar.c AND foo.d = bar.d 1524 ---- 1525 · distributed false 1526 · vectorized true 1527 render · · 1528 └── hash-join · · 1529 │ type inner 1530 │ equality (a, c) = (a, c) 1531 │ pred (b = b) AND (d = d) 1532 ├── scan · · 1533 │ table foo@primary 1534 │ spans FULL SCAN 1535 └── scan · · 1536 · table bar@primary 1537 · spans FULL SCAN 1538 1539 # Zigzag join tests. 1540 statement ok 1541 CREATE TABLE zigzag ( 1542 a INT PRIMARY KEY, 1543 b INT, 1544 c FLOAT, 1545 d FLOAT, 1546 INDEX b_idx(b), 1547 INDEX c_idx(c) 1548 ) 1549 1550 # No zigzag join should be planned if enable_zigzag_join is false. 1551 statement ok 1552 SET enable_zigzag_join = false 1553 1554 query TTT 1555 EXPLAIN SELECT a,b,c FROM zigzag WHERE b = 5 AND c = 6.0 1556 ---- 1557 · distributed false 1558 · vectorized true 1559 filter · · 1560 │ filter c = 6.0 1561 └── index-join · · 1562 │ table zigzag@primary 1563 │ key columns a 1564 └── scan · · 1565 · table zigzag@b_idx 1566 · spans /5-/6 1567 1568 # Enable zigzag joins. 1569 statement ok 1570 SET enable_zigzag_join = true 1571 1572 # Simple zigzag case - fixed columns, output cols from indexes only. 1573 query TTT 1574 EXPLAIN SELECT a,b,c FROM zigzag WHERE b = 5 AND c = 6.0 1575 ---- 1576 · distributed false 1577 · vectorized true 1578 zigzag-join · · 1579 │ type inner 1580 │ pred (@2 = 5) AND (@3 = 6.0) 1581 ├── scan · · 1582 │ table zigzag@b_idx 1583 │ fixedvals 1 column 1584 └── scan · · 1585 · table zigzag@c_idx 1586 · fixedvals 1 column 1587 1588 1589 # Zigzag join nested inside a lookup. 1590 query TTT 1591 EXPLAIN SELECT a,b,c,d FROM zigzag WHERE b = 5 AND c = 6.0 1592 ---- 1593 · distributed false 1594 · vectorized true 1595 lookup-join · · 1596 │ table zigzag@primary 1597 │ type inner 1598 │ equality (a) = (a) 1599 │ equality cols are key · 1600 │ parallel · 1601 └── zigzag-join · · 1602 │ type inner 1603 │ pred (@2 = 5) AND (@3 = 6.0) 1604 ├── scan · · 1605 │ table zigzag@b_idx 1606 │ fixedvals 1 column 1607 └── scan · · 1608 · table zigzag@c_idx 1609 · fixedvals 1 column 1610 1611 # Zigzag join nested inside a lookup, with an on condition on lookup join. 1612 query TTT 1613 EXPLAIN SELECT a,b,c,d FROM zigzag WHERE b = 5 AND c = 6.0 AND d > 4 1614 ---- 1615 · distributed false 1616 · vectorized true 1617 lookup-join · · 1618 │ table zigzag@primary 1619 │ type inner 1620 │ equality (a) = (a) 1621 │ equality cols are key · 1622 │ parallel · 1623 │ pred @4 > 4.0 1624 └── zigzag-join · · 1625 │ type inner 1626 │ pred (@2 = 5) AND (@3 = 6.0) 1627 ├── scan · · 1628 │ table zigzag@b_idx 1629 │ fixedvals 1 column 1630 └── scan · · 1631 · table zigzag@c_idx 1632 · fixedvals 1 column 1633 1634 1635 # Regression test for part of #34695. 1636 statement ok 1637 CREATE TABLE zigzag2 ( 1638 a INT, 1639 b INT, 1640 c INT, 1641 d INT, 1642 UNIQUE INDEX a_b_idx(a, b), 1643 INDEX c_idx(c) 1644 ) 1645 1646 # Check a value which is equated to NULL. 1647 1648 query TTT 1649 EXPLAIN SELECT * FROM zigzag2 WHERE a = 1 AND b = 2 AND c IS NULL 1650 ---- 1651 · distributed false 1652 · vectorized true 1653 filter · · 1654 │ filter c IS NULL 1655 └── index-join · · 1656 │ table zigzag2@primary 1657 │ key columns rowid 1658 └── scan · · 1659 · table zigzag2@a_b_idx 1660 · spans /1/2-/1/3 1661 1662 # Test that we can force a merge join. 1663 query TTT 1664 EXPLAIN SELECT * FROM onecolumn INNER MERGE JOIN twocolumn USING(x) 1665 ---- 1666 · distributed false 1667 · vectorized true 1668 render · · 1669 └── merge-join · · 1670 │ type inner 1671 │ equality (x) = (x) 1672 │ mergeJoinOrder +"(x=x)" 1673 ├── sort · · 1674 │ │ order +x 1675 │ └── scan · · 1676 │ table onecolumn@primary 1677 │ spans FULL SCAN 1678 └── sort · · 1679 │ order +x 1680 └── scan · · 1681 · table twocolumn@primary 1682 · spans FULL SCAN 1683 1684 # Test that we can force a merge join using the NATURAL syntax. 1685 query TTT 1686 EXPLAIN SELECT * FROM onecolumn NATURAL INNER MERGE JOIN twocolumn 1687 ---- 1688 · distributed false 1689 · vectorized true 1690 render · · 1691 └── merge-join · · 1692 │ type inner 1693 │ equality (x) = (x) 1694 │ mergeJoinOrder +"(x=x)" 1695 ├── sort · · 1696 │ │ order +x 1697 │ └── scan · · 1698 │ table onecolumn@primary 1699 │ spans FULL SCAN 1700 └── sort · · 1701 │ order +x 1702 └── scan · · 1703 · table twocolumn@primary 1704 · spans FULL SCAN 1705 1706 # Test that we can force a merge join using the CROSS syntax. 1707 query TTT 1708 EXPLAIN SELECT * FROM onecolumn CROSS MERGE JOIN twocolumn WHERE onecolumn.x = twocolumn.x 1709 ---- 1710 · distributed false 1711 · vectorized true 1712 merge-join · · 1713 │ type inner 1714 │ equality (x) = (x) 1715 │ mergeJoinOrder +"(x=x)" 1716 ├── sort · · 1717 │ │ order +x 1718 │ └── scan · · 1719 │ table onecolumn@primary 1720 │ spans FULL SCAN 1721 └── sort · · 1722 │ order +x 1723 └── scan · · 1724 · table twocolumn@primary 1725 · spans FULL SCAN 1726 1727 statement error LOOKUP can only be used with INNER or LEFT joins 1728 EXPLAIN SELECT * FROM onecolumn RIGHT LOOKUP JOIN twocolumn USING(x) 1729 1730 statement error could not produce a query plan conforming to the LOOKUP JOIN hint 1731 EXPLAIN SELECT * FROM onecolumn INNER LOOKUP JOIN twocolumn USING(x) 1732 1733 statement error could not produce a query plan conforming to the MERGE JOIN hint 1734 EXPLAIN SELECT * FROM onecolumn INNER MERGE JOIN twocolumn ON onecolumn.x > twocolumn.y 1735 1736 # Test that we can force a hash join (instead of merge join). 1737 query TTT 1738 EXPLAIN SELECT * FROM cards LEFT OUTER HASH JOIN customers ON customers.id = cards.cust 1739 ---- 1740 · distributed false 1741 · vectorized true 1742 hash-join · · 1743 │ type inner 1744 │ equality (cust) = (id) 1745 │ right cols are key · 1746 ├── scan · · 1747 │ table cards@primary 1748 │ spans FULL SCAN 1749 └── scan · · 1750 · table customers@primary 1751 · spans FULL SCAN