Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Michael PaquierDate: 2013-06-17 23:59:14
Subject: Re: [9.4 CF 1] Added in missing patches
Previous:From: Josh BerkusDate: 2013-06-17 22:58:04
Subject: [9.4 CF 1] What the 5-day Deadline Means

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group