On Tue, Sep 8, 2026 at 3:20 AM Tom Lane <[email protected]> wrote: > Having said that, I looked into the other query that hits the > assertion, which is later on in join.sql: > > select ss1.d1 from > tenk1 as t1 > inner join tenk1 as t2 > on t1.tenthous = t2.ten > inner join > int8_tbl as i8 > left join int4_tbl as i4 > inner join (select 64::information_schema.cardinal_number as d1 > from tenk1 t3, > lateral (select abs(t3.unique1) + random()) ss0(x) > where t3.fivethous < 0) as ss1 > on i4.f1 = ss1.d1 > on i8.q1 = i4.f1 > on t1.tenthous = ss1.d1 > where t1.unique1 < i4.f1;
Looking at the plan of this query, I found a duplicate clause in the
tree, and this time we put it in two different places, so my proposed
Assert cannot catch it (just as you mentioned).
Nested Loop
Output: (64)::information_schema.cardinal_number
Join Filter: (t1.tenthous =
((64)::information_schema.cardinal_number)::integer)
-> Seq Scan on public.tenk1 t3
...
-> Index Scan using tenk1_thous_tenthous on public.tenk1 t1
Index Cond: (t1.tenthous =
(((64)::information_schema.cardinal_number))::integer)
I can reproduce this issue with a simpler query:
set from_collapse_limit to 1;
explain (costs off)
select * from int4_tbl t1,
lateral (select * from tenk1 t2,
lateral (select t2.ten as x offset 0) s0
join tenk1 t3 on t3.unique2 = t1.f1
where t3.unique1 = t2.hundred + s0.x) ss1;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop
Join Filter: (t3.unique2 = t1.f1)
-> Nested Loop
Join Filter: (t3.unique1 = (t2.hundred + (t2.ten)))
-> Seq Scan on tenk1 t2
-> Nested Loop
-> Result
-> Index Scan using tenk1_unique1 on onek t3
Index Cond: (unique1 = (t2.hundred + (t2.ten)))
-> Materialize
-> Seq Scan on int4_tbl t1
(11 rows)
The condition "t3.unique1 = t2.hundred + s0.x" is enforced twice:
once as t3's index condition, and again as a join filter one level
up.
What happens here is that there are two RestrictInfos for this
condition. The first is the original qual (serial 1): when it is
recognized as an equivalence condition, it is absorbed into an EC and
stored there as a source clause, with parent_ec NULL. The second
(serial 4) is created when we build index paths for t3:
generate_implied_equalities_for_column asks create_join_clause for a
clause equating the index column t3.unique1 to the EC's other member,
passing parent_ec = ec to mark it as a potentially redundant join
clause.
In the selected plan, the serial-4 clause is enforced as t3's index
condition, and the serial-1 qual is handed back by
generate_join_implied_equalities at the t2/{s0,t3} join. The
redundancy should then be removed by create_nestloop_path, which drops
join clauses already enforced within the parameterized inner path, but
it matches them by rinfo_serial, so 1 does not match 4, and the
condition is enforced twice.
I think a quick fix is to make create_join_clause copy the
rinfo_serial from an existing clause that connects the same two
members with the opposite parent_ec marking. The two clauses are
really the same condition, and sharing the serial number allows
create_nestloop_path to detect the redundancy. Please see attached.
- Richard
v1-0001-Fix-duplicate-enforcement-of-EC-derived-condition.patch
Description: Binary data
