Ignored join clause

Hi,

It seems to me like PostgreSQL incorrectly removes a join clause when
planning some queries. I discovered this while debugging a large query,
which I below have simplified as much as I could. I suspect the bug may
be related to the lateral join but I am not sure.

This bug appears in both 10.3 and master (at least).

This works:

CREATE TABLE t AS SELECT 1 AS x, '{10,20}'::int[] AS ys;

SELECT *
FROM t
JOIN (VALUES (1, 10), (2, 20)) AS q1 (x, y) ON q1.x = t.x
LEFT JOIN unnest(ys) q2 (y) ON q2.y = q1.y;

x | ys | x | y | y
---+---------+---+----+----
1 | {10,20} | 1 | 10 | 10
(1 row)

This does not:

SELECT *
FROM t
LEFT JOIN (VALUES (1, 10), (2, 20)) AS q1 (x, y) ON q1.x = t.x
LEFT JOIN unnest(ys) q2 (y) ON q2.y = q1.y;

x | ys | x | y | y
---+---------+---+----+----
1 | {10,20} | 1 | 10 | 10
1 | {10,20} | 2 | 20 |
(2 rows)

I expect both these queries to return the same data on this data set,.
And the second row of the result violates "q1.x = t.x".

JOIN plan:

QUERY PLAN

---------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.05..56.90 rows=13 width=48)
Output: t.x, t.ys, "*VALUES*".column1, "*VALUES*".column2, q2.y
Join Filter: (q2.y = "*VALUES*".column2)
-> Hash Join (cost=0.05..27.64 rows=13 width=44)
Output: t.x, t.ys, "*VALUES*".column1, "*VALUES*".column2
Hash Cond: (t.x = "*VALUES*".column1)
-> Seq Scan on public.t (cost=0.00..22.70 rows=1270 width=36)
Output: t.x, t.ys
-> Hash (cost=0.03..0.03 rows=2 width=8)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2
width=8)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Function Scan on pg_catalog.unnest q2 (cost=0.00..1.00 rows=100
width=4)
Output: q2.y
Function Call: unnest(t.ys)
(15 rows)

LEFT JOIN plan:

QUERY PLAN

---------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.05..1826.15 rows=1270 width=48)
Output: t.x, t.ys, "*VALUES*".column1, "*VALUES*".column2, q2.y
-> Seq Scan on public.t (cost=0.00..22.70 rows=1270 width=36)
Output: t.x, t.ys
-> Hash Right Join (cost=0.05..1.45 rows=2 width=12)
Output: "*VALUES*".column1, "*VALUES*".column2, q2.y
Hash Cond: (q2.y = "*VALUES*".column2)
Join Filter: ("*VALUES*".column1 = t.x)
-> Function Scan on pg_catalog.unnest q2 (cost=0.00..1.00
rows=100 width=4)
Output: q2.y
Function Call: unnest(t.ys)
-> Hash (cost=0.03..0.03 rows=2 width=8)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2
width=8)
Output: "*VALUES*".column1, "*VALUES*".column2
(15 rows)

Andreas