github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/lookup_join (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, c)); 4 INSERT INTO abc VALUES (1, 1, 2), (2, 1, 1), (2, NULL, 2) 5 6 statement ok 7 CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (f, e)); 8 INSERT INTO def VALUES (1, 1, 2), (2, 1, 1), (NULL, 2, 1) 9 10 statement ok 11 CREATE TABLE gh (g INT, h INT, INDEX g_idx (g)); 12 INSERT INTO gh VALUES (NULL, 1) 13 14 # Set up the statistics as if the first table is much smaller than the second. 15 # This will make lookup join into the second table be the best plan. 16 statement ok 17 ALTER TABLE abc INJECT STATISTICS '[ 18 { 19 "columns": ["a"], 20 "created_at": "2018-01-01 1:00:00.00000+00:00", 21 "row_count": 100, 22 "distinct_count": 100 23 } 24 ]' 25 26 statement ok 27 ALTER TABLE def INJECT STATISTICS '[ 28 { 29 "columns": ["f"], 30 "created_at": "2018-01-01 1:00:00.00000+00:00", 31 "row_count": 10000, 32 "distinct_count": 10000 33 } 34 ]' 35 36 statement ok 37 ALTER TABLE gh INJECT STATISTICS '[ 38 { 39 "columns": ["g"], 40 "created_at": "2018-01-01 1:00:00.00000+00:00", 41 "row_count": 10000, 42 "distinct_count": 10000 43 } 44 ]' 45 46 query IIIIII rowsort 47 SELECT * FROM abc JOIN def ON f = b 48 ---- 49 1 1 2 2 1 1 50 2 1 1 2 1 1 51 1 1 2 NULL 2 1 52 2 1 1 NULL 2 1 53 54 query IIIIII rowsort 55 SELECT * FROM abc JOIN def ON f = b AND e = c 56 ---- 57 1 1 2 NULL 2 1 58 2 1 1 2 1 1 59 60 query IIIIII rowsort 61 SELECT * FROM abc JOIN def ON f = b WHERE a > 1 AND e > 1 62 ---- 63 2 1 1 NULL 2 1 64 65 query IIIIII rowsort 66 SELECT * FROM abc JOIN def ON f = b AND a > 1 AND e > 1 67 ---- 68 2 1 1 NULL 2 1 69 70 # Filter right side of a lookup join with a restriction on an indexed column. 71 query IIIIII rowsort 72 SELECT * FROM abc JOIN def ON f = a WHERE f > 1 73 ---- 74 2 1 1 1 1 2 75 2 NULL 2 1 1 2 76 77 # Test lookup join with restriction relating the left and right side. 78 query IIIIII rowsort 79 SELECT * FROM abc JOIN def ON f = b WHERE a >= e 80 ---- 81 1 1 2 2 1 1 82 2 1 1 2 1 1 83 2 1 1 NULL 2 1 84 85 # Test lookup join with restriction relating the left and right side. 86 query IIIIII rowsort 87 SELECT * FROM abc JOIN def ON f = b AND a >= e 88 ---- 89 1 1 2 2 1 1 90 2 1 1 2 1 1 91 2 1 1 NULL 2 1 92 93 # Test lookup join with selecting a subset of the columns. 94 query III rowsort 95 SELECT a, b, e FROM abc JOIN def ON f = b WHERE a >= e 96 ---- 97 1 1 1 98 2 1 1 99 2 1 2 100 101 # Test lookup join on NULL column. (https://github.com/cockroachdb/cockroach/issues/27032) 102 query I 103 SELECT h FROM abc JOIN gh ON b = g 104 ---- 105 106 statement ok 107 CREATE TABLE data (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b, c, d)) 108 109 # Generate all combinations of values 1 to 10. 110 statement ok 111 INSERT INTO data SELECT a, b, c, d FROM 112 generate_series(1, 10) AS a(a), 113 generate_series(1, 10) AS b(b), 114 generate_series(1, 10) AS c(c), 115 generate_series(1, 10) AS d(d) 116 117 statement ok 118 ALTER TABLE data INJECT STATISTICS '[ 119 { 120 "columns": ["a"], 121 "created_at": "2018-01-01 1:00:00.00000+00:00", 122 "row_count": 100000, 123 "distinct_count": 100000 124 } 125 ]' 126 127 # Ensure lookup join performs properly on input that has more than 100 rows. 128 query I 129 SELECT count(*) FROM (SELECT * FROM data WHERE c = 1) AS l NATURAL JOIN data AS r 130 ---- 131 1000 132 133 statement ok 134 CREATE TABLE foo (a int, b int); INSERT INTO foo VALUES (0, 1), (0, 2), (1, 1) 135 136 statement ok 137 CREATE TABLE bar (a int PRIMARY KEY, c int); INSERT INTO bar VALUES (0, 1), (1, 2), (2, 1) 138 139 query III rowsort 140 SELECT * FROM foo NATURAL JOIN bar 141 ---- 142 0 1 1 143 0 2 1 144 1 1 2 145 146 statement ok 147 CREATE TABLE books (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)); 148 INSERT INTO books VALUES 149 ('SICP', 1, 2), 150 ('Intro to Algo', 1, 1), 151 ('Intro to Algo', 2, 1), 152 ('Intro to Algo', 3, 2), 153 ('Art of Computer Programming', 1, 2), 154 ('Art of Computer Programming', 2, 2) 155 156 statement ok 157 CREATE TABLE books2 (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)); 158 INSERT INTO books2 VALUES 159 ('SICP', 1, 2), 160 ('Intro to Algo', 1, 1), 161 ('Intro to Algo', 2, 1), 162 ('Intro to Algo', 3, 2), 163 ('Art of Computer Programming', 1, 2), 164 ('Art of Computer Programming', 2, 2) 165 166 statement ok 167 ALTER TABLE books INJECT STATISTICS '[ 168 { 169 "columns": ["title"], 170 "created_at": "2018-01-01 1:00:00.00000+00:00", 171 "row_count": 100, 172 "distinct_count": 100 173 } 174 ]' 175 176 statement ok 177 ALTER TABLE books2 INJECT STATISTICS '[ 178 { 179 "columns": ["title"], 180 "created_at": "2018-01-01 1:00:00.00000+00:00", 181 "row_count": 10000, 182 "distinct_count": 1000 183 } 184 ]' 185 186 statement ok 187 CREATE TABLE authors (name STRING, book STRING); 188 INSERT INTO authors VALUES 189 ('Hal Abelson', 'SICP'), 190 ('Geral Jay Sussman', 'SICP'), 191 ('Thomas H Cormen', 'Intro to Algo'), 192 ('Charles E Leiserson', 'Intro to Algo'), 193 ('Ronald Rivest', 'Intro to Algo'), 194 ('Clifford Stein', 'Intro to Algo'), 195 ('Donald Knuth', 'Art of Computer Programming') 196 197 statement ok 198 ALTER TABLE authors INJECT STATISTICS '[ 199 { 200 "columns": ["name"], 201 "created_at": "2018-01-01 1:00:00.00000+00:00", 202 "row_count": 100, 203 "distinct_count": 100 204 } 205 ]' 206 207 # Filter on a column that is not returned or in the equality columns. 208 query T rowsort 209 SELECT DISTINCT b1.title FROM books as b1 JOIN books2 as b2 ON b1.title = b2.title WHERE b1.shelf <> b2.shelf 210 ---- 211 Intro to Algo 212 213 query T rowsort 214 SELECT DISTINCT authors.name FROM books AS b1, books2 as b2, authors WHERE b1.title = b2.title AND authors.book = b1.title AND b1.shelf <> b2.shelf 215 ---- 216 Thomas H Cormen 217 Charles E Leiserson 218 Ronald Rivest 219 Clifford Stein 220 221 # Ensure lookup join preserves ordering from the left side. 222 query T 223 SELECT a.name FROM authors AS a JOIN books2 AS b2 ON a.book = b2.title ORDER BY a.name 224 ---- 225 Charles E Leiserson 226 Charles E Leiserson 227 Charles E Leiserson 228 Clifford Stein 229 Clifford Stein 230 Clifford Stein 231 Donald Knuth 232 Donald Knuth 233 Geral Jay Sussman 234 Hal Abelson 235 Ronald Rivest 236 Ronald Rivest 237 Ronald Rivest 238 Thomas H Cormen 239 Thomas H Cormen 240 Thomas H Cormen 241 242 #################################### 243 # LOOKUP JOIN ON SECONDARY INDEX # 244 #################################### 245 246 statement ok 247 CREATE TABLE small (a INT PRIMARY KEY, b INT, c INT, d INT) 248 249 statement ok 250 CREATE TABLE large (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX bc (b) STORING (c)) 251 252 # Generate 10 rows for both tables. 253 statement ok 254 INSERT INTO small SELECT x, 2*x, 3*x, 4*x FROM 255 generate_series(1, 10) AS a(x) 256 257 statement ok 258 INSERT INTO large SELECT x, 2*x, 3*x, 4*x FROM 259 generate_series(1, 10) AS a(x) 260 261 statement ok 262 ALTER TABLE small INJECT STATISTICS '[ 263 { 264 "columns": ["a"], 265 "created_at": "2018-01-01 1:00:00.00000+00:00", 266 "row_count": 100, 267 "distinct_count": 100 268 } 269 ]' 270 271 statement ok 272 ALTER TABLE large INJECT STATISTICS '[ 273 { 274 "columns": ["a"], 275 "created_at": "2018-01-01 1:00:00.00000+00:00", 276 "row_count": 10000, 277 "distinct_count": 10000 278 } 279 ]' 280 281 # Lookup join on covering secondary index 282 query II rowsort 283 SELECT small.a, large.c FROM small JOIN large ON small.a = large.b 284 ---- 285 2 3 286 4 6 287 6 9 288 8 12 289 10 15 290 291 # Lookup join on non-covering secondary index 292 query II rowsort 293 SELECT small.a, large.d FROM small JOIN large ON small.a = large.b 294 ---- 295 2 4 296 4 8 297 6 12 298 8 16 299 10 20 300 301 ############################ 302 # LEFT OUTER LOOKUP JOIN # 303 ############################ 304 305 # Left join against primary index 306 query II rowsort 307 SELECT small.b, large.a FROM small LEFT JOIN large ON small.b = large.a 308 ---- 309 2 2 310 4 4 311 6 6 312 8 8 313 10 10 314 12 NULL 315 14 NULL 316 16 NULL 317 18 NULL 318 20 NULL 319 320 # Left join should preserve input order. 321 query II 322 SELECT t1.a, t2.b FROM small t1 LEFT JOIN large t2 ON t1.a = t2.a AND t2.b % 6 = 0 ORDER BY t1.a 323 ---- 324 1 NULL 325 2 NULL 326 3 6 327 4 NULL 328 5 NULL 329 6 12 330 7 NULL 331 8 NULL 332 9 18 333 10 NULL 334 335 # Left join against covering secondary index 336 query II rowsort 337 SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b 338 ---- 339 3 NULL 340 6 9 341 9 NULL 342 12 18 343 15 NULL 344 18 27 345 21 NULL 346 24 NULL 347 27 NULL 348 30 NULL 349 350 # Left join against non-covering secondary index 351 query II rowsort 352 SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b 353 ---- 354 3 NULL 355 6 12 356 9 NULL 357 12 24 358 15 NULL 359 18 36 360 21 NULL 361 24 NULL 362 27 NULL 363 30 NULL 364 365 # Left join with ON filter on covering index 366 query II rowsort 367 SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b AND large.c < 20 368 ---- 369 3 NULL 370 6 9 371 9 NULL 372 12 18 373 15 NULL 374 18 NULL 375 21 NULL 376 24 NULL 377 27 NULL 378 30 NULL 379 380 ## Left join with ON filter on non-covering index 381 query II rowsort 382 SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b AND large.d < 30 383 ---- 384 3 NULL 385 6 12 386 9 NULL 387 12 24 388 15 NULL 389 18 NULL 390 21 NULL 391 24 NULL 392 27 NULL 393 30 NULL 394 395 # Lookup joins against interleaved tables. Regression test for #28981. 396 # This is now tested more thoroughly by joinreader_test.go. 397 398 statement ok 399 CREATE TABLE parent (a INT, b INT, PRIMARY KEY(a, b)) 400 401 statement ok 402 CREATE TABLE child (a INT, b INT, c INT, PRIMARY KEY(a, b, c)) INTERLEAVE IN PARENT parent(a, b) 403 404 statement ok 405 CREATE TABLE source (a INT) 406 407 statement ok 408 ALTER TABLE source INJECT STATISTICS '[ 409 { 410 "columns": ["a"], 411 "created_at": "2018-01-01 1:00:00.00000+00:00", 412 "row_count": 1, 413 "distinct_count": 1 414 } 415 ]' 416 417 statement ok 418 ALTER TABLE child INJECT STATISTICS '[ 419 { 420 "columns": ["a", "b", "c"], 421 "created_at": "2018-01-01 1:00:00.00000+00:00", 422 "row_count": 10, 423 "distinct_count": 10 424 } 425 ]' 426 427 statement ok 428 INSERT INTO child VALUES(1, 2, 3) 429 430 statement ok 431 INSERT INTO source VALUES(1) 432 433 query IIII 434 SELECT * FROM source JOIN child ON source.a = child.a 435 ---- 436 1 1 2 3 437 438 ########################################################### 439 # LOOKUP JOINS ON IMPLICIT INDEX KEY COLUMNS # 440 # https://github.com/cockroachdb/cockroach/issues/31777 # 441 ########################################################### 442 statement ok 443 CREATE TABLE t (a INT, b INT, c INT, d INT, e INT) 444 445 statement ok 446 CREATE TABLE u (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY (a DESC, b, c)) 447 448 statement ok 449 INSERT INTO t VALUES 450 (1, 2, 3, 4, 5) 451 452 statement ok 453 INSERT INTO u VALUES 454 (1, 2, 3, 4, 5), 455 (2, 3, 4, 5, 6), 456 (3, 4, 5, 6, 7) 457 458 # Test index with all primary key columns implicit. 459 statement ok 460 CREATE INDEX idx ON u (d) 461 462 query I 463 SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 464 ---- 465 1 466 467 # Test unique version of same index. (Lookup join should not use column a.) 468 statement ok 469 DROP INDEX u@idx 470 471 statement ok 472 CREATE UNIQUE INDEX idx ON u (d) 473 474 query I 475 SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 476 ---- 477 1 478 479 # Test index with first primary key column explicit and the rest implicit. 480 statement ok 481 DROP INDEX u@idx CASCADE 482 483 statement ok 484 CREATE INDEX idx ON u (d, a) 485 486 query I 487 SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5 488 ---- 489 1 490 491 # Test index with middle primary key column explicit and the rest implicit. 492 statement ok 493 DROP INDEX u@idx 494 495 statement ok 496 CREATE INDEX idx ON u (d, b) 497 498 query I 499 SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.b = u.b WHERE t.e = 5 500 ---- 501 1 502 503 # Test index with last primary key column explicit and the rest implicit. 504 statement ok 505 DROP INDEX u@idx 506 507 statement ok 508 CREATE INDEX idx ON u (d, c) 509 510 query I 511 SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a AND t.d = u.d WHERE t.e = 5 512 ---- 513 1 514 515 query IIIIII colnames,partialsort(4) 516 SELECT * FROM def JOIN abc ON a=f ORDER BY a 517 ---- 518 d e f a b c 519 2 1 1 1 1 2 520 NULL 2 1 1 1 2 521 1 1 2 2 NULL 2 522 1 1 2 2 1 1 523 524 # Test lookup semi and anti join. 525 query III rowsort 526 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f) 527 ---- 528 1 1 2 529 2 1 1 530 2 NULL 2 531 532 query III rowsort 533 SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f) 534 ---- 535 536 query III rowsort 537 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND c=e) 538 ---- 539 1 1 2 540 2 1 1 541 542 query III rowsort 543 SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND c=e) 544 ---- 545 2 NULL 2 546 547 query III rowsort 548 SELECT * from abc WHERE EXISTS (SELECT * FROM def WHERE a=f AND d+b>1) 549 ---- 550 1 1 2 551 2 1 1 552 553 query III rowsort 554 SELECT * from abc WHERE NOT EXISTS (SELECT * FROM def WHERE a=f AND d+b>1) 555 ---- 556 2 NULL 2 557 558 query II 559 SELECT a,b from small WHERE EXISTS (SELECT a FROM data WHERE small.a=data.a) ORDER BY a 560 ---- 561 1 2 562 2 4 563 3 6 564 4 8 565 5 10 566 6 12 567 7 14 568 8 16 569 9 18 570 10 20 571 572 query II 573 SELECT a,b from small WHERE a+b<20 AND EXISTS (SELECT a FROM data WHERE small.a=data.a AND small.b+data.c>15) ORDER BY a 574 ---- 575 3 6 576 4 8 577 5 10 578 6 12 579 580 # The following tests check that if the joiners can separate a row request 581 # into separate families that it does, and generates spans for each family 582 # instead of reading the entire row when it doesn't need to. 583 584 statement ok 585 CREATE TABLE family_split_1 (x INT, PRIMARY KEY (x)) 586 587 statement ok 588 INSERT INTO family_split_1 VALUES (1) 589 590 statement ok 591 CREATE TABLE family_split_2 (x INT, y INT, z INT, PRIMARY KEY (x), FAMILY f1 (x), FAMILY f2 (y), FAMILY f3 (z)) 592 593 statement ok 594 INSERT INTO family_split_2 VALUES (1, 2, 3) 595 596 statement ok 597 SET tracing = on; SELECT family_split_2.x, family_split_2.z FROM family_split_1 INNER LOOKUP JOIN family_split_2 ON family_split_1.x = family_split_2.x; SET tracing = off 598 599 query T 600 SELECT message FROM [SHOW TRACE FOR SESSION] WHERE message LIKE 'Scan /Table/70/1/1/{0-1}, /Table/70/1/1/2/{1-2}' 601 ---- 602 Scan /Table/70/1/1/{0-1}, /Table/70/1/1/2/{1-2} 603 604 statement ok 605 CREATE TABLE family_index_join (x INT PRIMARY KEY, y INT, z INT, w INT, INDEX (y), FAMILY f1 (x), FAMILY f2 (y), FAMILY f3 (z), FAMILY f4(w)) 606 607 statement ok 608 INSERT INTO family_index_join VALUES (1, 2, 3, 4) 609 610 statement ok 611 SET tracing = on 612 613 query II 614 SELECT y,w FROM family_index_join@family_index_join_y_idx WHERE y = 2 615 ---- 616 2 4 617 618 statement ok 619 SET tracing = off 620 621 query T 622 SELECT message FROM [SHOW TRACE FOR SESSION] WHERE message LIKE 'Scan /Table/71/1/%' 623 ---- 624 Scan /Table/71/1/1/{0-1/2}, /Table/71/1/1/3/{1-2} 625 626 # Test generating tighter spans on interleaved tables. 627 statement ok 628 CREATE TABLE family_interleave_1 (x INT, y INT, z INT, PRIMARY KEY (x), FAMILY f1 (x), FAMILY f2 (y), FAMILY f3 (z)) 629 630 statement ok 631 CREATE TABLE family_interleave_2 (x INT, y INT, PRIMARY KEY (x, y)) INTERLEAVE IN PARENT family_interleave_1 (x) 632 633 statement ok 634 INSERT INTO family_interleave_1 VALUES (1, 2, 3) 635 636 statement ok 637 INSERT INTO family_interleave_2 VALUES (1, 3) 638 639 statement ok 640 SET TRACING = on 641 642 query II 643 SELECT family_interleave_1.x, family_interleave_1.z FROM family_interleave_2 INNER LOOKUP JOIN family_interleave_1 ON family_interleave_1.x = family_interleave_2.x 644 ---- 645 1 3 646 647 query T 648 SELECT message FROM [SHOW TRACE FOR SESSION] WHERE message LIKE 'Scan /Table/72/1/1/{0-1}, /Table/72/1/1/2/{1-2}' 649 ---- 650 Scan /Table/72/1/1/{0-1}, /Table/72/1/1/2/{1-2}