I was studying the PHV bug caused by join-removal and ran into an assertion failure with the query below, which is not related to join-removal. So start a new thread for it.
create table ta (id int primary key, x int);
create table tb (id int primary key, a_id int, x int);
create table tc (id int, x int);
explain (costs off)
select 1 from ta t1 left join
(select tb.x as bx, 1 as one from ta a2 left join tb on a2.id =
tb.a_id) t2 on true
left join lateral (select tc.x as cnt from tc where tc.id = t2.one
offset 0) t3
on t2.bx = t3.cnt;
TRAP: failed Assert("!have_unsafe_outer_join_ref(root, outerrelids,
inner_paramrels)")
The subquery t3 laterally references the PlaceHolderVar, which needs
to be evaluated at the a2/tb outer join. So t3's lateral_relids
include that outer join's relid. When join_is_legal() checks a
proposed join's minimum parameterization, it does not consider such
outer-join relids. Since the join clause of the t2/t3 join references
only tb, identity 3 allows this join to commute below the a2/tb join,
and join_is_legal() approves joining tb directly to t3. However,
this join includes part of the a2/tb join's required input, so that
outer join can only be completed above it, leaving t3's lateral
parameter forever unsatisfiable. Hence the Assert.
Attached is a patch that teaches join_is_legal() to reject a join
whose minimum parameterization includes an outer-join relid, if that
outer join cannot be formed outside the join.
Any thoughts?
- Richard
v1-0001-Disallow-joins-whose-lateral-references-need-an-u.patch
Description: Binary data
