On 06/18/2013 01:52 AM, Jeremy Evans wrote: > Maybe I am misunderstanding how LATERAL is supposed to work, but my > expectation is that doing a LEFT JOIN should not remove rows from > the LHS. I would expect all of the following select queries would > return a single row, but that isn't the case: > > CREATE TABLE i (n integer); > CREATE TABLE j (n integer); > INSERT INTO i VALUES (10); > SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j) j ON true; > n | n > ----+--- > 10 | > (1 row) > > SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n)) j ON > true; > n | n > ---+--- > (0 rows) > > INSERT INTO j VALUES (10); > SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n)) j ON > true; > n | n > ----+---- > 10 | 10 > (1 row) > > SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n)) j ON > false; > n | n > ---+--- > (0 rows)
This is a bug. If you block the optimizer from rearranging the lateral join condition, it gives the correct answer: No blocking: SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n)) j ON true; n | n ---+--- (0 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=0.00..65.01 rows=12 width=8) (actual time=0.027..0.027 rows=0 loops=1) Filter: (i.n = j.n) Rows Removed by Filter: 1 -> Seq Scan on i (cost=0.00..1.01 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1) -> Seq Scan on j (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1) Total runtime: 0.084 ms (6 rows) Blocking: SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n) OFFSET 0) j ON true; n | n ----+--- 10 | (1 row) QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=0.00..41.25 rows=12 width=8) (actual time=0.014..0.015 rows=1 loops=1) -> Seq Scan on i (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1) -> Seq Scan on j (cost=0.00..40.00 rows=12 width=4) (actual time=0.001..0.001 rows=0 loops=1) Filter: (i.n = n) Total runtime: 0.057 ms (5 rows) Vik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers