LEFT JOIN LATERAL can remove rows from LHS

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

Responses

Browse pgsql-hackers by date

  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