From: | Jeremy Evans <code(at)jeremyevans(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | LEFT JOIN LATERAL can remove rows from LHS |
Date: | 2013-06-17 23:52:36 |
Message-ID: | 20130617235236.GA1636@jeremyevans.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2013-06-17 23:59:14 | Re: [9.4 CF 1] Added in missing patches |
Previous Message | Josh Berkus | 2013-06-17 22:58:04 | [9.4 CF 1] What the 5-day Deadline Means |