modules/sql-engine/src/integrationTest/sql/join/natural/natural_join.test

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