github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/join (about) 1 # tests adapted from logictest -- join 2 3 exec-ddl 4 CREATE TABLE onecolumn (x INT) 5 ---- 6 7 build 8 SELECT * FROM onecolumn AS a(x) CROSS JOIN onecolumn AS b(y) 9 ---- 10 project 11 ├── columns: x:1 y:3 12 └── inner-join (cross) 13 ├── columns: x:1 a.rowid:2!null y:3 b.rowid:4!null 14 ├── scan a 15 │ └── columns: x:1 a.rowid:2!null 16 ├── scan b 17 │ └── columns: y:3 b.rowid:4!null 18 └── filters (true) 19 20 # Check that name resolution chokes on ambiguity when it needs to. 21 build 22 SELECT x FROM onecolumn AS a, onecolumn AS b 23 ---- 24 error (42702): column reference "x" is ambiguous (candidates: a.x, b.x) 25 26 # Check that name resolution does not choke on ambiguity if an 27 # unqualified column name is requested and there is an anonymous 28 # source providing this name in addition to two or more named sources 29 # that also provide it. 30 build 31 SELECT x FROM (SELECT 1 AS x), onecolumn AS a, onecolumn AS b 32 ---- 33 project 34 ├── columns: x:1!null 35 └── inner-join (cross) 36 ├── columns: x:1!null a.x:2 a.rowid:3!null b.x:4 b.rowid:5!null 37 ├── project 38 │ ├── columns: x:1!null 39 │ ├── values 40 │ │ └── () 41 │ └── projections 42 │ └── 1 [as=x:1] 43 ├── inner-join (cross) 44 │ ├── columns: a.x:2 a.rowid:3!null b.x:4 b.rowid:5!null 45 │ ├── scan a 46 │ │ └── columns: a.x:2 a.rowid:3!null 47 │ ├── scan b 48 │ │ └── columns: b.x:4 b.rowid:5!null 49 │ └── filters (true) 50 └── filters (true) 51 52 build 53 SELECT * FROM onecolumn AS a(x) JOIN onecolumn AS b(y) ON a.x = b.y 54 ---- 55 project 56 ├── columns: x:1!null y:3!null 57 └── inner-join (hash) 58 ├── columns: x:1!null a.rowid:2!null y:3!null b.rowid:4!null 59 ├── scan a 60 │ └── columns: x:1 a.rowid:2!null 61 ├── scan b 62 │ └── columns: y:3 b.rowid:4!null 63 └── filters 64 └── x:1 = y:3 65 66 build 67 SELECT * FROM onecolumn AS a JOIN onecolumn as b USING(x) ORDER BY x 68 ---- 69 sort 70 ├── columns: x:1!null 71 ├── ordering: +1 72 └── project 73 ├── columns: a.x:1!null 74 └── inner-join (hash) 75 ├── columns: a.x:1!null a.rowid:2!null b.x:3!null b.rowid:4!null 76 ├── scan a 77 │ └── columns: a.x:1 a.rowid:2!null 78 ├── scan b 79 │ └── columns: b.x:3 b.rowid:4!null 80 └── filters 81 └── a.x:1 = b.x:3 82 83 build 84 SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn as b 85 ---- 86 project 87 ├── columns: x:1!null 88 └── inner-join (hash) 89 ├── columns: a.x:1!null a.rowid:2!null b.x:3!null b.rowid:4!null 90 ├── scan a 91 │ └── columns: a.x:1 a.rowid:2!null 92 ├── scan b 93 │ └── columns: b.x:3 b.rowid:4!null 94 └── filters 95 └── a.x:1 = b.x:3 96 97 build 98 SELECT * FROM onecolumn AS a(x) LEFT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 99 ---- 100 project 101 ├── columns: x:1 y:3 102 └── left-join (hash) 103 ├── columns: x:1 a.rowid:2!null y:3 b.rowid:4 104 ├── scan a 105 │ └── columns: x:1 a.rowid:2!null 106 ├── scan b 107 │ └── columns: y:3 b.rowid:4!null 108 └── filters 109 └── x:1 = y:3 110 111 build 112 SELECT * FROM onecolumn AS a LEFT OUTER JOIN onecolumn AS b USING(x) ORDER BY x 113 ---- 114 sort 115 ├── columns: x:1 116 ├── ordering: +1 117 └── project 118 ├── columns: a.x:1 119 └── left-join (hash) 120 ├── columns: a.x:1 a.rowid:2!null b.x:3 b.rowid:4 121 ├── scan a 122 │ └── columns: a.x:1 a.rowid:2!null 123 ├── scan b 124 │ └── columns: b.x:3 b.rowid:4!null 125 └── filters 126 └── a.x:1 = b.x:3 127 128 # Check that ORDER BY chokes on ambiguity if no table less columns 129 # were introduced by USING. (#12239) 130 build 131 SELECT * FROM onecolumn AS a, onecolumn AS b ORDER BY x 132 ---- 133 error (42P09): ORDER BY "x" is ambiguous 134 135 build 136 SELECT * FROM (SELECT x, x FROM onecolumn) AS a JOIN onecolumn AS b USING (x) 137 ---- 138 error (42701): common column name "x" appears more than once in left table 139 140 build 141 SELECT * FROM onecolumn AS a JOIN (SELECT x, x FROM onecolumn) AS b USING (x) 142 ---- 143 error (42701): common column name "x" appears more than once in right table 144 145 build 146 SELECT * FROM (SELECT x, x FROM onecolumn) AS a NATURAL JOIN onecolumn AS b 147 ---- 148 error (42701): common column name "x" appears more than once in left table 149 150 build 151 SELECT * FROM onecolumn AS a NATURAL JOIN (SELECT x, x FROM onecolumn) AS b 152 ---- 153 error (42701): common column name "x" appears more than once in right table 154 155 build 156 SELECT * FROM onecolumn AS a NATURAL LEFT OUTER JOIN onecolumn AS b 157 ---- 158 project 159 ├── columns: x:1 160 └── left-join (hash) 161 ├── columns: a.x:1 a.rowid:2!null b.x:3 b.rowid:4 162 ├── scan a 163 │ └── columns: a.x:1 a.rowid:2!null 164 ├── scan b 165 │ └── columns: b.x:3 b.rowid:4!null 166 └── filters 167 └── a.x:1 = b.x:3 168 169 build 170 SELECT * FROM onecolumn AS a(x) RIGHT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 171 ---- 172 project 173 ├── columns: x:1 y:3 174 └── right-join (hash) 175 ├── columns: x:1 a.rowid:2 y:3 b.rowid:4!null 176 ├── scan a 177 │ └── columns: x:1 a.rowid:2!null 178 ├── scan b 179 │ └── columns: y:3 b.rowid:4!null 180 └── filters 181 └── x:1 = y:3 182 183 build 184 SELECT * FROM onecolumn AS a RIGHT OUTER JOIN onecolumn AS b USING(x) ORDER BY x 185 ---- 186 sort 187 ├── columns: x:3 188 ├── ordering: +3 189 └── project 190 ├── columns: b.x:3 191 └── right-join (hash) 192 ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4!null 193 ├── scan a 194 │ └── columns: a.x:1 a.rowid:2!null 195 ├── scan b 196 │ └── columns: b.x:3 b.rowid:4!null 197 └── filters 198 └── a.x:1 = b.x:3 199 200 build 201 SELECT * FROM onecolumn AS a NATURAL RIGHT OUTER JOIN onecolumn AS b 202 ---- 203 project 204 ├── columns: x:3 205 └── right-join (hash) 206 ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4!null 207 ├── scan a 208 │ └── columns: a.x:1 a.rowid:2!null 209 ├── scan b 210 │ └── columns: b.x:3 b.rowid:4!null 211 └── filters 212 └── a.x:1 = b.x:3 213 214 exec-ddl 215 CREATE TABLE onecolumn_w(w INT) 216 ---- 217 218 build 219 SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn_w as b 220 ---- 221 project 222 ├── columns: x:1 w:3 223 └── inner-join (cross) 224 ├── columns: x:1 a.rowid:2!null w:3 b.rowid:4!null 225 ├── scan a 226 │ └── columns: x:1 a.rowid:2!null 227 ├── scan b 228 │ └── columns: w:3 b.rowid:4!null 229 └── filters (true) 230 231 exec-ddl 232 CREATE TABLE othercolumn (x INT) 233 ---- 234 235 build 236 SELECT * FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b ON a.x = b.x ORDER BY a.x,b.x 237 ---- 238 sort 239 ├── columns: x:1 x:3 240 ├── ordering: +1,+3 241 └── project 242 ├── columns: a.x:1 b.x:3 243 └── full-join (hash) 244 ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 245 ├── scan a 246 │ └── columns: a.x:1 a.rowid:2!null 247 ├── scan b 248 │ └── columns: b.x:3 b.rowid:4!null 249 └── filters 250 └── a.x:1 = b.x:3 251 252 build 253 SELECT * FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b USING(x) ORDER BY x 254 ---- 255 sort 256 ├── columns: x:5 257 ├── ordering: +5 258 └── project 259 ├── columns: x:5 260 └── project 261 ├── columns: x:5 a.x:1 a.rowid:2 b.x:3 b.rowid:4 262 ├── full-join (hash) 263 │ ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 264 │ ├── scan a 265 │ │ └── columns: a.x:1 a.rowid:2!null 266 │ ├── scan b 267 │ │ └── columns: b.x:3 b.rowid:4!null 268 │ └── filters 269 │ └── a.x:1 = b.x:3 270 └── projections 271 └── COALESCE(a.x:1, b.x:3) [as=x:5] 272 273 # Check that the source columns can be selected separately from the 274 # USING column (#12033). 275 build 276 SELECT x AS s, a.x, b.x FROM onecolumn AS a FULL OUTER JOIN othercolumn AS b USING(x) ORDER BY s 277 ---- 278 sort 279 ├── columns: s:5 x:1 x:3 280 ├── ordering: +5 281 └── project 282 ├── columns: a.x:1 b.x:3 x:5 283 └── project 284 ├── columns: x:5 a.x:1 a.rowid:2 b.x:3 b.rowid:4 285 ├── full-join (hash) 286 │ ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 287 │ ├── scan a 288 │ │ └── columns: a.x:1 a.rowid:2!null 289 │ ├── scan b 290 │ │ └── columns: b.x:3 b.rowid:4!null 291 │ └── filters 292 │ └── a.x:1 = b.x:3 293 └── projections 294 └── COALESCE(a.x:1, b.x:3) [as=x:5] 295 296 build 297 SELECT * FROM onecolumn AS a NATURAL FULL OUTER JOIN othercolumn AS b ORDER BY x 298 ---- 299 sort 300 ├── columns: x:5 301 ├── ordering: +5 302 └── project 303 ├── columns: x:5 304 └── project 305 ├── columns: x:5 a.x:1 a.rowid:2 b.x:3 b.rowid:4 306 ├── full-join (hash) 307 │ ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 308 │ ├── scan a 309 │ │ └── columns: a.x:1 a.rowid:2!null 310 │ ├── scan b 311 │ │ └── columns: b.x:3 b.rowid:4!null 312 │ └── filters 313 │ └── a.x:1 = b.x:3 314 └── projections 315 └── COALESCE(a.x:1, b.x:3) [as=x:5] 316 317 # Check that a limit on the JOIN's result do not cause rows from the 318 # JOIN operands to become invisible to the JOIN. 319 build 320 SELECT * FROM (SELECT x FROM onecolumn ORDER BY x DESC) NATURAL JOIN (VALUES (42)) AS v(x) LIMIT 1 321 ---- 322 limit 323 ├── columns: x:1!null 324 ├── project 325 │ ├── columns: x:1!null 326 │ ├── limit hint: 1.00 327 │ └── inner-join (hash) 328 │ ├── columns: x:1!null column1:3!null 329 │ ├── limit hint: 1.00 330 │ ├── project 331 │ │ ├── columns: x:1 332 │ │ └── scan onecolumn 333 │ │ └── columns: x:1 rowid:2!null 334 │ ├── values 335 │ │ ├── columns: column1:3!null 336 │ │ └── (42,) 337 │ └── filters 338 │ └── x:1 = column1:3 339 └── 1 340 341 exec-ddl 342 CREATE TABLE empty (x INT) 343 ---- 344 345 build 346 SELECT * FROM onecolumn AS a(x) CROSS JOIN empty AS b(y) 347 ---- 348 project 349 ├── columns: x:1 y:3 350 └── inner-join (cross) 351 ├── columns: x:1 a.rowid:2!null y:3 b.rowid:4!null 352 ├── scan a 353 │ └── columns: x:1 a.rowid:2!null 354 ├── scan b 355 │ └── columns: y:3 b.rowid:4!null 356 └── filters (true) 357 358 build 359 SELECT * FROM empty AS a CROSS JOIN onecolumn AS b 360 ---- 361 project 362 ├── columns: x:1 x:3 363 └── inner-join (cross) 364 ├── columns: a.x:1 a.rowid:2!null b.x:3 b.rowid:4!null 365 ├── scan a 366 │ └── columns: a.x:1 a.rowid:2!null 367 ├── scan b 368 │ └── columns: b.x:3 b.rowid:4!null 369 └── filters (true) 370 371 build 372 SELECT * FROM onecolumn AS a(x) JOIN empty AS b(y) ON a.x = b.y 373 ---- 374 project 375 ├── columns: x:1!null y:3!null 376 └── inner-join (hash) 377 ├── columns: x:1!null a.rowid:2!null y:3!null b.rowid:4!null 378 ├── scan a 379 │ └── columns: x:1 a.rowid:2!null 380 ├── scan b 381 │ └── columns: y:3 b.rowid:4!null 382 └── filters 383 └── x:1 = y:3 384 385 build 386 SELECT * FROM onecolumn AS a JOIN empty AS b USING(x) 387 ---- 388 project 389 ├── columns: x:1!null 390 └── inner-join (hash) 391 ├── columns: a.x:1!null a.rowid:2!null b.x:3!null b.rowid:4!null 392 ├── scan a 393 │ └── columns: a.x:1 a.rowid:2!null 394 ├── scan b 395 │ └── columns: b.x:3 b.rowid:4!null 396 └── filters 397 └── a.x:1 = b.x:3 398 399 build 400 SELECT * FROM empty AS a(x) JOIN onecolumn AS b(y) ON a.x = b.y 401 ---- 402 project 403 ├── columns: x:1!null y:3!null 404 └── inner-join (hash) 405 ├── columns: x:1!null a.rowid:2!null y:3!null b.rowid:4!null 406 ├── scan a 407 │ └── columns: x:1 a.rowid:2!null 408 ├── scan b 409 │ └── columns: y:3 b.rowid:4!null 410 └── filters 411 └── x:1 = y:3 412 413 build 414 SELECT * FROM empty AS a JOIN onecolumn AS b USING(x) 415 ---- 416 project 417 ├── columns: x:1!null 418 └── inner-join (hash) 419 ├── columns: a.x:1!null a.rowid:2!null b.x:3!null b.rowid:4!null 420 ├── scan a 421 │ └── columns: a.x:1 a.rowid:2!null 422 ├── scan b 423 │ └── columns: b.x:3 b.rowid:4!null 424 └── filters 425 └── a.x:1 = b.x:3 426 427 build 428 SELECT * FROM onecolumn AS a(x) LEFT OUTER JOIN empty AS b(y) ON a.x = b.y ORDER BY a.x 429 ---- 430 sort 431 ├── columns: x:1 y:3 432 ├── ordering: +1 433 └── project 434 ├── columns: x:1 y:3 435 └── left-join (hash) 436 ├── columns: x:1 a.rowid:2!null y:3 b.rowid:4 437 ├── scan a 438 │ └── columns: x:1 a.rowid:2!null 439 ├── scan b 440 │ └── columns: y:3 b.rowid:4!null 441 └── filters 442 └── x:1 = y:3 443 444 build 445 SELECT * FROM onecolumn AS a LEFT OUTER JOIN empty AS b USING(x) ORDER BY x 446 ---- 447 sort 448 ├── columns: x:1 449 ├── ordering: +1 450 └── project 451 ├── columns: a.x:1 452 └── left-join (hash) 453 ├── columns: a.x:1 a.rowid:2!null b.x:3 b.rowid:4 454 ├── scan a 455 │ └── columns: a.x:1 a.rowid:2!null 456 ├── scan b 457 │ └── columns: b.x:3 b.rowid:4!null 458 └── filters 459 └── a.x:1 = b.x:3 460 461 build 462 SELECT * FROM empty AS a(x) LEFT OUTER JOIN onecolumn AS b(y) ON a.x = b.y 463 ---- 464 project 465 ├── columns: x:1 y:3 466 └── left-join (hash) 467 ├── columns: x:1 a.rowid:2!null y:3 b.rowid:4 468 ├── scan a 469 │ └── columns: x:1 a.rowid:2!null 470 ├── scan b 471 │ └── columns: y:3 b.rowid:4!null 472 └── filters 473 └── x:1 = y:3 474 475 build 476 SELECT * FROM empty AS a LEFT OUTER JOIN onecolumn AS b USING(x) 477 ---- 478 project 479 ├── columns: x:1 480 └── left-join (hash) 481 ├── columns: a.x:1 a.rowid:2!null b.x:3 b.rowid:4 482 ├── scan a 483 │ └── columns: a.x:1 a.rowid:2!null 484 ├── scan b 485 │ └── columns: b.x:3 b.rowid:4!null 486 └── filters 487 └── a.x:1 = b.x:3 488 489 build 490 SELECT * FROM onecolumn AS a(x) RIGHT OUTER JOIN empty AS b(y) ON a.x = b.y 491 ---- 492 project 493 ├── columns: x:1 y:3 494 └── right-join (hash) 495 ├── columns: x:1 a.rowid:2 y:3 b.rowid:4!null 496 ├── scan a 497 │ └── columns: x:1 a.rowid:2!null 498 ├── scan b 499 │ └── columns: y:3 b.rowid:4!null 500 └── filters 501 └── x:1 = y:3 502 503 build 504 SELECT * FROM onecolumn AS a RIGHT OUTER JOIN empty AS b USING(x) 505 ---- 506 project 507 ├── columns: x:3 508 └── right-join (hash) 509 ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4!null 510 ├── scan a 511 │ └── columns: a.x:1 a.rowid:2!null 512 ├── scan b 513 │ └── columns: b.x:3 b.rowid:4!null 514 └── filters 515 └── a.x:1 = b.x:3 516 517 build 518 SELECT * FROM empty AS a(x) FULL OUTER JOIN onecolumn AS b(y) ON a.x = b.y ORDER BY b.y 519 ---- 520 sort 521 ├── columns: x:1 y:3 522 ├── ordering: +3 523 └── project 524 ├── columns: x:1 y:3 525 └── full-join (hash) 526 ├── columns: x:1 a.rowid:2 y:3 b.rowid:4 527 ├── scan a 528 │ └── columns: x:1 a.rowid:2!null 529 ├── scan b 530 │ └── columns: y:3 b.rowid:4!null 531 └── filters 532 └── x:1 = y:3 533 534 build 535 SELECT * FROM empty AS a FULL OUTER JOIN onecolumn AS b USING(x) ORDER BY x 536 ---- 537 sort 538 ├── columns: x:5 539 ├── ordering: +5 540 └── project 541 ├── columns: x:5 542 └── project 543 ├── columns: x:5 a.x:1 a.rowid:2 b.x:3 b.rowid:4 544 ├── full-join (hash) 545 │ ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 546 │ ├── scan a 547 │ │ └── columns: a.x:1 a.rowid:2!null 548 │ ├── scan b 549 │ │ └── columns: b.x:3 b.rowid:4!null 550 │ └── filters 551 │ └── a.x:1 = b.x:3 552 └── projections 553 └── COALESCE(a.x:1, b.x:3) [as=x:5] 554 555 build 556 SELECT * FROM onecolumn AS a(x) FULL OUTER JOIN empty AS b(y) ON a.x = b.y ORDER BY a.x 557 ---- 558 sort 559 ├── columns: x:1 y:3 560 ├── ordering: +1 561 └── project 562 ├── columns: x:1 y:3 563 └── full-join (hash) 564 ├── columns: x:1 a.rowid:2 y:3 b.rowid:4 565 ├── scan a 566 │ └── columns: x:1 a.rowid:2!null 567 ├── scan b 568 │ └── columns: y:3 b.rowid:4!null 569 └── filters 570 └── x:1 = y:3 571 572 build 573 SELECT * FROM onecolumn AS a FULL OUTER JOIN empty AS b USING(x) ORDER BY x 574 ---- 575 sort 576 ├── columns: x:5 577 ├── ordering: +5 578 └── project 579 ├── columns: x:5 580 └── project 581 ├── columns: x:5 a.x:1 a.rowid:2 b.x:3 b.rowid:4 582 ├── full-join (hash) 583 │ ├── columns: a.x:1 a.rowid:2 b.x:3 b.rowid:4 584 │ ├── scan a 585 │ │ └── columns: a.x:1 a.rowid:2!null 586 │ ├── scan b 587 │ │ └── columns: b.x:3 b.rowid:4!null 588 │ └── filters 589 │ └── a.x:1 = b.x:3 590 └── projections 591 └── COALESCE(a.x:1, b.x:3) [as=x:5] 592 593 exec-ddl 594 CREATE TABLE twocolumn (x INT, y INT) 595 ---- 596 597 # Natural joins with partial match 598 build 599 SELECT * FROM onecolumn NATURAL JOIN twocolumn 600 ---- 601 project 602 ├── columns: x:1!null y:4 603 └── inner-join (hash) 604 ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null twocolumn.x:3!null y:4 twocolumn.rowid:5!null 605 ├── scan onecolumn 606 │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 607 ├── scan twocolumn 608 │ └── columns: twocolumn.x:3 y:4 twocolumn.rowid:5!null 609 └── filters 610 └── onecolumn.x:1 = twocolumn.x:3 611 612 build 613 SELECT * FROM onecolumn JOIN twocolumn USING(x) 614 ---- 615 project 616 ├── columns: x:1!null y:4 617 └── inner-join (hash) 618 ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null twocolumn.x:3!null y:4 twocolumn.rowid:5!null 619 ├── scan onecolumn 620 │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 621 ├── scan twocolumn 622 │ └── columns: twocolumn.x:3 y:4 twocolumn.rowid:5!null 623 └── filters 624 └── onecolumn.x:1 = twocolumn.x:3 625 626 build 627 SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = b.y 628 ---- 629 project 630 ├── columns: x:1!null y:2 x:4 y:5!null 631 └── inner-join (hash) 632 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4 b.y:5!null b.rowid:6!null 633 ├── scan a 634 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 635 ├── scan b 636 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 637 └── filters 638 └── a.x:1 = b.y:5 639 640 build 641 SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = a.y 642 ---- 643 project 644 ├── columns: x:1!null y:2!null x:4 y:5 645 └── inner-join (cross) 646 ├── columns: a.x:1!null a.y:2!null a.rowid:3!null b.x:4 b.y:5 b.rowid:6!null 647 ├── scan a 648 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 649 ├── scan b 650 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 651 └── filters 652 └── a.x:1 = a.y:2 653 654 build 655 SELECT * FROM onecolumn AS a JOIN twocolumn AS b ON ((a.x)) = ((b.y)) 656 ---- 657 project 658 ├── columns: x:1!null x:3 y:4!null 659 └── inner-join (hash) 660 ├── columns: a.x:1!null a.rowid:2!null b.x:3 y:4!null b.rowid:5!null 661 ├── scan a 662 │ └── columns: a.x:1 a.rowid:2!null 663 ├── scan b 664 │ └── columns: b.x:3 y:4 b.rowid:5!null 665 └── filters 666 └── a.x:1 = y:4 667 668 build 669 SELECT * FROM onecolumn JOIN twocolumn ON onecolumn.x = twocolumn.y 670 ---- 671 project 672 ├── columns: x:1!null x:3 y:4!null 673 └── inner-join (hash) 674 ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null twocolumn.x:3 y:4!null twocolumn.rowid:5!null 675 ├── scan onecolumn 676 │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 677 ├── scan twocolumn 678 │ └── columns: twocolumn.x:3 y:4 twocolumn.rowid:5!null 679 └── filters 680 └── onecolumn.x:1 = y:4 681 682 # Inner join with filter predicate 683 build 684 SELECT * FROM twocolumn AS a JOIN twocolumn AS b ON a.x = 44 685 ---- 686 project 687 ├── columns: x:1!null y:2 x:4 y:5 688 └── inner-join (cross) 689 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4 b.y:5 b.rowid:6!null 690 ├── scan a 691 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 692 ├── scan b 693 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 694 └── filters 695 └── a.x:1 = 44 696 697 build 698 SELECT o.x, t.y FROM onecolumn o INNER JOIN twocolumn t ON (o.x=t.x AND t.y=53) 699 ---- 700 project 701 ├── columns: x:1!null y:4!null 702 └── inner-join (cross) 703 ├── columns: o.x:1!null o.rowid:2!null t.x:3!null y:4!null t.rowid:5!null 704 ├── scan o 705 │ └── columns: o.x:1 o.rowid:2!null 706 ├── scan t 707 │ └── columns: t.x:3 y:4 t.rowid:5!null 708 └── filters 709 └── (o.x:1 = t.x:3) AND (y:4 = 53) 710 711 # Outer joins with filter predicate 712 build 713 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.y=53) 714 ---- 715 project 716 ├── columns: x:1 y:4 717 └── left-join (cross) 718 ├── columns: o.x:1 o.rowid:2!null t.x:3 y:4 t.rowid:5 719 ├── scan o 720 │ └── columns: o.x:1 o.rowid:2!null 721 ├── scan t 722 │ └── columns: t.x:3 y:4 t.rowid:5!null 723 └── filters 724 └── (o.x:1 = t.x:3) AND (y:4 = 53) 725 726 build 727 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND o.x=44) 728 ---- 729 project 730 ├── columns: x:1 y:4 731 └── left-join (cross) 732 ├── columns: o.x:1 o.rowid:2!null t.x:3 y:4 t.rowid:5 733 ├── scan o 734 │ └── columns: o.x:1 o.rowid:2!null 735 ├── scan t 736 │ └── columns: t.x:3 y:4 t.rowid:5!null 737 └── filters 738 └── (o.x:1 = t.x:3) AND (o.x:1 = 44) 739 740 build 741 SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND t.x=44) 742 ---- 743 project 744 ├── columns: x:1 y:4 745 └── left-join (cross) 746 ├── columns: o.x:1 o.rowid:2!null t.x:3 y:4 t.rowid:5 747 ├── scan o 748 │ └── columns: o.x:1 o.rowid:2!null 749 ├── scan t 750 │ └── columns: t.x:3 y:4 t.rowid:5!null 751 └── filters 752 └── (o.x:1 = t.x:3) AND (t.x:3 = 44) 753 754 build 755 SELECT x, a.x, b.y FROM (SELECT * FROM onecolumn AS a NATURAL JOIN twocolumn AS b) AS q 756 ---- 757 error (42P01): no data source matches prefix: a 758 759 build 760 SELECT x, a.x, b.y FROM (SELECT * FROM onecolumn AS a NATURAL JOIN twocolumn AS b) 761 ---- 762 error (42P01): no data source matches prefix: a 763 764 765 ## Simple test cases for inner, left, right, and outer joins 766 767 exec-ddl 768 CREATE TABLE a (i int) 769 ---- 770 771 exec-ddl 772 CREATE TABLE b (i int, b bool) 773 ---- 774 775 build 776 SELECT * FROM a INNER JOIN b ON a.i = b.i 777 ---- 778 project 779 ├── columns: i:1!null i:3!null b:4 780 └── inner-join (hash) 781 ├── columns: a.i:1!null a.rowid:2!null b.i:3!null b:4 b.rowid:5!null 782 ├── scan a 783 │ └── columns: a.i:1 a.rowid:2!null 784 ├── scan b 785 │ └── columns: b.i:3 b:4 b.rowid:5!null 786 └── filters 787 └── a.i:1 = b.i:3 788 789 build 790 SELECT * FROM a LEFT OUTER JOIN b ON a.i = b.i 791 ---- 792 project 793 ├── columns: i:1 i:3 b:4 794 └── left-join (hash) 795 ├── columns: a.i:1 a.rowid:2!null b.i:3 b:4 b.rowid:5 796 ├── scan a 797 │ └── columns: a.i:1 a.rowid:2!null 798 ├── scan b 799 │ └── columns: b.i:3 b:4 b.rowid:5!null 800 └── filters 801 └── a.i:1 = b.i:3 802 803 build 804 SELECT * FROM a RIGHT OUTER JOIN b ON a.i = b.i ORDER BY b.i, b.b 805 ---- 806 sort 807 ├── columns: i:1 i:3 b:4 808 ├── ordering: +3,+4 809 └── project 810 ├── columns: a.i:1 b.i:3 b:4 811 └── right-join (hash) 812 ├── columns: a.i:1 a.rowid:2 b.i:3 b:4 b.rowid:5!null 813 ├── scan a 814 │ └── columns: a.i:1 a.rowid:2!null 815 ├── scan b 816 │ └── columns: b.i:3 b:4 b.rowid:5!null 817 └── filters 818 └── a.i:1 = b.i:3 819 820 build 821 SELECT * FROM a FULL OUTER JOIN b ON a.i = b.i ORDER BY b.i, b.b 822 ---- 823 sort 824 ├── columns: i:1 i:3 b:4 825 ├── ordering: +3,+4 826 └── project 827 ├── columns: a.i:1 b.i:3 b:4 828 └── full-join (hash) 829 ├── columns: a.i:1 a.rowid:2 b.i:3 b:4 b.rowid:5 830 ├── scan a 831 │ └── columns: a.i:1 a.rowid:2!null 832 ├── scan b 833 │ └── columns: b.i:3 b:4 b.rowid:5!null 834 └── filters 835 └── a.i:1 = b.i:3 836 837 # Full outer join with filter predicate 838 build 839 SELECT * FROM a FULL OUTER JOIN b ON (a.i = b.i and a.i>2) ORDER BY a.i, b.i 840 ---- 841 sort 842 ├── columns: i:1 i:3 b:4 843 ├── ordering: +1,+3 844 └── project 845 ├── columns: a.i:1 b.i:3 b:4 846 └── full-join (cross) 847 ├── columns: a.i:1 a.rowid:2 b.i:3 b:4 b.rowid:5 848 ├── scan a 849 │ └── columns: a.i:1 a.rowid:2!null 850 ├── scan b 851 │ └── columns: b.i:3 b:4 b.rowid:5!null 852 └── filters 853 └── (a.i:1 = b.i:3) AND (a.i:1 > 2) 854 855 # Check column orders and names. 856 build 857 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 858 ---- 859 limit 860 ├── columns: x:1!null x:3!null y:4 b:6!null d:8!null e:9 861 ├── internal-ordering: +1 862 ├── ordering: +1 863 ├── sort 864 │ ├── columns: onecolumn.x:1!null twocolumn.x:3!null y:4 b:6!null d:8!null e:9 865 │ ├── ordering: +1 866 │ ├── limit hint: 1.00 867 │ └── project 868 │ ├── columns: onecolumn.x:1!null twocolumn.x:3!null y:4 b:6!null d:8!null e:9 869 │ └── inner-join (cross) 870 │ ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null twocolumn.x:3!null y:4 twocolumn.rowid:5!null b:6!null a.rowid:7!null d:8!null e:9 c.rowid:10!null 871 │ ├── inner-join (hash) 872 │ │ ├── columns: onecolumn.x:1 onecolumn.rowid:2!null twocolumn.x:3!null y:4 twocolumn.rowid:5!null b:6!null a.rowid:7!null 873 │ │ ├── inner-join (cross) 874 │ │ │ ├── columns: onecolumn.x:1 onecolumn.rowid:2!null twocolumn.x:3 y:4 twocolumn.rowid:5!null 875 │ │ │ ├── scan onecolumn 876 │ │ │ │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 877 │ │ │ ├── scan twocolumn 878 │ │ │ │ └── columns: twocolumn.x:3 y:4 twocolumn.rowid:5!null 879 │ │ │ └── filters (true) 880 │ │ ├── scan a 881 │ │ │ └── columns: b:6 a.rowid:7!null 882 │ │ └── filters 883 │ │ └── b:6 = twocolumn.x:3 884 │ ├── scan c 885 │ │ └── columns: d:8 e:9 c.rowid:10!null 886 │ └── filters 887 │ └── (b:6 = d:8) AND (d:8 = onecolumn.x:1) 888 └── 1 889 890 # Check sub-queries in ON conditions. 891 build 892 SELECT * FROM onecolumn JOIN twocolumn ON twocolumn.x = onecolumn.x AND onecolumn.x IN (SELECT x FROM twocolumn WHERE y >= 52) 893 ---- 894 project 895 ├── columns: x:1!null x:3!null y:4 896 └── inner-join (cross) 897 ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null twocolumn.x:3!null y:4 twocolumn.rowid:5!null 898 ├── scan onecolumn 899 │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 900 ├── scan twocolumn 901 │ └── columns: twocolumn.x:3 y:4 twocolumn.rowid:5!null 902 └── filters 903 └── and 904 ├── twocolumn.x:3 = onecolumn.x:1 905 └── any: eq 906 ├── project 907 │ ├── columns: twocolumn.x:6 908 │ └── select 909 │ ├── columns: twocolumn.x:6 y:7!null twocolumn.rowid:8!null 910 │ ├── scan twocolumn 911 │ │ └── columns: twocolumn.x:6 y:7 twocolumn.rowid:8!null 912 │ └── filters 913 │ └── y:7 >= 52 914 └── onecolumn.x:1 915 916 # Check sub-queries as data sources. 917 build 918 SELECT * FROM onecolumn JOIN (VALUES (41),(42),(43)) AS a(x) USING(x) 919 ---- 920 project 921 ├── columns: x:1!null 922 └── inner-join (hash) 923 ├── columns: x:1!null rowid:2!null column1:3!null 924 ├── scan onecolumn 925 │ └── columns: x:1 rowid:2!null 926 ├── values 927 │ ├── columns: column1:3!null 928 │ ├── (41,) 929 │ ├── (42,) 930 │ └── (43,) 931 └── filters 932 └── x:1 = column1:3 933 934 build 935 SELECT * FROM onecolumn JOIN (SELECT x + 2 AS x FROM onecolumn) USING(x) 936 ---- 937 project 938 ├── columns: x:1!null 939 └── inner-join (hash) 940 ├── columns: onecolumn.x:1!null rowid:2!null x:5!null 941 ├── scan onecolumn 942 │ └── columns: onecolumn.x:1 rowid:2!null 943 ├── project 944 │ ├── columns: x:5 945 │ ├── scan onecolumn 946 │ │ └── columns: onecolumn.x:3 rowid:4!null 947 │ └── projections 948 │ └── onecolumn.x:3 + 2 [as=x:5] 949 └── filters 950 └── onecolumn.x:1 = x:5 951 952 # Check that a single column can have multiple table aliases. 953 build 954 SELECT * FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY x LIMIT 1 955 ---- 956 limit 957 ├── columns: x:1!null y:2 y:5 y:8 958 ├── internal-ordering: +1 959 ├── ordering: +1 960 ├── sort 961 │ ├── columns: a.x:1!null a.y:2 b.y:5 c.y:8 962 │ ├── ordering: +1 963 │ ├── limit hint: 1.00 964 │ └── project 965 │ ├── columns: a.x:1!null a.y:2 b.y:5 c.y:8 966 │ └── inner-join (hash) 967 │ ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null c.x:7!null c.y:8 c.rowid:9!null 968 │ ├── inner-join (hash) 969 │ │ ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null 970 │ │ ├── scan a 971 │ │ │ └── columns: a.x:1 a.y:2 a.rowid:3!null 972 │ │ ├── scan b 973 │ │ │ └── columns: b.x:4 b.y:5 b.rowid:6!null 974 │ │ └── filters 975 │ │ └── a.x:1 = b.x:4 976 │ ├── scan c 977 │ │ └── columns: c.x:7 c.y:8 c.rowid:9!null 978 │ └── filters 979 │ └── a.x:1 = c.x:7 980 └── 1 981 982 build 983 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 984 ---- 985 sort 986 ├── columns: s:1!null x:4!null x:7!null y:2 y:5 y:8 987 ├── ordering: +1 988 └── project 989 ├── columns: a.x:1!null a.y:2 b.x:4!null b.y:5 c.x:7!null c.y:8 990 └── inner-join (hash) 991 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null c.x:7!null c.y:8 c.rowid:9!null 992 ├── inner-join (hash) 993 │ ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null 994 │ ├── scan a 995 │ │ └── columns: a.x:1 a.y:2 a.rowid:3!null 996 │ ├── scan b 997 │ │ └── columns: b.x:4 b.y:5 b.rowid:6!null 998 │ └── filters 999 │ └── a.x:1 = b.x:4 1000 ├── scan c 1001 │ └── columns: c.x:7 c.y:8 c.rowid:9!null 1002 └── filters 1003 └── a.x:1 = c.x:7 1004 1005 build 1006 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(y)) 1007 ---- 1008 error (42703): column "y" specified in USING clause does not exist in left table 1009 1010 build 1011 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b USING(x, x)) 1012 ---- 1013 error (42701): column name "x" appears more than once in USING clause 1014 1015 exec-ddl 1016 CREATE TABLE othertype (x TEXT) 1017 ---- 1018 1019 build 1020 SELECT * FROM (onecolumn AS a JOIN othertype AS b USING(x)) 1021 ---- 1022 error (42804): JOIN/USING types int for left and string for right cannot be matched for column "x" 1023 1024 build 1025 SELECT * FROM (onecolumn JOIN onecolumn USING(x)) 1026 ---- 1027 error (42712): source name "onecolumn" specified more than once (missing AS clause) 1028 1029 build 1030 SELECT * FROM (onecolumn JOIN twocolumn USING(x) JOIN onecolumn USING(x)) 1031 ---- 1032 error (42712): source name "onecolumn" specified more than once (missing AS clause) 1033 1034 # Check that star expansion works across anonymous sources. 1035 build 1036 SELECT * FROM (SELECT * FROM onecolumn), (SELECT * FROM onecolumn) 1037 ---- 1038 inner-join (cross) 1039 ├── columns: x:1 x:3 1040 ├── project 1041 │ ├── columns: x:1 1042 │ └── scan onecolumn 1043 │ └── columns: x:1 rowid:2!null 1044 ├── project 1045 │ ├── columns: x:3 1046 │ └── scan onecolumn 1047 │ └── columns: x:3 rowid:4!null 1048 └── filters (true) 1049 1050 # Check that anonymous sources are properly looked up without ambiguity. 1051 build 1052 SELECT x FROM (onecolumn JOIN othercolumn USING (x)) JOIN (onecolumn AS a JOIN othercolumn AS b USING(x)) USING(x) 1053 ---- 1054 project 1055 ├── columns: x:1!null 1056 └── inner-join (hash) 1057 ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null othercolumn.x:3!null othercolumn.rowid:4!null a.x:5!null a.rowid:6!null b.x:7!null b.rowid:8!null 1058 ├── inner-join (hash) 1059 │ ├── columns: onecolumn.x:1!null onecolumn.rowid:2!null othercolumn.x:3!null othercolumn.rowid:4!null 1060 │ ├── scan onecolumn 1061 │ │ └── columns: onecolumn.x:1 onecolumn.rowid:2!null 1062 │ ├── scan othercolumn 1063 │ │ └── columns: othercolumn.x:3 othercolumn.rowid:4!null 1064 │ └── filters 1065 │ └── onecolumn.x:1 = othercolumn.x:3 1066 ├── inner-join (hash) 1067 │ ├── columns: a.x:5!null a.rowid:6!null b.x:7!null b.rowid:8!null 1068 │ ├── scan a 1069 │ │ └── columns: a.x:5 a.rowid:6!null 1070 │ ├── scan b 1071 │ │ └── columns: b.x:7 b.rowid:8!null 1072 │ └── filters 1073 │ └── a.x:5 = b.x:7 1074 └── filters 1075 └── onecolumn.x:1 = a.x:5 1076 1077 # Check that multiple anonymous sources cause proper ambiguity errors. 1078 build 1079 SELECT x FROM (SELECT * FROM onecolumn), (SELECT * FROM onecolumn) 1080 ---- 1081 error (42702): column reference "x" is ambiguous (candidates: <anonymous>.x) 1082 1083 build 1084 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON x > 32) 1085 ---- 1086 error (42702): column reference "x" is ambiguous (candidates: a.x, b.x) 1087 1088 build 1089 SELECT * FROM (onecolumn AS a JOIN onecolumn AS b ON a.y > y) 1090 ---- 1091 error (42703): column "a.y" does not exist 1092 1093 # THe following queries verify that only the necessary columns are scanned. 1094 build 1095 SELECT a.x, b.y FROM twocolumn AS a, twocolumn AS b 1096 ---- 1097 project 1098 ├── columns: x:1 y:5 1099 └── inner-join (cross) 1100 ├── columns: a.x:1 a.y:2 a.rowid:3!null b.x:4 b.y:5 b.rowid:6!null 1101 ├── scan a 1102 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 1103 ├── scan b 1104 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 1105 └── filters (true) 1106 1107 build 1108 SELECT b.y FROM (twocolumn AS a JOIN twocolumn AS b USING(x)) 1109 ---- 1110 project 1111 ├── columns: y:5 1112 └── inner-join (hash) 1113 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null 1114 ├── scan a 1115 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 1116 ├── scan b 1117 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 1118 └── filters 1119 └── a.x:1 = b.x:4 1120 1121 build 1122 SELECT b.y FROM (twocolumn AS a JOIN twocolumn AS b ON a.x = b.x) 1123 ---- 1124 project 1125 ├── columns: y:5 1126 └── inner-join (hash) 1127 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4!null b.y:5 b.rowid:6!null 1128 ├── scan a 1129 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 1130 ├── scan b 1131 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 1132 └── filters 1133 └── a.x:1 = b.x:4 1134 1135 build 1136 SELECT a.x FROM (twocolumn AS a JOIN twocolumn AS b ON a.x < b.y) 1137 ---- 1138 project 1139 ├── columns: x:1!null 1140 └── inner-join (cross) 1141 ├── columns: a.x:1!null a.y:2 a.rowid:3!null b.x:4 b.y:5!null b.rowid:6!null 1142 ├── scan a 1143 │ └── columns: a.x:1 a.y:2 a.rowid:3!null 1144 ├── scan b 1145 │ └── columns: b.x:4 b.y:5 b.rowid:6!null 1146 └── filters 1147 └── a.x:1 < b.y:5 1148 1149 build 1150 SELECT * FROM (SELECT * FROM (VALUES (9, 1), (8, 2)) AS a (u, k) ORDER BY k) 1151 INNER JOIN (VALUES (1, 1), (2, 2)) AS b (k, w) USING (k) ORDER BY u 1152 ---- 1153 sort 1154 ├── columns: k:2!null u:1!null w:4!null 1155 ├── ordering: +1 1156 └── project 1157 ├── columns: column1:1!null column2:2!null column2:4!null 1158 └── inner-join (hash) 1159 ├── columns: column1:1!null column2:2!null column1:3!null column2:4!null 1160 ├── values 1161 │ ├── columns: column1:1!null column2:2!null 1162 │ ├── (9, 1) 1163 │ └── (8, 2) 1164 ├── values 1165 │ ├── columns: column1:3!null column2:4!null 1166 │ ├── (1, 1) 1167 │ └── (2, 2) 1168 └── filters 1169 └── column2:2 = column1:3 1170 1171 # Tests for filter propagation through joins. 1172 1173 exec-ddl 1174 CREATE TABLE square (n INT PRIMARY KEY, sq INT) 1175 ---- 1176 1177 exec-ddl 1178 CREATE TABLE pairs (a INT, b INT) 1179 ---- 1180 1181 # The filter expression becomes an equality constraint. 1182 build 1183 SELECT * FROM pairs, square WHERE pairs.b = square.n 1184 ---- 1185 project 1186 ├── columns: a:1 b:2!null n:4!null sq:5 1187 └── select 1188 ├── columns: a:1 b:2!null rowid:3!null n:4!null sq:5 1189 ├── inner-join (cross) 1190 │ ├── columns: a:1 b:2 rowid:3!null n:4!null sq:5 1191 │ ├── scan pairs 1192 │ │ └── columns: a:1 b:2 rowid:3!null 1193 │ ├── scan square 1194 │ │ └── columns: n:4!null sq:5 1195 │ └── filters (true) 1196 └── filters 1197 └── b:2 = n:4 1198 1199 # The filter expression becomes an ON predicate. 1200 build 1201 SELECT * FROM pairs, square WHERE pairs.a + pairs.b = square.sq 1202 ---- 1203 project 1204 ├── columns: a:1 b:2 n:4!null sq:5 1205 └── select 1206 ├── columns: a:1 b:2 rowid:3!null n:4!null sq:5 1207 ├── inner-join (cross) 1208 │ ├── columns: a:1 b:2 rowid:3!null n:4!null sq:5 1209 │ ├── scan pairs 1210 │ │ └── columns: a:1 b:2 rowid:3!null 1211 │ ├── scan square 1212 │ │ └── columns: n:4!null sq:5 1213 │ └── filters (true) 1214 └── filters 1215 └── (a:1 + b:2) = sq:5 1216 1217 # Query similar to the one above, but the filter refers to a rendered 1218 # expression and can't "break through". See the comment for propagateFilters 1219 # in fitler_opt.go for all the details. 1220 build 1221 SELECT a, b, n, sq FROM (SELECT a, b, a + b AS sum, n, sq FROM pairs, square) WHERE sum = sq 1222 ---- 1223 project 1224 ├── columns: a:1 b:2 n:4!null sq:5!null 1225 └── select 1226 ├── columns: a:1 b:2 n:4!null sq:5!null sum:6!null 1227 ├── project 1228 │ ├── columns: sum:6 a:1 b:2 n:4!null sq:5 1229 │ ├── inner-join (cross) 1230 │ │ ├── columns: a:1 b:2 rowid:3!null n:4!null sq:5 1231 │ │ ├── scan pairs 1232 │ │ │ └── columns: a:1 b:2 rowid:3!null 1233 │ │ ├── scan square 1234 │ │ │ └── columns: n:4!null sq:5 1235 │ │ └── filters (true) 1236 │ └── projections 1237 │ └── a:1 + b:2 [as=sum:6] 1238 └── filters 1239 └── sum:6 = sq:5 1240 1241 # The filter expression must stay on top of the outer join. 1242 build 1243 SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq 1244 ---- 1245 project 1246 ├── columns: a:1 b:2 n:4 sq:5 1247 └── full-join (cross) 1248 ├── columns: a:1 b:2 rowid:3 n:4 sq:5 1249 ├── scan pairs 1250 │ └── columns: a:1 b:2 rowid:3!null 1251 ├── scan square 1252 │ └── columns: n:4!null sq:5 1253 └── filters 1254 └── (a:1 + b:2) = sq:5 1255 1256 build 1257 SELECT * FROM pairs FULL OUTER JOIN square ON pairs.a + pairs.b = square.sq WHERE pairs.b%2 <> square.sq%2 1258 ---- 1259 project 1260 ├── columns: a:1 b:2 n:4 sq:5 1261 └── select 1262 ├── columns: a:1 b:2 rowid:3 n:4 sq:5 1263 ├── full-join (cross) 1264 │ ├── columns: a:1 b:2 rowid:3 n:4 sq:5 1265 │ ├── scan pairs 1266 │ │ └── columns: a:1 b:2 rowid:3!null 1267 │ ├── scan square 1268 │ │ └── columns: n:4!null sq:5 1269 │ └── filters 1270 │ └── (a:1 + b:2) = sq:5 1271 └── filters 1272 └── (b:2 % 2) != (sq:5 % 2) 1273 1274 # Filter propagation through outer joins. 1275 1276 build 1277 SELECT * 1278 FROM (SELECT * FROM pairs LEFT JOIN square ON b = sq AND a > 1 AND n < 6) 1279 WHERE b > 1 AND (n IS NULL OR n > 1) AND (n IS NULL OR a < sq) 1280 ---- 1281 select 1282 ├── columns: a:1 b:2!null n:4 sq:5 1283 ├── project 1284 │ ├── columns: a:1 b:2 n:4 sq:5 1285 │ └── left-join (cross) 1286 │ ├── columns: a:1 b:2 rowid:3!null n:4 sq:5 1287 │ ├── scan pairs 1288 │ │ └── columns: a:1 b:2 rowid:3!null 1289 │ ├── scan square 1290 │ │ └── columns: n:4!null sq:5 1291 │ └── filters 1292 │ └── ((b:2 = sq:5) AND (a:1 > 1)) AND (n:4 < 6) 1293 └── filters 1294 └── ((b:2 > 1) AND ((n:4 IS NULL) OR (n:4 > 1))) AND ((n:4 IS NULL) OR (a:1 < sq:5)) 1295 1296 build 1297 SELECT * 1298 FROM (SELECT * FROM pairs RIGHT JOIN square ON b = sq AND a > 1 AND n < 6) 1299 WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq) 1300 ---- 1301 select 1302 ├── columns: a:1 b:2 n:4!null sq:5 1303 ├── project 1304 │ ├── columns: a:1 b:2 n:4!null sq:5 1305 │ └── right-join (cross) 1306 │ ├── columns: a:1 b:2 rowid:3 n:4!null sq:5 1307 │ ├── scan pairs 1308 │ │ └── columns: a:1 b:2 rowid:3!null 1309 │ ├── scan square 1310 │ │ └── columns: n:4!null sq:5 1311 │ └── filters 1312 │ └── ((b:2 = sq:5) AND (a:1 > 1)) AND (n:4 < 6) 1313 └── filters 1314 └── (((a:1 IS NULL) OR (a:1 > 2)) AND (n:4 > 1)) AND ((a:1 IS NULL) OR (a:1 < sq:5)) 1315 1316 # The simpler plan for an inner join, to compare. 1317 build 1318 SELECT * 1319 FROM (SELECT * FROM pairs JOIN square ON b = sq AND a > 1 AND n < 6) 1320 WHERE (a IS NULL OR a > 2) AND n > 1 AND (a IS NULL OR a < sq) 1321 ---- 1322 select 1323 ├── columns: a:1!null b:2!null n:4!null sq:5!null 1324 ├── project 1325 │ ├── columns: a:1!null b:2!null n:4!null sq:5!null 1326 │ └── inner-join (cross) 1327 │ ├── columns: a:1!null b:2!null rowid:3!null n:4!null sq:5!null 1328 │ ├── scan pairs 1329 │ │ └── columns: a:1 b:2 rowid:3!null 1330 │ ├── scan square 1331 │ │ └── columns: n:4!null sq:5 1332 │ └── filters 1333 │ └── ((b:2 = sq:5) AND (a:1 > 1)) AND (n:4 < 6) 1334 └── filters 1335 └── (((a:1 IS NULL) OR (a:1 > 2)) AND (n:4 > 1)) AND ((a:1 IS NULL) OR (a:1 < sq:5)) 1336 1337 1338 exec-ddl 1339 CREATE TABLE t1 (col1 INT, x INT, col2 INT, y INT) 1340 ---- 1341 1342 exec-ddl 1343 CREATE TABLE t2 (col3 INT, y INT, x INT, col4 INT) 1344 ---- 1345 1346 build 1347 SELECT * FROM t1 JOIN t2 USING(x) 1348 ---- 1349 project 1350 ├── columns: x:2!null col1:1 col2:3 y:4 col3:6 y:7 col4:9 1351 └── inner-join (hash) 1352 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4 t1.rowid:5!null col3:6 t2.y:7 t2.x:8!null col4:9 t2.rowid:10!null 1353 ├── scan t1 1354 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1355 ├── scan t2 1356 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1357 └── filters 1358 └── t1.x:2 = t2.x:8 1359 1360 build 1361 SELECT * FROM t1 NATURAL JOIN t2 1362 ---- 1363 project 1364 ├── columns: x:2!null y:4!null col1:1 col2:3 col3:6 col4:9 1365 └── inner-join (hash) 1366 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4!null t1.rowid:5!null col3:6 t2.y:7!null t2.x:8!null col4:9 t2.rowid:10!null 1367 ├── scan t1 1368 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1369 ├── scan t2 1370 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1371 └── filters 1372 ├── t1.x:2 = t2.x:8 1373 └── t1.y:4 = t2.y:7 1374 1375 build 1376 SELECT x, t1.x, t2.x FROM t1 NATURAL JOIN t2 1377 ---- 1378 project 1379 ├── columns: x:2!null x:2!null x:8!null 1380 └── inner-join (hash) 1381 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4!null t1.rowid:5!null col3:6 t2.y:7!null t2.x:8!null col4:9 t2.rowid:10!null 1382 ├── scan t1 1383 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1384 ├── scan t2 1385 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1386 └── filters 1387 ├── t1.x:2 = t2.x:8 1388 └── t1.y:4 = t2.y:7 1389 1390 build 1391 SELECT t1.*, t2.* FROM t1 NATURAL JOIN t2 1392 ---- 1393 project 1394 ├── columns: x:2!null y:4!null col1:1 col2:3 col3:6 col4:9 1395 └── inner-join (hash) 1396 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4!null t1.rowid:5!null col3:6 t2.y:7!null t2.x:8!null col4:9 t2.rowid:10!null 1397 ├── scan t1 1398 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1399 ├── scan t2 1400 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1401 └── filters 1402 ├── t1.x:2 = t2.x:8 1403 └── t1.y:4 = t2.y:7 1404 1405 build 1406 SELECT * FROM t1 JOIN t2 ON t2.x=t1.x 1407 ---- 1408 project 1409 ├── columns: col1:1 x:2!null col2:3 y:4 col3:6 y:7 x:8!null col4:9 1410 └── inner-join (hash) 1411 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4 t1.rowid:5!null col3:6 t2.y:7 t2.x:8!null col4:9 t2.rowid:10!null 1412 ├── scan t1 1413 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1414 ├── scan t2 1415 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1416 └── filters 1417 └── t2.x:8 = t1.x:2 1418 1419 build 1420 SELECT * FROM t1 FULL OUTER JOIN t2 USING(x) 1421 ---- 1422 project 1423 ├── columns: x:11 col1:1 col2:3 y:4 col3:6 y:7 col4:9 1424 └── project 1425 ├── columns: x:11 col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1426 ├── full-join (hash) 1427 │ ├── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1428 │ ├── scan t1 1429 │ │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1430 │ ├── scan t2 1431 │ │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1432 │ └── filters 1433 │ └── t1.x:2 = t2.x:8 1434 └── projections 1435 └── COALESCE(t1.x:2, t2.x:8) [as=x:11] 1436 1437 build 1438 SELECT * FROM t1 NATURAL FULL OUTER JOIN t2 1439 ---- 1440 project 1441 ├── columns: x:11 y:12 col1:1 col2:3 col3:6 col4:9 1442 └── project 1443 ├── columns: x:11 y:12 col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1444 ├── full-join (hash) 1445 │ ├── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1446 │ ├── scan t1 1447 │ │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1448 │ ├── scan t2 1449 │ │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1450 │ └── filters 1451 │ ├── t1.x:2 = t2.x:8 1452 │ └── t1.y:4 = t2.y:7 1453 └── projections 1454 ├── COALESCE(t1.x:2, t2.x:8) [as=x:11] 1455 └── COALESCE(t1.y:4, t2.y:7) [as=y:12] 1456 1457 # Regression: computed columns are not wrapped with Variable outside join. 1458 build 1459 SELECT * FROM (SELECT x, x+1 AS plus1 FROM t1) NATURAL FULL OUTER JOIN (SELECT x, 2 AS two FROM t2) 1460 ---- 1461 project 1462 ├── columns: x:13 plus1:6 two:12 1463 └── project 1464 ├── columns: x:13 t1.x:2 plus1:6 t2.x:9 two:12 1465 ├── full-join (hash) 1466 │ ├── columns: t1.x:2 plus1:6 t2.x:9 two:12 1467 │ ├── project 1468 │ │ ├── columns: plus1:6 t1.x:2 1469 │ │ ├── scan t1 1470 │ │ │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1471 │ │ └── projections 1472 │ │ └── t1.x:2 + 1 [as=plus1:6] 1473 │ ├── project 1474 │ │ ├── columns: two:12!null t2.x:9 1475 │ │ ├── scan t2 1476 │ │ │ └── columns: col3:7 t2.y:8 t2.x:9 col4:10 t2.rowid:11!null 1477 │ │ └── projections 1478 │ │ └── 2 [as=two:12] 1479 │ └── filters 1480 │ └── t1.x:2 = t2.x:9 1481 └── projections 1482 └── COALESCE(t1.x:2, t2.x:9) [as=x:13] 1483 1484 build 1485 SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.x=t2.x 1486 ---- 1487 project 1488 ├── columns: col1:1 x:2 col2:3 y:4 col3:6 y:7 x:8 col4:9 1489 └── full-join (hash) 1490 ├── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1491 ├── scan t1 1492 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1493 ├── scan t2 1494 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1495 └── filters 1496 └── t1.x:2 = t2.x:8 1497 1498 build 1499 SELECT t2.x, t1.x, x FROM t1 JOIN t2 USING(x) 1500 ---- 1501 project 1502 ├── columns: x:8!null x:2!null x:2!null 1503 └── inner-join (hash) 1504 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4 t1.rowid:5!null col3:6 t2.y:7 t2.x:8!null col4:9 t2.rowid:10!null 1505 ├── scan t1 1506 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1507 ├── scan t2 1508 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1509 └── filters 1510 └── t1.x:2 = t2.x:8 1511 1512 build 1513 SELECT t2.x, t1.x, x FROM t1 FULL OUTER JOIN t2 USING(x) 1514 ---- 1515 project 1516 ├── columns: x:8 x:2 x:11 1517 └── project 1518 ├── columns: x:11 col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1519 ├── full-join (hash) 1520 │ ├── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5 col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10 1521 │ ├── scan t1 1522 │ │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1523 │ ├── scan t2 1524 │ │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1525 │ └── filters 1526 │ └── t1.x:2 = t2.x:8 1527 └── projections 1528 └── COALESCE(t1.x:2, t2.x:8) [as=x:11] 1529 1530 # Test for #19536. 1531 build 1532 SELECT x FROM t1 NATURAL JOIN (SELECT * FROM t2) 1533 ---- 1534 project 1535 ├── columns: x:2!null 1536 └── inner-join (hash) 1537 ├── columns: col1:1 t1.x:2!null col2:3 t1.y:4!null t1.rowid:5!null col3:6 t2.y:7!null t2.x:8!null col4:9 1538 ├── scan t1 1539 │ └── columns: col1:1 t1.x:2 col2:3 t1.y:4 t1.rowid:5!null 1540 ├── project 1541 │ ├── columns: col3:6 t2.y:7 t2.x:8 col4:9 1542 │ └── scan t2 1543 │ └── columns: col3:6 t2.y:7 t2.x:8 col4:9 t2.rowid:10!null 1544 └── filters 1545 ├── t1.x:2 = t2.x:8 1546 └── t1.y:4 = t2.y:7 1547 1548 # Tests for merge join ordering information. 1549 exec-ddl 1550 CREATE TABLE pkBA (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a)) 1551 ---- 1552 1553 exec-ddl 1554 CREATE TABLE pkBC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,c)) 1555 ---- 1556 1557 exec-ddl 1558 CREATE TABLE pkBAC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,c)) 1559 ---- 1560 1561 exec-ddl 1562 CREATE TABLE pkBAD (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,d)) 1563 ---- 1564 1565 build 1566 SELECT * FROM pkBA AS l JOIN pkBC AS r ON l.a = r.a AND l.b = r.b AND l.c = r.c 1567 ---- 1568 inner-join (cross) 1569 ├── columns: a:1!null b:2!null c:3!null d:4 a:5!null b:6!null c:7!null d:8 1570 ├── scan l 1571 │ └── columns: l.a:1!null l.b:2!null l.c:3 l.d:4 1572 ├── scan r 1573 │ └── columns: r.a:5 r.b:6!null r.c:7!null r.d:8 1574 └── filters 1575 └── ((l.a:1 = r.a:5) AND (l.b:2 = r.b:6)) AND (l.c:3 = r.c:7) 1576 1577 build 1578 SELECT * FROM pkBA NATURAL JOIN pkBAD 1579 ---- 1580 project 1581 ├── columns: a:1!null b:2!null c:3!null d:4!null 1582 └── inner-join (hash) 1583 ├── columns: pkba.a:1!null pkba.b:2!null pkba.c:3!null pkba.d:4!null pkbad.a:5!null pkbad.b:6!null pkbad.c:7!null pkbad.d:8!null 1584 ├── scan pkba 1585 │ └── columns: pkba.a:1!null pkba.b:2!null pkba.c:3 pkba.d:4 1586 ├── scan pkbad 1587 │ └── columns: pkbad.a:5!null pkbad.b:6!null pkbad.c:7 pkbad.d:8!null 1588 └── filters 1589 ├── pkba.a:1 = pkbad.a:5 1590 ├── pkba.b:2 = pkbad.b:6 1591 ├── pkba.c:3 = pkbad.c:7 1592 └── pkba.d:4 = pkbad.d:8 1593 1594 build 1595 SELECT * FROM pkBAC AS l JOIN pkBAC AS r USING(a, b, c) 1596 ---- 1597 project 1598 ├── columns: a:1!null b:2!null c:3!null d:4 d:8 1599 └── inner-join (hash) 1600 ├── columns: l.a:1!null l.b:2!null l.c:3!null l.d:4 r.a:5!null r.b:6!null r.c:7!null r.d:8 1601 ├── scan l 1602 │ └── columns: l.a:1!null l.b:2!null l.c:3!null l.d:4 1603 ├── scan r 1604 │ └── columns: r.a:5!null r.b:6!null r.c:7!null r.d:8 1605 └── filters 1606 ├── l.a:1 = r.a:5 1607 ├── l.b:2 = r.b:6 1608 └── l.c:3 = r.c:7 1609 1610 build 1611 SELECT * FROM pkBAC AS l JOIN pkBAD AS r ON l.c = r.d AND l.a = r.a AND l.b = r.b 1612 ---- 1613 inner-join (cross) 1614 ├── columns: a:1!null b:2!null c:3!null d:4 a:5!null b:6!null c:7 d:8!null 1615 ├── scan l 1616 │ └── columns: l.a:1!null l.b:2!null l.c:3!null l.d:4 1617 ├── scan r 1618 │ └── columns: r.a:5!null r.b:6!null r.c:7 r.d:8!null 1619 └── filters 1620 └── ((l.c:3 = r.d:8) AND (l.a:1 = r.a:5)) AND (l.b:2 = r.b:6) 1621 1622 # Tests with joins with merged columns of collated string type. 1623 exec-ddl 1624 CREATE TABLE str1 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 1625 ---- 1626 1627 exec-ddl 1628 CREATE TABLE str2 (a INT PRIMARY KEY, s STRING COLLATE en_u_ks_level1) 1629 ---- 1630 1631 build 1632 SELECT s, str1.s, str2.s FROM str1 INNER JOIN str2 USING(s) 1633 ---- 1634 project 1635 ├── columns: s:2!null s:2!null s:4!null 1636 └── inner-join (hash) 1637 ├── columns: str1.a:1!null str1.s:2!null str2.a:3!null str2.s:4!null 1638 ├── scan str1 1639 │ └── columns: str1.a:1!null str1.s:2 1640 ├── scan str2 1641 │ └── columns: str2.a:3!null str2.s:4 1642 └── filters 1643 └── str1.s:2 = str2.s:4 1644 1645 build 1646 SELECT s, str1.s, str2.s FROM str1 LEFT OUTER JOIN str2 USING(s) 1647 ---- 1648 project 1649 ├── columns: s:2 s:2 s:4 1650 └── left-join (hash) 1651 ├── columns: str1.a:1!null str1.s:2 str2.a:3 str2.s:4 1652 ├── scan str1 1653 │ └── columns: str1.a:1!null str1.s:2 1654 ├── scan str2 1655 │ └── columns: str2.a:3!null str2.s:4 1656 └── filters 1657 └── str1.s:2 = str2.s:4 1658 1659 build 1660 SELECT s, str1.s, str2.s FROM str1 RIGHT OUTER JOIN str2 USING(s) 1661 ---- 1662 project 1663 ├── columns: s:5 s:2 s:4 1664 └── project 1665 ├── columns: s:5 str1.a:1 str1.s:2 str2.a:3!null str2.s:4 1666 ├── right-join (hash) 1667 │ ├── columns: str1.a:1 str1.s:2 str2.a:3!null str2.s:4 1668 │ ├── scan str1 1669 │ │ └── columns: str1.a:1!null str1.s:2 1670 │ ├── scan str2 1671 │ │ └── columns: str2.a:3!null str2.s:4 1672 │ └── filters 1673 │ └── str1.s:2 = str2.s:4 1674 └── projections 1675 └── COALESCE(str1.s:2, str2.s:4) [as=s:5] 1676 1677 build 1678 SELECT s, str1.s, str2.s FROM str1 FULL OUTER JOIN str2 USING(s) 1679 ---- 1680 project 1681 ├── columns: s:5 s:2 s:4 1682 └── project 1683 ├── columns: s:5 str1.a:1 str1.s:2 str2.a:3 str2.s:4 1684 ├── full-join (hash) 1685 │ ├── columns: str1.a:1 str1.s:2 str2.a:3 str2.s:4 1686 │ ├── scan str1 1687 │ │ └── columns: str1.a:1!null str1.s:2 1688 │ ├── scan str2 1689 │ │ └── columns: str2.a:3!null str2.s:4 1690 │ └── filters 1691 │ └── str1.s:2 = str2.s:4 1692 └── projections 1693 └── COALESCE(str1.s:2, str2.s:4) [as=s:5] 1694 1695 # Verify that we resolve the merged column a to str2.a but use IFNULL for 1696 # column s which is a collated string. 1697 build 1698 SELECT * FROM str1 RIGHT OUTER JOIN str2 USING(a, s) 1699 ---- 1700 project 1701 ├── columns: a:3!null s:5 1702 └── project 1703 ├── columns: s:5 str1.a:1 str1.s:2 str2.a:3!null str2.s:4 1704 ├── right-join (hash) 1705 │ ├── columns: str1.a:1 str1.s:2 str2.a:3!null str2.s:4 1706 │ ├── scan str1 1707 │ │ └── columns: str1.a:1!null str1.s:2 1708 │ ├── scan str2 1709 │ │ └── columns: str2.a:3!null str2.s:4 1710 │ └── filters 1711 │ ├── str1.a:1 = str2.a:3 1712 │ └── str1.s:2 = str2.s:4 1713 └── projections 1714 └── COALESCE(str1.s:2, str2.s:4) [as=s:5] 1715 1716 1717 exec-ddl 1718 CREATE TABLE xyu (x INT, y INT, u INT, PRIMARY KEY(x,y,u)) 1719 ---- 1720 1721 exec-ddl 1722 CREATE TABLE xyv (x INT, y INT, v INT, PRIMARY KEY(x,y,v)) 1723 ---- 1724 1725 build 1726 SELECT * FROM xyu INNER JOIN xyv USING(x, y) WHERE x > 2 1727 ---- 1728 project 1729 ├── columns: x:1!null y:2!null u:3!null v:6!null 1730 └── select 1731 ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4!null xyv.y:5!null v:6!null 1732 ├── inner-join (hash) 1733 │ ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4!null xyv.y:5!null v:6!null 1734 │ ├── scan xyu 1735 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1736 │ ├── scan xyv 1737 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1738 │ └── filters 1739 │ ├── xyu.x:1 = xyv.x:4 1740 │ └── xyu.y:2 = xyv.y:5 1741 └── filters 1742 └── xyu.x:1 > 2 1743 1744 build 1745 SELECT * FROM xyu LEFT OUTER JOIN xyv USING(x, y) WHERE x > 2 1746 ---- 1747 project 1748 ├── columns: x:1!null y:2!null u:3!null v:6 1749 └── select 1750 ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4 xyv.y:5 v:6 1751 ├── left-join (hash) 1752 │ ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4 xyv.y:5 v:6 1753 │ ├── scan xyu 1754 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1755 │ ├── scan xyv 1756 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1757 │ └── filters 1758 │ ├── xyu.x:1 = xyv.x:4 1759 │ └── xyu.y:2 = xyv.y:5 1760 └── filters 1761 └── xyu.x:1 > 2 1762 1763 build 1764 SELECT * FROM xyu RIGHT OUTER JOIN xyv USING(x, y) WHERE x > 2 1765 ---- 1766 project 1767 ├── columns: x:4!null y:5!null u:3 v:6!null 1768 └── select 1769 ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4!null xyv.y:5!null v:6!null 1770 ├── right-join (hash) 1771 │ ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4!null xyv.y:5!null v:6!null 1772 │ ├── scan xyu 1773 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1774 │ ├── scan xyv 1775 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1776 │ └── filters 1777 │ ├── xyu.x:1 = xyv.x:4 1778 │ └── xyu.y:2 = xyv.y:5 1779 └── filters 1780 └── xyv.x:4 > 2 1781 1782 build 1783 SELECT * FROM xyu FULL OUTER JOIN xyv USING(x, y) WHERE x > 2 1784 ---- 1785 project 1786 ├── columns: x:7!null y:8 u:3 v:6 1787 └── select 1788 ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 x:7!null y:8 1789 ├── project 1790 │ ├── columns: x:7 y:8 xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 1791 │ ├── full-join (hash) 1792 │ │ ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 1793 │ │ ├── scan xyu 1794 │ │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1795 │ │ ├── scan xyv 1796 │ │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1797 │ │ └── filters 1798 │ │ ├── xyu.x:1 = xyv.x:4 1799 │ │ └── xyu.y:2 = xyv.y:5 1800 │ └── projections 1801 │ ├── COALESCE(xyu.x:1, xyv.x:4) [as=x:7] 1802 │ └── COALESCE(xyu.y:2, xyv.y:5) [as=y:8] 1803 └── filters 1804 └── x:7 > 2 1805 1806 # Verify that we transfer constraints between the two sides. 1807 build 1808 SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y WHERE xyu.x = 1 AND xyu.y < 10 1809 ---- 1810 select 1811 ├── columns: x:1!null y:2!null u:3!null x:4!null y:5!null v:6!null 1812 ├── inner-join (cross) 1813 │ ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4!null xyv.y:5!null v:6!null 1814 │ ├── scan xyu 1815 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1816 │ ├── scan xyv 1817 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1818 │ └── filters 1819 │ └── (xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5) 1820 └── filters 1821 └── (xyu.x:1 = 1) AND (xyu.y:2 < 10) 1822 1823 build 1824 SELECT * FROM xyu INNER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10 1825 ---- 1826 inner-join (cross) 1827 ├── columns: x:1!null y:2!null u:3!null x:4!null y:5!null v:6!null 1828 ├── scan xyu 1829 │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1830 ├── scan xyv 1831 │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1832 └── filters 1833 └── (((xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5)) AND (xyu.x:1 = 1)) AND (xyu.y:2 < 10) 1834 1835 build 1836 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 1837 ---- 1838 left-join (cross) 1839 ├── columns: x:1!null y:2!null u:3!null x:4 y:5 v:6 1840 ├── scan xyu 1841 │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1842 ├── scan xyv 1843 │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1844 └── filters 1845 └── (((xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5)) AND (xyu.x:1 = 1)) AND (xyu.y:2 < 10) 1846 1847 build 1848 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 1849 ---- 1850 right-join (cross) 1851 ├── columns: x:1 y:2 u:3 x:4!null y:5!null v:6!null 1852 ├── scan xyu 1853 │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1854 ├── scan xyv 1855 │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1856 └── filters 1857 └── (((xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5)) AND (xyu.x:1 = 1)) AND (xyu.y:2 < 10) 1858 1859 1860 # Test OUTER joins that are run in the distSQL merge joiner 1861 1862 build 1863 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 1864 ---- 1865 project 1866 ├── columns: x:1!null y:2!null u:3!null v:6 1867 └── select 1868 ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4 xyv.y:5 v:6 1869 ├── left-join (hash) 1870 │ ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4 xyv.y:5 v:6 1871 │ ├── scan xyu 1872 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1873 │ ├── scan xyv 1874 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1875 │ └── filters 1876 │ ├── xyu.x:1 = xyv.x:4 1877 │ └── xyu.y:2 = xyv.y:5 1878 └── filters 1879 └── xyu.x:1 > 2 1880 1881 build 1882 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 1883 ---- 1884 project 1885 ├── columns: x:4!null y:5!null u:3 v:6!null 1886 └── select 1887 ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4!null xyv.y:5!null v:6!null 1888 ├── right-join (hash) 1889 │ ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4!null xyv.y:5!null v:6!null 1890 │ ├── scan xyu 1891 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1892 │ ├── scan xyv 1893 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1894 │ └── filters 1895 │ ├── xyu.x:1 = xyv.x:4 1896 │ └── xyu.y:2 = xyv.y:5 1897 └── filters 1898 └── xyv.x:4 > 2 1899 1900 build 1901 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 1902 ---- 1903 project 1904 ├── columns: x:7!null y:8 u:3 v:6 1905 └── select 1906 ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 x:7!null y:8 1907 ├── project 1908 │ ├── columns: x:7 y:8 xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 1909 │ ├── full-join (hash) 1910 │ │ ├── columns: xyu.x:1 xyu.y:2 u:3 xyv.x:4 xyv.y:5 v:6 1911 │ │ ├── scan xyu 1912 │ │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1913 │ │ ├── scan xyv 1914 │ │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1915 │ │ └── filters 1916 │ │ ├── xyu.x:1 = xyv.x:4 1917 │ │ └── xyu.y:2 = xyv.y:5 1918 │ └── projections 1919 │ ├── COALESCE(xyu.x:1, xyv.x:4) [as=x:7] 1920 │ └── COALESCE(xyu.y:2, xyv.y:5) [as=y:8] 1921 └── filters 1922 └── x:7 > 2 1923 1924 build 1925 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 1926 ---- 1927 left-join (cross) 1928 ├── columns: x:1!null y:2!null u:3!null x:4 y:5 v:6 1929 ├── scan xyu 1930 │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1931 ├── scan xyv 1932 │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1933 └── filters 1934 └── (((xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5)) AND (xyu.x:1 = 1)) AND (xyu.y:2 < 10) 1935 1936 build 1937 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 1938 ---- 1939 right-join (cross) 1940 ├── columns: x:1 y:2 u:3 x:4!null y:5!null v:6!null 1941 ├── scan xyu 1942 │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1943 ├── scan xyv 1944 │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1945 └── filters 1946 └── (((xyu.x:1 = xyv.x:4) AND (xyu.y:2 = xyv.y:5)) AND (xyu.x:1 = 1)) AND (xyu.y:2 < 10) 1947 1948 # Regression test for #20472: break up tuple inequalities. 1949 build 1950 SELECT * FROM xyu JOIN xyv USING(x, y) WHERE (x, y, u) > (1, 2, 3) 1951 ---- 1952 project 1953 ├── columns: x:1!null y:2!null u:3!null v:6!null 1954 └── select 1955 ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4!null xyv.y:5!null v:6!null 1956 ├── inner-join (hash) 1957 │ ├── columns: xyu.x:1!null xyu.y:2!null u:3!null xyv.x:4!null xyv.y:5!null v:6!null 1958 │ ├── scan xyu 1959 │ │ └── columns: xyu.x:1!null xyu.y:2!null u:3!null 1960 │ ├── scan xyv 1961 │ │ └── columns: xyv.x:4!null xyv.y:5!null v:6!null 1962 │ └── filters 1963 │ ├── xyu.x:1 = xyv.x:4 1964 │ └── xyu.y:2 = xyv.y:5 1965 └── filters 1966 └── (xyu.x:1, xyu.y:2, u:3) > (1, 2, 3) 1967 1968 1969 # Regression test for #20858. 1970 1971 exec-ddl 1972 CREATE TABLE l (a INT PRIMARY KEY) 1973 ---- 1974 1975 exec-ddl 1976 CREATE TABLE r (a INT PRIMARY KEY) 1977 ---- 1978 1979 build 1980 SELECT * FROM l LEFT OUTER JOIN r ON l.a = r.a WHERE l.a = 3; 1981 ---- 1982 select 1983 ├── columns: a:1!null a:2 1984 ├── left-join (hash) 1985 │ ├── columns: l.a:1!null r.a:2 1986 │ ├── scan l 1987 │ │ └── columns: l.a:1!null 1988 │ ├── scan r 1989 │ │ └── columns: r.a:2!null 1990 │ └── filters 1991 │ └── l.a:1 = r.a:2 1992 └── filters 1993 └── l.a:1 = 3 1994 1995 build 1996 SELECT * FROM l RIGHT OUTER JOIN r ON l.a = r.a WHERE r.a = 3; 1997 ---- 1998 select 1999 ├── columns: a:1 a:2!null 2000 ├── right-join (hash) 2001 │ ├── columns: l.a:1 r.a:2!null 2002 │ ├── scan l 2003 │ │ └── columns: l.a:1!null 2004 │ ├── scan r 2005 │ │ └── columns: r.a:2!null 2006 │ └── filters 2007 │ └── l.a:1 = r.a:2 2008 └── filters 2009 └── r.a:2 = 3 2010 2011 build 2012 SELECT * FROM l LEFT OUTER JOIN r USING(a) WHERE a = 1 2013 ---- 2014 project 2015 ├── columns: a:1!null 2016 └── select 2017 ├── columns: l.a:1!null r.a:2 2018 ├── left-join (hash) 2019 │ ├── columns: l.a:1!null r.a:2 2020 │ ├── scan l 2021 │ │ └── columns: l.a:1!null 2022 │ ├── scan r 2023 │ │ └── columns: r.a:2!null 2024 │ └── filters 2025 │ └── l.a:1 = r.a:2 2026 └── filters 2027 └── l.a:1 = 1 2028 2029 build 2030 SELECT * FROM l RIGHT OUTER JOIN r USING(a) WHERE a = 3 2031 ---- 2032 project 2033 ├── columns: a:2!null 2034 └── select 2035 ├── columns: l.a:1 r.a:2!null 2036 ├── right-join (hash) 2037 │ ├── columns: l.a:1 r.a:2!null 2038 │ ├── scan l 2039 │ │ └── columns: l.a:1!null 2040 │ ├── scan r 2041 │ │ └── columns: r.a:2!null 2042 │ └── filters 2043 │ └── l.a:1 = r.a:2 2044 └── filters 2045 └── r.a:2 = 3 2046 2047 # Regression tests for #21243 2048 exec-ddl 2049 CREATE TABLE abcdef ( 2050 a INT NOT NULL, 2051 b INT NOT NULL, 2052 c INT NOT NULL, 2053 d INT NOT NULL, 2054 e INT NULL, 2055 f INT NULL, 2056 PRIMARY KEY (a ASC, b ASC, c DESC, d ASC) 2057 ) 2058 ---- 2059 2060 exec-ddl 2061 CREATE TABLE abg ( 2062 a INT NOT NULL, 2063 b INT NOT NULL, 2064 g INT NULL, 2065 PRIMARY KEY (a ASC, b ASC) 2066 ); 2067 ---- 2068 2069 build 2070 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)) 2071 ---- 2072 project 2073 ├── columns: a:1!null b:2!null c:3!null d:4!null e:5 f:6 g:9 2074 └── select 2075 ├── columns: abcdef.a:1!null abcdef.b:2!null c:3!null d:4!null e:5 f:6 abg.a:7!null abg.b:8!null g:9 2076 ├── inner-join (hash) 2077 │ ├── columns: abcdef.a:1!null abcdef.b:2!null c:3!null d:4!null e:5 f:6 abg.a:7!null abg.b:8!null g:9 2078 │ ├── scan abcdef 2079 │ │ └── columns: abcdef.a:1!null abcdef.b:2!null c:3!null d:4!null e:5 f:6 2080 │ ├── scan abg 2081 │ │ └── columns: abg.a:7!null abg.b:8!null g:9 2082 │ └── filters 2083 │ ├── abcdef.a:1 = abg.a:7 2084 │ └── abcdef.b:2 = abg.b:8 2085 └── filters 2086 └── (((abcdef.a:1, abcdef.b:2) > (1, 2)) OR (((abcdef.a:1, abcdef.b:2) = (1, 2)) AND (c:3 < 6))) OR (((abcdef.a:1, abcdef.b:2, c:3) = (1, 2, 6)) AND (d:4 > 8)) 2087 2088 # Regression tests for mixed-type equality columns (#22514). 2089 exec-ddl 2090 CREATE TABLE foo ( 2091 a INT, 2092 b INT, 2093 c FLOAT, 2094 d FLOAT 2095 ) 2096 ---- 2097 2098 exec-ddl 2099 CREATE TABLE bar ( 2100 a INT, 2101 b FLOAT, 2102 c FLOAT, 2103 d INT 2104 ) 2105 ---- 2106 2107 # Only a and c can be equality columns. 2108 build 2109 SELECT * FROM foo NATURAL JOIN bar 2110 ---- 2111 project 2112 ├── columns: a:1!null b:2!null c:3!null d:4!null 2113 └── inner-join (hash) 2114 ├── columns: foo.a:1!null foo.b:2!null foo.c:3!null foo.d:4!null foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8!null bar.d:9!null bar.rowid:10!null 2115 ├── scan foo 2116 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2117 ├── scan bar 2118 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2119 └── filters 2120 ├── foo.a:1 = bar.a:6 2121 ├── foo.b:2 = bar.b:7 2122 ├── foo.c:3 = bar.c:8 2123 └── foo.d:4 = bar.d:9 2124 2125 # b can't be an equality column. 2126 build 2127 SELECT * FROM foo JOIN bar USING (b) 2128 ---- 2129 project 2130 ├── columns: b:2!null a:1 c:3 d:4 a:6 c:8 d:9 2131 └── inner-join (cross) 2132 ├── columns: foo.a:1 foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6 bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2133 ├── scan foo 2134 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2135 ├── scan bar 2136 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2137 └── filters 2138 └── foo.b:2 = bar.b:7 2139 2140 # Only a can be an equality column. 2141 build 2142 SELECT * FROM foo JOIN bar USING (a, b) 2143 ---- 2144 project 2145 ├── columns: a:1!null b:2!null c:3 d:4 c:8 d:9 2146 └── inner-join (hash) 2147 ├── columns: foo.a:1!null foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2148 ├── scan foo 2149 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2150 ├── scan bar 2151 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2152 └── filters 2153 ├── foo.a:1 = bar.a:6 2154 └── foo.b:2 = bar.b:7 2155 2156 # Only a and c can be equality columns. 2157 build 2158 SELECT * FROM foo JOIN bar USING (a, b, c) 2159 ---- 2160 project 2161 ├── columns: a:1!null b:2!null c:3!null d:4 d:9 2162 └── inner-join (hash) 2163 ├── columns: foo.a:1!null foo.b:2!null foo.c:3!null foo.d:4 foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8!null bar.d:9 bar.rowid:10!null 2164 ├── scan foo 2165 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2166 ├── scan bar 2167 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2168 └── filters 2169 ├── foo.a:1 = bar.a:6 2170 ├── foo.b:2 = bar.b:7 2171 └── foo.c:3 = bar.c:8 2172 2173 # b can't be an equality column. 2174 build 2175 SELECT * FROM foo JOIN bar ON foo.b = bar.b 2176 ---- 2177 project 2178 ├── columns: a:1 b:2!null c:3 d:4 a:6 b:7!null c:8 d:9 2179 └── inner-join (cross) 2180 ├── columns: foo.a:1 foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6 bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2181 ├── scan foo 2182 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2183 ├── scan bar 2184 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2185 └── filters 2186 └── foo.b:2 = bar.b:7 2187 2188 # Only a can be an equality column. 2189 build 2190 SELECT * FROM foo JOIN bar ON foo.a = bar.a AND foo.b = bar.b 2191 ---- 2192 project 2193 ├── columns: a:1!null b:2!null c:3 d:4 a:6!null b:7!null c:8 d:9 2194 └── inner-join (cross) 2195 ├── columns: foo.a:1!null foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2196 ├── scan foo 2197 │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2198 ├── scan bar 2199 │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2200 └── filters 2201 └── (foo.a:1 = bar.a:6) AND (foo.b:2 = bar.b:7) 2202 2203 build 2204 SELECT * FROM foo, bar WHERE foo.b = bar.b 2205 ---- 2206 project 2207 ├── columns: a:1 b:2!null c:3 d:4 a:6 b:7!null c:8 d:9 2208 └── select 2209 ├── columns: foo.a:1 foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6 bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2210 ├── inner-join (cross) 2211 │ ├── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2212 │ ├── scan foo 2213 │ │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2214 │ ├── scan bar 2215 │ │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2216 │ └── filters (true) 2217 └── filters 2218 └── foo.b:2 = bar.b:7 2219 2220 # Only a can be an equality column. 2221 build 2222 SELECT * FROM foo, bar WHERE foo.a = bar.a AND foo.b = bar.b 2223 ---- 2224 project 2225 ├── columns: a:1!null b:2!null c:3 d:4 a:6!null b:7!null c:8 d:9 2226 └── select 2227 ├── columns: foo.a:1!null foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2228 ├── inner-join (cross) 2229 │ ├── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2230 │ ├── scan foo 2231 │ │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2232 │ ├── scan bar 2233 │ │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2234 │ └── filters (true) 2235 └── filters 2236 └── (foo.a:1 = bar.a:6) AND (foo.b:2 = bar.b:7) 2237 2238 # Only a and c can be equality columns. 2239 build 2240 SELECT * FROM foo JOIN bar USING (a, b) WHERE foo.c = bar.c AND foo.d = bar.d 2241 ---- 2242 project 2243 ├── columns: a:1!null b:2!null c:3!null d:4!null c:8!null d:9!null 2244 └── select 2245 ├── columns: foo.a:1!null foo.b:2!null foo.c:3!null foo.d:4!null foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8!null bar.d:9!null bar.rowid:10!null 2246 ├── inner-join (hash) 2247 │ ├── columns: foo.a:1!null foo.b:2!null foo.c:3 foo.d:4 foo.rowid:5!null bar.a:6!null bar.b:7!null bar.c:8 bar.d:9 bar.rowid:10!null 2248 │ ├── scan foo 2249 │ │ └── columns: foo.a:1 foo.b:2 foo.c:3 foo.d:4 foo.rowid:5!null 2250 │ ├── scan bar 2251 │ │ └── columns: bar.a:6 bar.b:7 bar.c:8 bar.d:9 bar.rowid:10!null 2252 │ └── filters 2253 │ ├── foo.a:1 = bar.a:6 2254 │ └── foo.b:2 = bar.b:7 2255 └── filters 2256 └── (foo.c:3 = bar.c:8) AND (foo.d:4 = bar.d:9) 2257 2258 exec-ddl 2259 CREATE TABLE t.kv ( 2260 k INT PRIMARY KEY, 2261 v INT, 2262 w INT, 2263 s STRING 2264 ) 2265 ---- 2266 2267 build 2268 SELECT k FROM kv, (SELECT 1 AS k) 2269 ---- 2270 project 2271 ├── columns: k:5!null 2272 └── inner-join (cross) 2273 ├── columns: kv.k:1!null v:2 w:3 s:4 k:5!null 2274 ├── scan kv 2275 │ └── columns: kv.k:1!null v:2 w:3 s:4 2276 ├── project 2277 │ ├── columns: k:5!null 2278 │ ├── values 2279 │ │ └── () 2280 │ └── projections 2281 │ └── 1 [as=k:5] 2282 └── filters (true) 2283 2284 build 2285 select * from (select 1 as k), (select 2 as k) where 1 in (select k from kv) 2286 ---- 2287 select 2288 ├── columns: k:1!null k:2!null 2289 ├── inner-join (cross) 2290 │ ├── columns: k:1!null k:2!null 2291 │ ├── project 2292 │ │ ├── columns: k:1!null 2293 │ │ ├── values 2294 │ │ │ └── () 2295 │ │ └── projections 2296 │ │ └── 1 [as=k:1] 2297 │ ├── project 2298 │ │ ├── columns: k:2!null 2299 │ │ ├── values 2300 │ │ │ └── () 2301 │ │ └── projections 2302 │ │ └── 2 [as=k:2] 2303 │ └── filters (true) 2304 └── filters 2305 └── any: eq 2306 ├── project 2307 │ ├── columns: kv.k:3!null 2308 │ └── scan kv 2309 │ └── columns: kv.k:3!null v:4 w:5 s:6 2310 └── 1 2311 2312 # Test natural outer join when the left side has unknown type 2313 build 2314 SELECT * FROM (VALUES (NULL, NULL)) NATURAL FULL OUTER JOIN (SELECT * FROM (VALUES (1, 1))) 2315 ---- 2316 project 2317 ├── columns: column1:5 column2:6 2318 └── project 2319 ├── columns: column1:5 column2:6 column1:1 column2:2 column1:3 column2:4 2320 ├── full-join (cross) 2321 │ ├── columns: column1:1 column2:2 column1:3 column2:4 2322 │ ├── values 2323 │ │ ├── columns: column1:1 column2:2 2324 │ │ └── (NULL, NULL) 2325 │ ├── values 2326 │ │ ├── columns: column1:3!null column2:4!null 2327 │ │ └── (1, 1) 2328 │ └── filters 2329 │ ├── column1:1 = column1:3 2330 │ └── column2:2 = column2:4 2331 └── projections 2332 ├── COALESCE(column1:1, column1:3) [as=column1:5] 2333 └── COALESCE(column2:2, column2:4) [as=column2:6] 2334 2335 # Regression test for #23609: make sure that the type of the merged column 2336 # is int (not unknown). 2337 build 2338 SELECT column1, column1+1 AS r 2339 FROM 2340 (SELECT * FROM 2341 (VALUES (NULL, NULL)) AS t 2342 NATURAL FULL OUTER JOIN 2343 (VALUES (1, 1)) AS u) 2344 ---- 2345 project 2346 ├── columns: column1:5 r:7 2347 ├── project 2348 │ ├── columns: column1:5 column2:6 2349 │ └── project 2350 │ ├── columns: column1:5 column2:6 column1:1 column2:2 column1:3 column2:4 2351 │ ├── full-join (cross) 2352 │ │ ├── columns: column1:1 column2:2 column1:3 column2:4 2353 │ │ ├── values 2354 │ │ │ ├── columns: column1:1 column2:2 2355 │ │ │ └── (NULL, NULL) 2356 │ │ ├── values 2357 │ │ │ ├── columns: column1:3!null column2:4!null 2358 │ │ │ └── (1, 1) 2359 │ │ └── filters 2360 │ │ ├── column1:1 = column1:3 2361 │ │ └── column2:2 = column2:4 2362 │ └── projections 2363 │ ├── COALESCE(column1:1, column1:3) [as=column1:5] 2364 │ └── COALESCE(column2:2, column2:4) [as=column2:6] 2365 └── projections 2366 └── column1:5 + 1 [as=r:7] 2367 2368 # ON clause must be type bool. 2369 build 2370 SELECT * FROM foo JOIN bar ON foo.c 2371 ---- 2372 error (42804): argument of ON must be type bool, not type float 2373 2374 # Regression test for #28817. Do not allow special functions in ON clause. 2375 build 2376 SELECT * FROM foo JOIN bar ON generate_series(0, 1) < 2 2377 ---- 2378 error (0A000): generate_series(): generator functions are not allowed in ON 2379 2380 build 2381 SELECT * FROM foo JOIN bar ON max(foo.c) < 2 2382 ---- 2383 error (42803): aggregate functions are not allowed in JOIN conditions 2384 2385 # Verify join hints get populated. 2386 build 2387 SELECT * FROM onecolumn AS a(x) INNER MERGE JOIN onecolumn AS b(y) ON a.x = b.y 2388 ---- 2389 project 2390 ├── columns: x:1!null y:3!null 2391 └── inner-join (hash) 2392 ├── columns: x:1!null a.rowid:2!null y:3!null b.rowid:4!null 2393 ├── flags: force merge join 2394 ├── scan a 2395 │ └── columns: x:1 a.rowid:2!null 2396 ├── scan b 2397 │ └── columns: y:3 b.rowid:4!null 2398 └── filters 2399 └── x:1 = y:3 2400 2401 build 2402 SELECT * FROM onecolumn AS a NATURAL LEFT LOOKUP JOIN onecolumn as b USING(x) 2403 ---- 2404 error (42601): at or near "using": syntax error 2405 2406 build 2407 SELECT * FROM onecolumn AS a(x) FULL OUTER HASH JOIN onecolumn AS b(y) ON a.x = b.y 2408 ---- 2409 project 2410 ├── columns: x:1 y:3 2411 └── full-join (hash) 2412 ├── columns: x:1 a.rowid:2 y:3 b.rowid:4 2413 ├── flags: force hash join (store right side) 2414 ├── scan a 2415 │ └── columns: x:1 a.rowid:2!null 2416 ├── scan b 2417 │ └── columns: y:3 b.rowid:4!null 2418 └── filters 2419 └── x:1 = y:3 2420 2421 # Regression test for #46403. 2422 exec-ddl 2423 CREATE TABLE t0(c0 INT) 2424 ---- 2425 2426 exec-ddl 2427 CREATE VIEW v0(c0, c1) AS SELECT DISTINCT c0, c0 FROM t0 2428 ---- 2429 2430 build 2431 SELECT * FROM v0 NATURAL JOIN t0 2432 ---- 2433 project 2434 ├── columns: c0:1!null c1:1!null 2435 └── inner-join (hash) 2436 ├── columns: c0:1!null c0:3!null rowid:4!null 2437 ├── distinct-on 2438 │ ├── columns: c0:1 2439 │ ├── grouping columns: c0:1 2440 │ └── project 2441 │ ├── columns: c0:1 2442 │ └── scan t0 2443 │ └── columns: c0:1 rowid:2!null 2444 ├── scan t0 2445 │ └── columns: c0:3 rowid:4!null 2446 └── filters 2447 └── c0:1 = c0:3 2448 2449 build 2450 SELECT * FROM t0 NATURAL JOIN v0 2451 ---- 2452 project 2453 ├── columns: c0:1!null c1:3!null 2454 └── inner-join (hash) 2455 ├── columns: c0:1!null rowid:2!null c0:3!null 2456 ├── scan t0 2457 │ └── columns: c0:1 rowid:2!null 2458 ├── distinct-on 2459 │ ├── columns: c0:3 2460 │ ├── grouping columns: c0:3 2461 │ └── project 2462 │ ├── columns: c0:3 2463 │ └── scan t0 2464 │ └── columns: c0:3 rowid:4!null 2465 └── filters 2466 └── c0:1 = c0:3 2467 2468 build 2469 SELECT * FROM v0 NATURAL JOIN v0 AS v1 2470 ---- 2471 project 2472 ├── columns: c0:1!null c1:1!null 2473 └── inner-join (hash) 2474 ├── columns: c0:1!null c0:3!null 2475 ├── distinct-on 2476 │ ├── columns: c0:1 2477 │ ├── grouping columns: c0:1 2478 │ └── project 2479 │ ├── columns: c0:1 2480 │ └── scan t0 2481 │ └── columns: c0:1 rowid:2!null 2482 ├── distinct-on 2483 │ ├── columns: c0:3 2484 │ ├── grouping columns: c0:3 2485 │ └── project 2486 │ ├── columns: c0:3 2487 │ └── scan t0 2488 │ └── columns: c0:3 rowid:4!null 2489 └── filters 2490 ├── c0:1 = c0:3 2491 └── c0:1 = c0:3 2492 2493 build 2494 SELECT * FROM v0 NATURAL LEFT JOIN v0 AS v1 2495 ---- 2496 project 2497 ├── columns: c0:1 c1:1 2498 └── left-join (hash) 2499 ├── columns: c0:1 c0:3 2500 ├── distinct-on 2501 │ ├── columns: c0:1 2502 │ ├── grouping columns: c0:1 2503 │ └── project 2504 │ ├── columns: c0:1 2505 │ └── scan t0 2506 │ └── columns: c0:1 rowid:2!null 2507 ├── distinct-on 2508 │ ├── columns: c0:3 2509 │ ├── grouping columns: c0:3 2510 │ └── project 2511 │ ├── columns: c0:3 2512 │ └── scan t0 2513 │ └── columns: c0:3 rowid:4!null 2514 └── filters 2515 ├── c0:1 = c0:3 2516 └── c0:1 = c0:3 2517 2518 build 2519 SELECT * FROM v0 NATURAL RIGHT JOIN v0 AS v1 2520 ---- 2521 project 2522 ├── columns: c0:3 c1:3 2523 └── right-join (hash) 2524 ├── columns: c0:1 c0:3 2525 ├── distinct-on 2526 │ ├── columns: c0:1 2527 │ ├── grouping columns: c0:1 2528 │ └── project 2529 │ ├── columns: c0:1 2530 │ └── scan t0 2531 │ └── columns: c0:1 rowid:2!null 2532 ├── distinct-on 2533 │ ├── columns: c0:3 2534 │ ├── grouping columns: c0:3 2535 │ └── project 2536 │ ├── columns: c0:3 2537 │ └── scan t0 2538 │ └── columns: c0:3 rowid:4!null 2539 └── filters 2540 ├── c0:1 = c0:3 2541 └── c0:1 = c0:3 2542 2543 build 2544 SELECT * FROM v0 NATURAL FULL OUTER JOIN v0 AS v1 2545 ---- 2546 project 2547 ├── columns: c0:5 c1:6 2548 └── project 2549 ├── columns: c0:5 c1:6 t0.c0:1 t0.c0:3 2550 ├── full-join (hash) 2551 │ ├── columns: t0.c0:1 t0.c0:3 2552 │ ├── distinct-on 2553 │ │ ├── columns: t0.c0:1 2554 │ │ ├── grouping columns: t0.c0:1 2555 │ │ └── project 2556 │ │ ├── columns: t0.c0:1 2557 │ │ └── scan t0 2558 │ │ └── columns: t0.c0:1 rowid:2!null 2559 │ ├── distinct-on 2560 │ │ ├── columns: t0.c0:3 2561 │ │ ├── grouping columns: t0.c0:3 2562 │ │ └── project 2563 │ │ ├── columns: t0.c0:3 2564 │ │ └── scan t0 2565 │ │ └── columns: t0.c0:3 rowid:4!null 2566 │ └── filters 2567 │ ├── t0.c0:1 = t0.c0:3 2568 │ └── t0.c0:1 = t0.c0:3 2569 └── projections 2570 ├── COALESCE(t0.c0:1, t0.c0:3) [as=c0:5] 2571 └── COALESCE(t0.c0:1, t0.c0:3) [as=c1:6] 2572 2573 build 2574 SELECT * FROM (SELECT DISTINCT c0, c0 FROM t0) AS v1(c0, c1) NATURAL JOIN t0 2575 ---- 2576 project 2577 ├── columns: c0:1!null c1:1!null 2578 └── inner-join (hash) 2579 ├── columns: c0:1!null c0:3!null rowid:4!null 2580 ├── distinct-on 2581 │ ├── columns: c0:1 2582 │ ├── grouping columns: c0:1 2583 │ └── project 2584 │ ├── columns: c0:1 2585 │ └── scan t0 2586 │ └── columns: c0:1 rowid:2!null 2587 ├── scan t0 2588 │ └── columns: c0:3 rowid:4!null 2589 └── filters 2590 └── c0:1 = c0:3 2591 2592 build 2593 SELECT * FROM v0 JOIN v0 AS v1 USING (c0) 2594 ---- 2595 inner-join (hash) 2596 ├── columns: c0:1!null c1:1!null c1:3!null 2597 ├── distinct-on 2598 │ ├── columns: c0:1 2599 │ ├── grouping columns: c0:1 2600 │ └── project 2601 │ ├── columns: c0:1 2602 │ └── scan t0 2603 │ └── columns: c0:1 rowid:2!null 2604 ├── distinct-on 2605 │ ├── columns: c0:3 2606 │ ├── grouping columns: c0:3 2607 │ └── project 2608 │ ├── columns: c0:3 2609 │ └── scan t0 2610 │ └── columns: c0:3 rowid:4!null 2611 └── filters 2612 └── c0:1 = c0:3 2613 2614 build 2615 SELECT * FROM v0 JOIN v0 AS v1 USING (c1) 2616 ---- 2617 inner-join (hash) 2618 ├── columns: c1:1!null c0:1!null c0:3!null 2619 ├── distinct-on 2620 │ ├── columns: c0:1 2621 │ ├── grouping columns: c0:1 2622 │ └── project 2623 │ ├── columns: c0:1 2624 │ └── scan t0 2625 │ └── columns: c0:1 rowid:2!null 2626 ├── distinct-on 2627 │ ├── columns: c0:3 2628 │ ├── grouping columns: c0:3 2629 │ └── project 2630 │ ├── columns: c0:3 2631 │ └── scan t0 2632 │ └── columns: c0:3 rowid:4!null 2633 └── filters 2634 └── c0:1 = c0:3