USE tpch1;
/* From bug 2961 description. */
select top_part.n_nationkey top, minus_part.n_nationkey minus from (select n_nationkey from nation)
top_part left outer join  (select n_nationkey from nation) minus_part on ( top_part.n_nationkey = minus_part.n_nationkey ) order by 1, 2;
top	minus
0	0
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
select top_part.n_nationkey top, minus_part.n_nationkey minus from (select n_nationkey from nation) top_part left outer join  
(select n_nationkey from nation where n_nationkey < 5) minus_part on (top_part.n_nationkey = minus_part.n_nationkey ) order by 1, 2;
top	minus
0	0
1	1
2	2
3	3
4	4
5	NULL
6	NULL
7	NULL
8	NULL
9	NULL
10	NULL
11	NULL
12	NULL
13	NULL
14	NULL
15	NULL
16	NULL
17	NULL
18	NULL
19	NULL
20	NULL
21	NULL
22	NULL
23	NULL
24	NULL
/* Comment 1 */
select sub1.c1 x, sub2.c1 y from sub1 left join sub2 on sub1.c1 = sub2.c1 order by 1, 2;
x	y
1	1
1	1
1	1
2	2
2	2
2	2
3	NULL
4	NULL
5	NULL
6	6
select sub1.c1, x.c1 from sub1 left join (select c1 from sub2) x on sub1.c1 = x.c1 order by 1, 2;
c1	c1
1	1
1	1
1	1
2	2
2	2
2	2
3	NULL
4	NULL
5	NULL
6	6
/* Comment 3 */
select sub1.c1, x.c1 from sub1 join (select sub1.c1 as sub1_c1, sub2.c1 from sub1 left join sub2 on 
sub1.c1 = sub2.c1) x on sub1.c1 = x.sub1_c1 order by 1, 2;
c1	c1
1	1
1	1
1	1
2	2
2	2
2	2
3	NULL
4	NULL
5	NULL
6	6