LEFT JOIN LATERAL can remove rows from LHS
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/18/2013 01:52 AM, Jeremy Evans wrote:
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)
This is a bug. If you block the optimizer from rearranging the lateral
join condition, it gives the correct answer:
No blocking:
SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n)) j
ON true;
n | n
---+---
(0 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..65.01 rows=12 width=8) (actual time=0.027..0.027 rows=0 loops=1)
Filter: (i.n = j.n)
Rows Removed by Filter: 1
-> Seq Scan on i (cost=0.00..1.01 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1)
-> Seq Scan on j (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 0.084 ms
(6 rows)
Blocking:
SELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE (i.n = j.n) OFFSET 0) j ON true;
n | n
----+---
10 |
(1 row)
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..41.25 rows=12 width=8) (actual time=0.014..0.015 rows=1 loops=1)
-> Seq Scan on i (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
-> Seq Scan on j (cost=0.00..40.00 rows=12 width=4) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (i.n = n)
Total runtime: 0.057 ms
(5 rows)
Vik
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 06/18/2013 01:52 AM, Jeremy Evans wrote:
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 have added this to the list of 9.3 blockers.
https://wiki.postgresql.org/wiki/PostgreSQL_9.3_Open_Items
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers