Re: BUG #17978: Unexpected error: "wrong varnullingrels (b) (expected (b 5)) for Var 6/2" triggered by JOIN

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> My fuzzer finds a bug in Postgres, which triggers an unexpected error in
> Postgres.

Thanks for the report!

I initially thought this was something wrong with subplan parameters,
but it reproduces without a subplan. I reduced it to this test case
in the regression database:

select ss1.unique1
from
(select t0.unique1
from onek t0 left join onek t1 on t0.hundred = t1.thousand
where t0.hundred <> coalesce(t1.tenthous, -1)) as ss1
left join (select 1 as c_1) as ss2 on ss1.unique1 = ss2.c_1
right join int4_tbl i4 on ss1.unique1 = i4.f1;

ERROR: wrong varnullingrels (b) (expected (b 5)) for Var 6/7

Temporarily disabling the setrefs.c crosscheck shows that it's
trying to create this plan:

Hash Right Join
Hash Cond: (t1.thousand = t0.hundred)
Filter: (t0.hundred <> COALESCE(t1.tenthous, '-1'::integer))
-> Seq Scan on onek t1
-> Hash
-> Nested Loop Left Join
-> Seq Scan on int4_tbl i4
-> Index Scan using onek_unique1 on onek t0
Index Cond: (unique1 = i4.f1)

which is the wrong join order: the filter condition can't be
applied at that join level. So the nullingrel cross-check
has caught a real bug, but why the bug? Pre-v16, this would
have been prevented by the delay_upper_joins mechanism.
I convinced myself that we didn't need that anymore, but
maybe I was mistaken. It could also be some smaller problem.
It's curious that the bug doesn't reproduce if you remove the
visibly-useless join to ss2:

regression=# explain (costs off) select ss1.unique1
regression-# from
regression-# (select t0.unique1
regression(# from onek t0 left join onek t1 on t0.hundred = t1.thousand
regression(# where t0.hundred <> coalesce(t1.tenthous, -1)) as ss1
regression-# right join int4_tbl i4 on ss1.unique1 = i4.f1;

Hash Right Join
Hash Cond: (t0.unique1 = i4.f1)
-> Hash Left Join
Hash Cond: (t0.hundred = t1.thousand)
Filter: (t0.hundred <> COALESCE(t1.tenthous, '-1'::integer))
-> Seq Scan on onek t0
-> Hash
-> Seq Scan on onek t1
-> Hash
-> Seq Scan on int4_tbl i4

So maybe the logic is fundamentally correct but gets confused by
intermediate outer joins. Needs more time to look at than I have
today, but I'll add an open item.

regards, tom lane