blob: a88207bd22936ce082409f26fc42eb0fa6932c2e [file] [log] [blame]
# name: test/sql/join/natural/natural_join.test
# description: Test natural joins
# group: [natural]
statement ok
PRAGMA enable_verification
# create tables
statement ok
CREATE TABLE t1 (a INTEGER, b INTEGER)
statement ok
INSERT INTO t1 VALUES (1, 2)
# https://issues.apache.org/jira/browse/IGNITE-22158
# Let's amend both type for t2 table to BIGINT after resolve the ticket.
statement ok
CREATE TABLE t2 (a INTEGER, c INTEGER)
statement ok
INSERT INTO t2 VALUES (1, 3), (2, 4)
# NATURAL join with one column
query III
SELECT * FROM t1 NATURAL JOIN t2
----
1 2 3
query III
SELECT t1.a, t1.b, t2.c FROM t1 NATURAL JOIN t2
----
1 2 3
query III
SELECT t1.a, t1.b, t2.c FROM t1 NATURAL JOIN t2 ORDER BY t2.a
----
1 2 3
# natural join with multiple matching columns
statement ok
CREATE TABLE t3 (a INTEGER, b INTEGER, c INTEGER)
statement ok
INSERT INTO t3 VALUES (1, 2, 3)
query III
SELECT * FROM t1 NATURAL JOIN t3
----
1 2 3
# common columns moved to the first place, column order: a, c, b
query III
SELECT * FROM t3 NATURAL JOIN t2
----
1 3 2
# when there no matching columns are present natural join behaves like a cross join
skipif ignite3
# https://issues.apache.org/jira/browse/IGNITE-18668
query I
select * from (values (1)) tbl(a) natural join (values (1), (2)) tbl2(b) order by 1, 2
----
1 1
1 2
# natural join with subqueries
query I
select * from (select 42) tbl(a) natural join (select 42) tbl2(a)
----
42
# uncorrelated scalar subquery
query I
select (select * from (select 42) tbl(a) natural join (select 42) tbl2(a))
----
42
# error: duplicate table alias on both sides
statement error
select (select * from (select 42) tbl(a) natural join (select 42) tbl(a))