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) Is the error in PostgreSQL or my understanding of LATERAL subqueries? Please CC me when responding as I don't currently subscribe to the list. Thanks, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers