statement ok
create table x(id int primary key, a int, b int);

statement ok
create table y(id int primary key, c int, d int);

statement ok
insert into x values (0, 1, 2), (1, 1, 3);

query IIII
select a, b, c, d from x join y on a = c;
----

statement ok
insert into y values (0, 1, 5), (1, 1, 6), (2, 2, 7);

query IIII
select a, b, c, d from x join y on a = c;
----
1   2   1   5
1   3   1   5
1   2   1   6
1   3   1   6

statement ok
drop table x;

statement ok
drop table y;

statement ok
create table a(id int primary key, v1 int, v2 int);

statement ok
create table b(id int primary key, v3 int, v4 int);

statement ok
insert into a values (0, 1, 1), (1, 2, 2), (2, 3, 3);

query IIII rowsort
select v1, v2, v3, v4 from a left join b on v1 = v3;
----
1 1 null null
2 2 null null
3 3 null null

statement ok
insert into b values (0, 1, 100), (1, 3, 300), (2, 4, 400);

query IIII
select v1, v2, v3, v4 from a left join b on v1 = v3;
----
1 1 1 100
3 3 3 300
2 2 null null

query IIII rowsort
select v1, v2, v3, v4 from a right join b on v1 = v3;
----
1 1 1 100
3 3 3 300
null null 4 400

query IIII rowsort
select v1, v2, v3, v4 from a full join b on v1 = v3;
----
1 1 1 100
2 2 null null
3 3 3 300
null null 4 400

statement ok
drop table a;

statement ok
drop table b;

statement ok
create table a(id int primary key, v1 int, v2 int);

statement ok
create table b(id int primary key, v3 int, v4 int, v5 int);

statement ok
insert into a values (0, 1, 1), (1, 2, 2), (2, 3, 3);

statement ok
insert into b values (0, 1, 1, 1), (1, 2, 2, 2), (2, 3, 3, 4), (3, 1, 1, 5);

query IIIII rowsort
select v1, v2, v3, v4, v5 from a join b on v1 = v3 and v2 = v4;
----
1   1   1   1   1
1   1   1   1   5
2   2   2   2   2
3   3   3   3   4

query IIIII rowsort
select v1, v2, v3, v4, v5 from a join b on v1 = v3 and v2 = v4 and v1 < v5;
----
1   1   1   1   5
3   3   3   3   4