On Mon, Sep 7, 2026 at 12:03 PM Richard Guo <[email protected]> wrote: > Thanks for reviewing! I agree that back-patching into stable > branches isn't worth the risk of plan changes, given the lack of field > complaints. I've pushed this to master and v19.
I was curious whether there are other cases where we can end up with
duplicate qual clauses, so I added the attached Assert to verify that
the clauses to be enforced at a join or at a parameterized path's scan
contain no duplicate rinfo_serial, and the regression tests
immediately crashed :-O
One regression query that trips the Assert is:
explain (costs off)
select * from onek t1
left join onek t2 on t1.unique1 = t2.unique1
left join onek t3 on t2.unique1 = t3.unique1
left join onek t4 on t3.unique1 = t4.unique1 and t2.unique2 = t4.unique2;
It crashes in get_baserel_parampathinfo for base rel t4, whose
joininfo contains four clauses:
[0] serial=3 clause_relids={4 5 6} required={2 4 5 6} incompatible={3 7}
[1] serial=4 clause_relids={2 6} required={2 4 5 6} incompatible={3 7}
[2] serial=3 clause_relids={4 5 6} required={2 3 4 5 6} incompatible={7}
[3] serial=4 clause_relids={2 3 6} required={2 3 4 5 6} incompatible={7}
[0] and [2] are two clone variants of "t3.unique1 = t4.unique1".
Since the commuting outer join (relid 3) nulls no Var referenced by
this clause, the two variants are textually identical, differing only
in required_relids and incompatible_relids.
When t4 is probed with required_outer = {4, 5}, joinrelids is {4 5 6}.
Both variants are movable into the scan, and neither one's
incompatible_relids overlaps joinrelids, so both end up in
ppi_clauses.
It seems to me that something is wrong somewhere.
After a closer look, I don't think anything is wrong in
deconstruct_distribute_oj_quals. Both variants are needed for clause
selection at joins, where subbuild_joinrel_restrictlist checks
required_relids and incompatible_relids against the input relids. In
the normal join order, [0] is rejected because relid 3 appears in its
incompatible_relids, and [2] is the one applied; in the commuted
order, where t3/t4 join is performed below t1/t2 join, [2] is rejected
because relid 3 in its required_relids is not available, and [0] is
the one applied.
But a parameterized path cannot tell them apart. Since outer join 3
nulls no Var referenced by this clause, the parameterization looks
exactly the same whether that join is computed below the scan (the
normal join order, where [2] is the right variant) or above it (the
commuted order, where [0] is), so the same ParamPathInfo serves both
orders. For the same reason, either variant is correct in any join
order, so I think we should just enforce one of them and ignore the
rest? Thought?
- Richard
assert_no_duplicate_clause_serials.patch
Description: Binary data
