control sortmode rowsort

statement ok
drop table if exists t1

statement ok
CREATE TABLE t1 (t1_id INT PRIMARY KEY, k INT, v INT)

statement ok
INSERT INTO t1 VALUES (0, -1, -1), (1, 0, 4), (2, 2, 1), (3, 3, 4), (4, 5, 4)

statement ok
drop table if exists t2

statement ok
CREATE TABLE t2 (t2_id INT PRIMARY KEY, x INT, y INT)

statement ok
INSERT INTO t2 VALUES (0, 0, 5), (1, 1, 3), (2, 1, 4), (3, 3, 2), (4, 3, 3), (5, 4, 6)

query IIII
SELECT k, v, x, y FROM t1 NATURAL JOIN t2 ORDER BY k, v, x, y
----
-1 -1 0 5
-1 -1 1 3
-1 -1 1 4
-1 -1 3 2
-1 -1 3 3
-1 -1 4 6
0 4 0 5
0 4 1 3
0 4 1 4
0 4 3 2
0 4 3 3
0 4 4 6
2 1 0 5
2 1 1 3
2 1 1 4
2 1 3 2
2 1 3 3
2 1 4 6
3 4 0 5
3 4 1 3
3 4 1 4
3 4 3 2
3 4 3 3
3 4 4 6
5 4 0 5
5 4 1 3
5 4 1 4
5 4 3 2
5 4 3 3
5 4 4 6

query IIII
SELECT k, v, x, y FROM t1 JOIN t2 ON k = x
----
0 4 0 5
3 4 3 2
3 4 3 3

statement ok
DROP TABLE t1

statement ok
DROP TABLE t2

statement ok
CREATE TABLE t1 (t1_id INT PRIMARY KEY, k INT NULL)

statement ok
INSERT INTO t1 VALUES (0, 0), (1, null)

statement ok
CREATE TABLE t2 (t2_id INT PRIMARY KEY, x INT NULL)

statement ok
INSERT INTO t2 VALUES (0, 0), (1, null)

query II
SELECT k, x FROM t1 NATURAL JOIN t2
----
0 0
0 null
null 0
null null

query II
SELECT k, x FROM t1 JOIN t2 ON k = x
----
0 0

statement ok
drop table if exists t44798_0

statement ok
drop table if exists t44798_1

#TODO: different types on joins
# statement ok
# CREATE TABLE t44798_0(c0 TINYINT PRIMARY KEY)

# statement ok
# CREATE TABLE t44798_1(c0 SMALLINT PRIMARY KEY)

# statement ok
# INSERT INTO t44798_0(c0) VALUES(0), (1), (2)

# statement ok
# INSERT INTO t44798_1(c0) VALUES(0), (2), (4)

# query I
# SELECT * FROM t44798_0 NATURAL JOIN t44798_1 order by c0
# ----
# 0
# 2

statement ok
drop table if exists l

statement ok
drop table if exists r

statement ok
CREATE TABLE l (l INT PRIMARY KEY)

statement ok
INSERT INTO l VALUES (1), (2)

statement ok
CREATE TABLE r (r INT PRIMARY KEY)

statement ok
INSERT INTO r VALUES (1)

query II
SELECT *, true FROM (SELECT l FROM l WHERE l NOT IN (SELECT r FROM r))
----
2 true