# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the logic test suite in CockroachDB. The # original file was retrieved on June 10, 2019 from: # # https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/lookup_join # # The original source code is subject to the terms of the Apache # 2.0 license, a copy of which can be found in the LICENSE file at the # root of this repository. mode cockroach simple conn=mz_system,user=mz_system ALTER SYSTEM SET unsafe_enable_table_keys = true ---- COMPLETE 0 statement ok CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY (a, c)) statement ok INSERT INTO abc VALUES (1, 1, 2), (2, 1, 1), (2, NULL, 2) statement ok CREATE TABLE def (d INT, e INT, f INT, PRIMARY KEY (f, e)) statement ok INSERT INTO def VALUES (1, 1, 2), (2, 1, 1), (NULL, 2, 1) statement ok CREATE TABLE gh (g INT, h INT, INDEX g_idx (g)) statement ok INSERT INTO gh VALUES (NULL, 1) # # Set up the statistics as if the first table is much smaller than the second. # # This will make lookup join into the second table be the best plan. # # TODO(radu): we have to use very small row counts because of the poor row # # count estimation for joins (left-rows * right-rows / 10). # statement ok # ALTER TABLE abc INJECT STATISTICS '[ # { # "columns": ["a"], # "created_at": "2018-01-01 1:00:00.00000+00:00", # "row_count": 100, # "distinct_count": 100 # } # ]' # statement ok # ALTER TABLE def INJECT STATISTICS '[ # { # "columns": ["f"], # "created_at": "2018-01-01 1:00:00.00000+00:00", # "row_count": 10000, # "distinct_count": 10000 # } # ]' # statement ok # ALTER TABLE gh INJECT STATISTICS '[ # { # "columns": ["g"], # "created_at": "2018-01-01 1:00:00.00000+00:00", # "row_count": 10000, # "distinct_count": 10000 # } # ]' query IIIIII rowsort SELECT * FROM abc JOIN def ON f = b ---- 1 1 2 2 1 1 2 1 1 2 1 1 1 1 2 NULL 2 1 2 1 1 NULL 2 1 query IIIIII rowsort SELECT * FROM abc JOIN def ON f = b WHERE a > 1 AND e > 1 ---- 2 1 1 NULL 2 1 query IIIIII rowsort SELECT * FROM abc JOIN def ON f = b AND a > 1 AND e > 1 ---- 2 1 1 NULL 2 1 # Filter right side of a lookup join with a restriction on an indexed column. query IIIIII rowsort SELECT * FROM abc JOIN def ON f = a WHERE f > 1 ---- 2 1 1 1 1 2 2 NULL 2 1 1 2 # Test lookup join with restriction relating the left and right side. query IIIIII rowsort SELECT * FROM abc JOIN def ON f = b WHERE a >= e ---- 1 1 2 2 1 1 2 1 1 2 1 1 2 1 1 NULL 2 1 # Test lookup join with restriction relating the left and right side. query IIIIII rowsort SELECT * FROM abc JOIN def ON f = b AND a >= e ---- 1 1 2 2 1 1 2 1 1 2 1 1 2 1 1 NULL 2 1 # Test lookup join with selecting a subset of the columns. query III rowsort SELECT a, b, e FROM abc JOIN def ON f = b WHERE a >= e ---- 1 1 1 2 1 1 2 1 2 # Test lookup join on NULL column. (https://github.com/cockroachdb/cockroach/issues/27032) query I SELECT h FROM abc JOIN gh ON b = g ---- statement ok CREATE TABLE data (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b, c, d)) # Generate all combinations of values 1 to 10. statement ok INSERT INTO data SELECT a, b, c, d FROM generate_series(1, 10) AS a(a), generate_series(1, 10) AS b(b), generate_series(1, 10) AS c(c), generate_series(1, 10) AS d(d) # Ensure lookup join performs properly on input that has more than 100 rows. query I SELECT count(*) FROM (SELECT * FROM data WHERE c = 1) AS l NATURAL JOIN data AS r ---- 1000 statement ok CREATE TABLE foo (a int, b int) statement ok INSERT INTO foo VALUES (0, 1), (0, 2), (1, 1) statement ok CREATE TABLE bar (a int PRIMARY KEY, c int) statement ok INSERT INTO bar VALUES (0, 1), (1, 2), (2, 1) query III rowsort SELECT * FROM foo NATURAL JOIN bar ---- 0 1 1 0 2 1 1 1 2 statement ok CREATE TABLE books (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)) statement ok INSERT INTO books VALUES ('SICP', 1, 2), ('Intro to Algo', 1, 1), ('Intro to Algo', 2, 1), ('Intro to Algo', 3, 2), ('Art of Computer Programming', 1, 2), ('Art of Computer Programming', 2, 2) statement ok CREATE TABLE books2 (title STRING, edition INT, shelf INT, PRIMARY KEY (title, edition)) statement ok INSERT INTO books2 VALUES ('SICP', 1, 2), ('Intro to Algo', 1, 1), ('Intro to Algo', 2, 1), ('Intro to Algo', 3, 2), ('Art of Computer Programming', 1, 2), ('Art of Computer Programming', 2, 2) statement ok ALTER TABLE books INJECT STATISTICS '[ { "columns": ["title"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 100, "distinct_count": 100 } ]' statement ok ALTER TABLE books2 INJECT STATISTICS '[ { "columns": ["title"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 10000, "distinct_count": 1000 } ]' statement ok CREATE TABLE authors (name STRING, book STRING) statement ok INSERT INTO authors VALUES ('Hal Abelson', 'SICP'), ('Geral Jay Sussman', 'SICP'), ('Thomas H Cormen', 'Intro to Algo'), ('Charles E Leiserson', 'Intro to Algo'), ('Ronald Rivest', 'Intro to Algo'), ('Clifford Stein', 'Intro to Algo'), ('Donald Knuth', 'Art of Computer Programming') statement ok ALTER TABLE authors INJECT STATISTICS '[ { "columns": ["name"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 100, "distinct_count": 100 } ]' # Filter on a column that is not returned or in the equality columns. query T rowsort SELECT DISTINCT b1.title FROM books as b1 JOIN books2 as b2 ON b1.title = b2.title WHERE b1.shelf <> b2.shelf ---- Intro to Algo query T rowsort 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 ---- Thomas H Cormen Charles E Leiserson Ronald Rivest Clifford Stein # Ensure lookup join preserves ordering from the left side. query T SELECT a.name FROM authors AS a JOIN books2 AS b2 ON a.book = b2.title ORDER BY a.name ---- Charles E Leiserson Charles E Leiserson Charles E Leiserson Clifford Stein Clifford Stein Clifford Stein Donald Knuth Donald Knuth Geral Jay Sussman Hal Abelson Ronald Rivest Ronald Rivest Ronald Rivest Thomas H Cormen Thomas H Cormen Thomas H Cormen #################################### # LOOKUP JOIN ON SECONDARY INDEX # #################################### statement ok CREATE TABLE small (a INT PRIMARY KEY, b INT, c INT, d INT) statement ok CREATE TABLE large (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX bc (b) STORING (c)) # Generate 10 rows for both tables. statement ok INSERT INTO small SELECT x, 2*x, 3*x, 4*x FROM generate_series(1, 10) AS a(x) statement ok INSERT INTO large SELECT x, 2*x, 3*x, 4*x FROM generate_series(1, 10) AS a(x) statement ok ALTER TABLE small INJECT STATISTICS '[ { "columns": ["a"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 100, "distinct_count": 100 } ]' statement ok ALTER TABLE large INJECT STATISTICS '[ { "columns": ["a"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 10000, "distinct_count": 10000 } ]' # Lookup join on covering secondary index query II rowsort SELECT small.a, large.c FROM small JOIN large ON small.a = large.b ---- 2 3 4 6 6 9 8 12 10 15 # Lookup join on non-covering secondary index query II rowsort SELECT small.a, large.d FROM small JOIN large ON small.a = large.b ---- 2 4 4 8 6 12 8 16 10 20 ############################ # LEFT OUTER LOOKUP JOIN # ############################ # Left join against primary index query II rowsort SELECT small.b, large.a FROM small LEFT JOIN large ON small.b = large.a ---- 2 2 4 4 6 6 8 8 10 10 12 NULL 14 NULL 16 NULL 18 NULL 20 NULL # Left join should preserve input order. query II 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 ---- 1 NULL 2 NULL 3 6 4 NULL 5 NULL 6 12 7 NULL 8 NULL 9 18 10 NULL # Left join against covering secondary index query II rowsort SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b ---- 3 NULL 6 9 9 NULL 12 18 15 NULL 18 27 21 NULL 24 NULL 27 NULL 30 NULL # Left join against non-covering secondary index query II rowsort SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b ---- 3 NULL 6 12 9 NULL 12 24 15 NULL 18 36 21 NULL 24 NULL 27 NULL 30 NULL # Left join with ON filter on covering index query II rowsort SELECT small.c, large.c FROM small LEFT JOIN large ON small.c = large.b AND large.c < 20 ---- 3 NULL 6 9 9 NULL 12 18 15 NULL 18 NULL 21 NULL 24 NULL 27 NULL 30 NULL ## Left join with ON filter on non-covering index query II rowsort SELECT small.c, large.d FROM small LEFT JOIN large ON small.c = large.b AND large.d < 30 ---- 3 NULL 6 12 9 NULL 12 24 15 NULL 18 NULL 21 NULL 24 NULL 27 NULL 30 NULL # Lookup joins against interleaved tables. Regression test for materialize#28981. # This is now tested more thoroughly by joinreader_test.go. statement ok CREATE TABLE parent (a INT, b INT, PRIMARY KEY(a, b)) statement ok CREATE TABLE child (a INT, b INT, c INT, PRIMARY KEY(a, b, c)) INTERLEAVE IN PARENT parent(a, b) statement ok CREATE TABLE source (a INT) statement ok ALTER TABLE source INJECT STATISTICS '[ { "columns": ["a"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 1, "distinct_count": 1 } ]' statement ok ALTER TABLE child INJECT STATISTICS '[ { "columns": ["a", "b", "c"], "created_at": "2018-01-01 1:00:00.00000+00:00", "row_count": 10, "distinct_count": 10 } ]' statement ok INSERT INTO child VALUES(1, 2, 3) statement ok INSERT INTO source VALUES(1) query IIII SELECT * FROM source JOIN child ON source.a = child.a ---- 1 1 2 3 ########################################################### # LOOKUP JOINS ON IMPLICIT INDEX KEY COLUMNS # # https://github.com/cockroachdb/cockroach/issues/31777 # ########################################################### statement ok CREATE TABLE t (a INT, b INT, c INT, d INT, e INT) statement ok CREATE TABLE u (a INT, b INT, c INT, d INT, e INT, PRIMARY KEY (a DESC, b, c)) statement ok INSERT INTO t VALUES (1, 2, 3, 4, 5) statement ok INSERT INTO u VALUES (1, 2, 3, 4, 5), (2, 3, 4, 5, 6), (3, 4, 5, 6, 7) # Test index with all primary key columns implicit. statement ok CREATE INDEX idx ON u (d) query I SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 ---- 1 # Test unique version of same index. (Lookup join should not use column a.) statement ok DROP INDEX u@idx statement ok CREATE UNIQUE INDEX idx ON u (d) query I SELECT u.a FROM t JOIN u ON t.d = u.d AND t.a = u.a WHERE t.e = 5 ---- 1 # Test index with first primary key column explicit and the rest implicit. statement ok DROP INDEX u@idx CASCADE statement ok CREATE INDEX idx ON u (d, a) query I 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 ---- 1 # Test index with middle primary key column explicit and the rest implicit. statement ok DROP INDEX u@idx statement ok CREATE INDEX idx ON u (d, b) query I 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 ---- 1 # Test index with last primary key column explicit and the rest implicit. statement ok DROP INDEX u@idx statement ok CREATE INDEX idx ON u (d, c) query I 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 ---- 1 query IIIIII colnames,partialsort(4) SELECT * FROM def JOIN abc ON a=f ORDER BY a ---- d e f a b c 2 1 1 1 1 2 NULL 2 1 1 1 2 1 1 2 2 NULL 2 1 1 2 2 1 1