From: Tom Lane <tgl@sss.pgh.pa.us>
To: zuming.jiang@inf.ethz.ch
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17978: Unexpected error: "wrong varnullingrels (b) (expected (b 5)) for Var 6/2" triggered by JOIN
Date: Sat, 17 Jun 2023 15:57:23 -0400
Message-ID: <2615002.1687031843@sss.pgh.pa.us> (raw)
In-Reply-To: <17978-12f3d93a55297266@postgresql.org>
References: <17978-12f3d93a55297266@postgresql.org>
PG Bug reporting form <noreply@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17978: Unexpected error: "wrong varnullingrels (b) (expected (b 5)) for Var 6/2" triggered by JOIN
In-Reply-To: <2615002.1687031843@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox