| 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: | Whole Thread | Raw Message | 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 |