*** /home/heikki/git-sandbox/postgresql/src/test/regress/expected/join.out 2014-08-18 09:38:55.146171394 +0300 --- /home/heikki/git-sandbox/postgresql/src/test/regress/results/join.out 2014-08-18 10:28:30.326491898 +0300 *************** *** 2579,2590 **** ----------------------------------------------------------- Sort Sort Key: t1.q1, t1.q2 ! -> Hash Left Join ! Hash Cond: (t1.q2 = t2.q1) Filter: (1 = (SubPlan 1)) ! -> Seq Scan on int8_tbl t1 -> Hash ! -> Seq Scan on int8_tbl t2 SubPlan 1 -> Limit -> Result --- 2579,2590 ---- ----------------------------------------------------------- Sort Sort Key: t1.q1, t1.q2 ! -> Hash Right Join ! Hash Cond: (t2.q1 = t1.q2) Filter: (1 = (SubPlan 1)) ! -> Seq Scan on int8_tbl t2 -> Hash ! -> Seq Scan on int8_tbl t1 SubPlan 1 -> Limit -> Result *************** *** 3589,3603 **** lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2); q1 | q2 | q1 | q2 | xq1 | yq1 | yq2 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- - 123 | 456 | | | 123 | | - 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 - 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 - 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 ! 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 ! 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | (10 rows) --- 3589,3603 ---- lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2); q1 | q2 | q1 | q2 | xq1 | yq1 | yq2 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 ! 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 + 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 + 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 + 123 | 456 | | | 123 | | 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | (10 rows) *************** *** 3606,3620 **** lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); q1 | q2 | q1 | q2 | xq1 | yq1 | yq2 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- - 123 | 456 | | | 123 | | - 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 - 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 - 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 - 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 ! 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 ! 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | (10 rows) --- 3606,3620 ---- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); q1 | q2 | q1 | q2 | xq1 | yq1 | yq2 ------------------+-------------------+------------------+-------------------+------------------+------------------+------------------- 4567890123456789 | 123 | 123 | 456 | 4567890123456789 | 123 | 456 ! 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 123 + 123 | 4567890123456789 | 4567890123456789 | 123 | 123 | 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 + 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 + 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | -4567890123456789 + 123 | 456 | | | 123 | | 4567890123456789 | -4567890123456789 | | | 4567890123456789 | | (10 rows) *************** *** 3623,3637 **** lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); q1 | q2 ------------------+------------------- - 123 | 456 - 123 | 4567890123456789 - 123 | 4567890123456789 - 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (10 rows) --- 3623,3637 ---- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); q1 | q2 ------------------+------------------- 4567890123456789 | 123 4567890123456789 | 123 4567890123456789 | 4567890123456789 + 123 | 4567890123456789 4567890123456789 | 4567890123456789 + 123 | 4567890123456789 4567890123456789 | 4567890123456789 + 123 | 4567890123456789 + 123 | 456 4567890123456789 | -4567890123456789 (10 rows) *************** *** 3990,4004 **** Hash Cond: (d.q1 = c.q2) -> Nested Loop Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)), (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)) ! -> Hash Left Join Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint)) ! Hash Cond: (a.q2 = b.q1) ! -> Seq Scan on public.int8_tbl a ! Output: a.q1, a.q2 -> Hash ! Output: b.q1, (COALESCE(b.q2, 42::bigint)) ! -> Seq Scan on public.int8_tbl b ! Output: b.q1, COALESCE(b.q2, 42::bigint) -> Seq Scan on public.int8_tbl d Output: d.q1, COALESCE((COALESCE(b.q2, 42::bigint)), d.q2) -> Hash --- 3990,4004 ---- Hash Cond: (d.q1 = c.q2) -> Nested Loop Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)), (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2)) ! -> Hash Right Join Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint)) ! Hash Cond: (b.q1 = a.q2) ! -> Seq Scan on public.int8_tbl b ! Output: b.q1, COALESCE(b.q2, 42::bigint) -> Hash ! Output: a.q1, a.q2 ! -> Seq Scan on public.int8_tbl a ! Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl d Output: d.q1, COALESCE((COALESCE(b.q2, 42::bigint)), d.q2) -> Hash *************** *** 4021,4062 **** lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select * from int4_tbl i where ss2.y > f1) ss3; ! QUERY PLAN ! --------------------------------------------------------------------------------------------------------- ! Nested Loop Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1 ! Join Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1) ! -> Hash Right Join ! Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) ! Hash Cond: (d.q1 = c.q2) ! -> Nested Loop ! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) ! -> Hash Right Join ! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) ! Hash Cond: (b.q1 = a.q2) ! -> Nested Loop ! Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint) ! Join Filter: (b.q1 < b2.f1) ! -> Seq Scan on public.int8_tbl b ! Output: b.q1, b.q2 ! -> Materialize Output: b2.f1 ! -> Seq Scan on public.int4_tbl b2 ! Output: b2.f1 ! -> Hash Output: a.q1, a.q2 ! -> Seq Scan on public.int8_tbl a ! Output: a.q1, a.q2 ! -> Seq Scan on public.int8_tbl d ! Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) ! -> Hash ! Output: c.q1, c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 ! -> Materialize ! Output: i.f1 ! -> Seq Scan on public.int4_tbl i ! Output: i.f1 (34 rows) -- check processing of postponed quals (bug #9041) --- 4021,4062 ---- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select * from int4_tbl i where ss2.y > f1) ss3; ! QUERY PLAN ! --------------------------------------------------------------------------------------------- ! Hash Right Join Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1 ! Hash Cond: (d.q1 = c.q2) ! Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1) ! -> Nested Loop ! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) ! -> Hash Right Join ! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) ! Hash Cond: (b.q1 = a.q2) ! -> Nested Loop ! Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint) ! Join Filter: (b.q1 < b2.f1) ! -> Seq Scan on public.int8_tbl b ! Output: b.q1, b.q2 ! -> Materialize ! Output: b2.f1 ! -> Seq Scan on public.int4_tbl b2 Output: b2.f1 ! -> Hash ! Output: a.q1, a.q2 ! -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 ! -> Seq Scan on public.int8_tbl d ! Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) ! -> Hash ! Output: c.q1, c.q2, i.f1 ! -> Nested Loop ! Output: c.q1, c.q2, i.f1 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 ! -> Materialize ! Output: i.f1 ! -> Seq Scan on public.int4_tbl i ! Output: i.f1 (34 rows) -- check processing of postponed quals (bug #9041) ======================================================================