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

# name: test/sql/join/inner/test_using_join.test
# description: Test USING joins
# group: [inner]

statement ok
PRAGMA enable_verification

# create tables
statement ok
CREATE TABLE t1 (a INTEGER, b INTEGER, c INTEGER)

statement ok
INSERT INTO t1 VALUES (1,2,3)

statement ok
CREATE TABLE t2 (a INTEGER, b INTEGER, c INTEGER)

statement ok
INSERT INTO t2 VALUES (1,2,3), (2,2,4), (1,3,4)

# USING join
query III
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a) ORDER BY t2.b
----
1	2	3
1	3	4

query III
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(b) ORDER BY t2.c
----
1	2	3
2	2	4

query III
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a,b)
----
1	2	3

query III
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a,b,c)
----
1	2	3

skipif ignite3
# IGNITE-15570 JOIN with USING with common column names failed
query I
SELECT a+1 FROM t1 JOIN t2 USING(a) ORDER BY a
----
2
2

statement error
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a+b)

statement error
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING("")

statement error
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(d)

statement error
SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(t1.a)

query IIII
SELECT * FROM t1 JOIN t2 USING(a,b)
----
1	2	3	3

# CONTROVERSIAL:
# we do not allow this because it is ambiguous: "b" can be bind to both "t1.b" or "t2.b" and this would give
# different results SQLite allows this, PostgreSQL does not
skipif ignite3
# IGNITE-19125 Expected errors on joins are not rised after bump calcite version to 1.34
statement error
SELECT * FROM t1 JOIN t2 USING(a) JOIN t2 t2b USING (b);

skipif ignite3
# IGNITE-19125 Expected errors on joins are not rised after bump calcite version to 1.34
# this is the same, but now with a duplicate potential binding on the RHS
statement error
select * from (values (1)) tbl(i) join ((values (1)) tbl2(i) join  (values (1)) tbl3(i) on tbl2.i=tbl3.i) using (i)

# a chain with the same column name is allowed though!
query IIIIIII
SELECT * FROM t1 JOIN t2 USING(a) JOIN t2 t2b USING (a) ORDER BY 1, 2, 3, 4, 5, 6, 7
----
1	2	3	2	3	2	3
1	2	3	2	3	3	4
1	2	3	3	4	2	3
1	2	3	3	4	3	4