modules/calcite/src/test/sql/sqlite/join/join1.test

statement ok
CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);

statement ok
CREATE TABLE t2(b INTEGER, c INTEGER, d INTEGER);

statement ok
INSERT INTO t1 VALUES(1,2,3);

statement ok
INSERT INTO t1 VALUES(2,3,4);

statement ok
INSERT INTO t1 VALUES(3,4,5);

statement ok
INSERT INTO t2 VALUES(1,2,3);

statement ok
INSERT INTO t2 VALUES(2,3,4);

statement ok
INSERT INTO t2 VALUES(3,4,5);

query III rowsort
SELECT /*+ CNL_JOIN */ t2.* FROM t2 NATURAL JOIN t1;
----
2	3	4
3	4	5

query III rowsort
SELECT /*+ CNL_JOIN */ t2.* FROM t2 NATURAL JOIN t1;
----
2	3	4
3	4	5

query III rowsort
SELECT /*+ MERGE_JOIN */ t2.* FROM t2 NATURAL JOIN t1;
----
2	3	4
3	4	5

query III rowsort
SELECT t1.* FROM t2 NATURAL JOIN t1;
----
1	2	3
2	3	4

query IIIIII nosort
SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1 ORDER BY t1.a
----
1	2	3	2	3	4
2	3	4	3	4	5

query I nosort
SELECT b FROM t1 JOIN t2 USING(b) ORDER BY b;
----
2
3

query III rowsort
SELECT t1.* FROM t1 JOIN t2 USING(b) ORDER BY t1.a;
----
1	2	3
2	3	4

query III rowsort
SELECT t2.* FROM t1 JOIN t2 USING(b) ORDER BY t1.a;
----
2	3	4
3	4	5

query IIIII nosort
SELECT * FROM t1 INNER JOIN t2 USING(b) ORDER BY t1.a;
----
2	1	3	3	4
3	2	4	4	5

query IIIII nosort
SELECT t1.a, t1.b, t1.c, t2.b, t2.d FROM t1 INNER JOIN t2 USING(c) ORDER BY t1.a;
----
1	2	3	2	4
2	3	4	3	5

query IIII nosort
SELECT * FROM t1 INNER JOIN t2 USING(c,b) ORDER BY t1.c;
----
3	2	1	4
4	3	2	5

query IIII nosort
SELECT t1.b, t1.c, t1.a, t3.d FROM t1 NATURAL JOIN (SELECT b, c, d FROM t2) as t3 ORDER BY t1.a;
----
2	3	1	4
3	4	2	5

statement ok
CREATE TABLE t3(c INTEGER, d INTEGER, e INTEGER);

statement ok
INSERT INTO t3 VALUES(2,3,4);

statement ok
INSERT INTO t3 VALUES(3,4,5);

statement ok
INSERT INTO t3 VALUES(4,5,6);

query IIII nosort
SELECT * FROM t1 NATURAL LEFT JOIN t2 ORDER BY t1.a;
----
2	3	1	4
3	4	2	5
4	5	3	NULL

query III rowsort
SELECT t2.* FROM t1 NATURAL LEFT JOIN t2 ORDER BY t2.b;
----
2	3	4
3	4	5
NULL	NULL	NULL

query IIIIII nosort
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 ORDER BY t1.a;
----
2	3	4	NULL	NULL	NULL
3	4	5	1	2	3

query IIIIII rowsort
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 ORDER BY t1.a;
----
1	2	3	NULL	NULL	NULL
2	3	4	NULL	NULL	NULL

query IIII nosort
SELECT * FROM t1 INNER JOIN t2 USING(b,c) ORDER BY t1.a;
----
2	3	1	4
3	4	2	5

statement error
SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
----

statement error
SELECT * FROM t1 JOIN t2 USING(a);
----

statement error
SELECT * FROM t1 INNER OUTER JOIN t2;
----