Re: LEFT JOIN LATERAL can remove rows from LHS

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
onj  (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=1width=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.001rows=0 loops=1)        Filter: (i.n = n)Total runtime: 0.057 ms
 
(5 rows)



Vik