modules/sql-engine/src/integrationTest/sql/join/inner/test_join.test

# name: test/sql/join/inner/test_join.test
# description: Test basic joins of tables
# group: [inner]

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE test (a INTEGER, b INTEGER);

statement ok
INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)

statement ok
CREATE TABLE test2 (b INTEGER, c INTEGER);

statement ok
INSERT INTO test2 VALUES (1, 10), (1, 20), (2, 30)

# simple cross product + join condition
query III
SELECT a, test.b, c FROM test, test2 WHERE test.b = test2.b ORDER BY c;
----
11	1	10
11	1	20
12	2	30

# ambiguous reference to column
statement error
SELECT b FROM test, test2 WHERE test.b > test2.b;

# simple cross product + multiple join conditions
query III
SELECT a, test.b, c FROM test, test2 WHERE test.b=test2.b AND test.a-1=test2.c
----
11	1	10

# use join columns in subquery
query III
SELECT a, (SELECT test.a), c FROM test, test2 WHERE test.b = test2.b ORDER BY c;
----
11	11	10
11	11	20
12	12	30

# explicit join
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON test.b = test2.b ORDER BY c;
----
11	1	10
11	1	20
12	2	30

# explicit join with condition the wrong way around
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON test2.b = test.b ORDER BY c;
----
11	1	10
11	1	20
12	2	30

# explicit join with additional condition that is no left-right comparision
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON test2.b = test.b and test.b = 2;
----
12	2	30

# explicit join with additional condition that is constant
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON test2.b = test.b and 2 = 2 ORDER BY c;
----
11	1	10
11	1	20
12	2	30

# explicit join with only condition that is no left-right comparision
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON test.b = 2 ORDER BY c;
----
12	2	10
12	2	20
12	2	30

# explicit join with only condition that is constant
query III
SELECT a, test.b, c FROM test INNER JOIN test2 ON NULL = 2;
----

# equality join where both lhs and rhs keys are projected
query II
SELECT * FROM (VALUES (1)) tbl(i) JOIN (VALUES (1)) tbl2(j) ON (i=j);
----
1	1

# equality join where both lhs and rhs keys are projected with filter
query II
SELECT * FROM (VALUES (1), (2)) tbl(i) JOIN (VALUES (1), (2)) tbl2(j) ON (i=j) WHERE i+j=2;
----
1	1