Re: Assert failure in try_nestloop_path()

On Tue, Sep 8, 2026 at 3:20 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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