modules/calcite/src/test/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

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
statement error
SELECT * FROM t1 JOIN t2 USING(a) JOIN t2 t2b USING (b);

# 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)