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