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