modules/sql-engine/src/integrationTest/sql/join/test_nested_outer_join.test

# name: sql/join/test_nested_outer_join.test
# description: SQL feature F041-05 (Outer joins can be nested)
# group: [Basic joined table]

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE t1(c11 INTEGER, c12 INTEGER, c13 CHAR, cc INTEGER);

statement ok
INSERT INTO t1 VALUES (1, 2, 'a', 1), (2, 3, 'b', 2), (3, 4, 'c', 3)

statement ok
CREATE TABLE t2 (c21 INTEGER, c22 INTEGER, c23 CHAR, cc INTEGER);

statement ok
INSERT INTO t2 VALUES (2, 3, 'a', 2), (3, 4, 'b', 3), (4, 3, 'c', 4)

statement ok
CREATE TABLE t3 (c31 INTEGER, c32 INTEGER, c33 CHAR, cc INTEGER);

statement ok
INSERT INTO t3 VALUES (3, 3, 'a', 3), (4, 4, 'b', 4), (5, 3, 'c', 5)

query III rowsort
select t1.c11, t1.c12, t2.c22 from t1 left outer join t2 on t1.c11 = t2.c21 left outer join t1 t11 on t2.c22 = t11.c12
----
1	2	null
2	3	3
3	4	4

query III rowsort
select t1.c11, t1.c12, t2.c22 from t1 left outer join t2 on t1.c11 = t2.c21 left outer join t1 t11 on t2.c22 = t11.c12
----
2	3	3
3	4	4
1	2	null

query III rowsort
select t1.c11, t2.c22, t3.c33 from t1 left outer join t2 on t1.c11 = t2.c21 left outer join t3 on t2.c22 = t3.c32;
----
1	null	null
2	3	a
2	3	c
3	4	b

query III rowsort
select t1.c11, t2.c22, t3.c33 from t1 right outer join t2 on t1.c11 = t2.c21 left outer join t3 on t2.c22 = t3.c32
----
2	3	a
2	3	c
3	4	b
null	3	a
null	3	c

query III rowsort
select t1.c11, t1.c12, t2.c22 from t1 left outer join t2 on t1.c13 = t2.c23 left outer join t1 t11 on t2.c23 = t11.c13
----
1	2	3
2	3	4
3	4	3

query III rowsort
select t1.c11, t2.c22, t3.c33 from t1 left outer join t2 using (cc) left outer join t3 using (cc);
----
1	null	null
2	3	null
3	4	a