modules/calcite/src/test/sql/join/test_complex_join_expr.test

# name: test/sql/join/test_complex_join_expr.test
# description: Test joins with comparisons involving both sides of the join
# group: [join]

statement ok
PRAGMA enable_verification

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

statement ok
INSERT INTO test VALUES (4, 1), (2, 2)

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

statement ok
INSERT INTO test2 VALUES (1, 2), (3, 0)

query IIII
SELECT * FROM test JOIN test2 ON test.a+test2.c=test.b+test2.b
----
4	1	3	0

query IIII
SELECT * FROM test LEFT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY 1
----
2	2	NULL	NULL
4	1	3	0

query IIII
SELECT * FROM test RIGHT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY 1
----
NULL	NULL	1	2
4	1	3	0