Re: BUG #19798: "failed to build any 4-way joins" with ROLLUP over a LATERAL subquery that references the nullable s

PG Bug reporting form <noreply(at)postgresql(dot)org> 于2026年10月27日周二 04:32写道：
> As far as I can tell, since that commit pull_up_simple_subquery()
> wraps the subquery output a.x, a lateral reference, in a
> PlaceHolderVar evaluated at {c,d} when the query has grouping sets. So
> c and d must be joined first, and the only legal 4-way join is
> {b,a} + {c,d}. No join clause connects these two. In the bushy loop of
> join_search_one_level() the pair is considered only with {b,a} as
> old_rel, since {b,a} comes first in the level-2 list. {b,a} is skipped
> there because it has no join clauses and has_join_restriction() is
> false for it: it contains its outer join, and a joinrel's
> lateral_referencers is always NULL. The 5-way and 6-way forms fail the
> same way, because there the pair is considered only with the smaller
> side, {b,a} or {b,a,f}, as old_rel.
>

Thanks for the report and the detailed analysis. I investigated this
and reproduced the problem.

The grouping-sets processing causes the pulled-up lateral subquery
output to be represented by a PlaceHolderVar.
In the original example, the resulting PlaceHolderInfo looks roughly like this:

[ph_eval_at] Bitmapset [7 8] ({c, d})
[ph_lateral] Bitmapset [2 3] ({a, outer-join relid})

The value is evaluated at the join of c and d, but it depends on a
value supplied by the completed outer join between b and a. Therefore,
the only usable final join is the bushy join

{b, a, outer-join relid} + {c, d}

No ordinary join clause exists between these two sides.

The problem is that join_search_one_level() discards the first side
before considering that pair. In the bushy-join loop, it has this
preliminary check:

if (old_rel->joininfo == NIL &&
!old_rel->has_eclass_joins &&
!has_join_restriction(root, old_rel))
continue;

For the completed b/a outer join, joininfo is empty, and there are no
relevant EC joins.
has_join_restriction() also returns false.
The outer join is already contained in the joinrel; its lateral_relids
and lateral_referencers are both empty, and the joinrel is not a
subset of the PHV's ph_eval_at set.

However, that joinrel contains all the relations in the PHV's
ph_lateral set, so it supplies the lateral parameters required to
evaluate the PHV at {c,d}.
If the pair reaches have_join_order_restriction(), that function
recognizes the relationship and the join can be built successfully.
The preliminary one-sided check prevents that from happening.

This is especially visible for the 2+2 case because each unordered
pair is considered only once.
The b/a joinrel appears first in the level-2 list and is skipped as
old_rel; when the c/d joinrel is reached, the search doesn't look back
at b/a.
The larger examples fail for the same reason, with the smaller
provider-side joinrel being skipped.

I considered a few possible fixes.

First, removing the preliminary check from the bushy-join loop fixes
the problem, and all regression tests pass.
The pairwise checks already reject unrelated combinations. However,
this makes the planner examine more bushy-join pairs and defeats the
purpose of the existing pruning heuristic, so it seems too broad.

Second, I tried teaching has_join_restriction() to return true when a
relation contains a PHV's complete ph_lateral set.
That also fixes the bug, but it produced three plan changes in join.sql.
This also changes the classification of such relations in other parts
of join search, while the missing case is specific to bushy-join
pruning here.

The attached patch therefore keeps the existing pruning and adds a
local exception for a joinrel that provides all lateral parameters
required by a PHV whose evaluation relations have not yet been joined.
The preliminary check allows such a relation, after which the existing
pairwise have_join_order_restriction() test determines whether a
particular bushy join should be attempted.

The standard regression tests pass without plan changes.
Any thoughts?

--
Thanks,
Tender Wang