Re: Ignored join clause

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> "Andrew" == Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> Andrew> My suspicion is that this is an interaction between lateral and
> Andrew> join reordering. Looking into it further.

> I think I was wrong, and that in fact this is a much more general
> problem which amounts to a lack of communication between
> get_joinrel_parampathinfo and extract_actual_join_clauses.

I've not got to the bottom of it yet either, but I notice that if you
replace the VALUES thingy with a plain table, the bug goes away:

regression=# create table q1(x int, y int);
CREATE TABLE
regression=# insert into q1 values (1, 10), (2, 20);
INSERT 0 2
regression=# SELECT *
FROM t
LEFT JOIN q1 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)

The plan's much saner-looking, too:

Nested Loop Left Join (cost=246.68..32758.05 rows=14351 width=48)
Output: t.x, t.ys, q1.x, q1.y, q2.y
Join Filter: (q2.y = q1.y)
-> Merge Left Join (cost=246.68..468.29 rows=14351 width=44)
Output: t.x, t.ys, q1.x, q1.y
Merge Cond: (t.x = q1.x)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Output: t.x, t.ys
Sort Key: t.x
-> Seq Scan on public.t (cost=0.00..22.70 rows=1270 width=36)
Output: t.x, t.ys
-> Sort (cost=158.51..164.16 rows=2260 width=8)
Output: q1.x, q1.y
Sort Key: q1.x
-> Seq Scan on public.q1 (cost=0.00..32.60 rows=2260 width=8)
Output: q1.x, q1.y
-> Function Scan on pg_catalog.unnest q2 (cost=0.00..1.00 rows=100 width=4)
Output: q2.y
Function Call: unnest(t.ys)

So I'm suspicious that the real issue here has to do with the weird
subselect representation we use for VALUES; either that's broken in
itself somehow, or more likely the planner gets confused while
flattening it.

regards, tom lane