
create table n1(a int, b int not null);
create table n2(a int, b int not null);
create table n3(a int, b int not null);


-- single table
select * from n1;
select * from n1 where a > 2;

-- two tables join
select * from n1, n2 where n1.a = n2.a;
select * from n1 inner join n2 on n1.a = n2.a;
select * from n1 left join n2 on n1.a = n2.a;
select * from n1 full join n2 on n1.a = n2.a;

-- 3+ tables join.

select * from n1 inner join n2 on n1.a = n2.a left join n3 on n2.a = n3.a;
select * from n1 left join n2 on n1.a = n2.a left join n3 on n2.a = n3.a;
select * from n1 full join n2 on n1.a = n2.a left join n3 on n2.a = n3.a;
select * from n1 left join n2 on n1.a = n2.a full join n3 on n2.a = n3.a;

select * from n1 left join (select * from n2 as n2(x, y) full join n3 on n2.x = n3.a) n2 on n1.a = n2.a;


-- Test partitioned case.
create table p (a int, b int, c int not null) partition by list(a);

create table p0 partition of p for values in (1);
create table p1 partition of p for values in (2) partition by list(b);
create table p1_1 partition of p1 for values in (1);
create table p1_2(c int not null, b int, a int);
alter table p1 attach partition p1_2 for values in (2);
create table p3 (b int, a int, c int not null);
alter table p attach partition p3 for values in (3);


select oid, relname from pg_class where relname in ('p', 'p0', 'p1', 'p3', 'p1_1', 'p1_2');
select * from p;

select * from p where a > 0;

drop table n1;
drop table n2;
drop table n3;
drop table p;
