From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
Cc: 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: Mon, 19 Jun 2023 16:59:15 -0400
Message-ID: <3153450.1687208355@sss.pgh.pa.us> (raw)
In-Reply-To: <2979923.1687190824@sss.pgh.pa.us>
References: <17978-12f3d93a55297266@postgresql.org>
<2615002.1687031843@sss.pgh.pa.us>
<CAMbWs48pMV0m6UathaqkUtrC=93TsikSPxQnKYun1+SoJxt0Yw@mail.gmail.com>
<2979923.1687190824@sss.pgh.pa.us>
I wrote:
> Anyway, what I'm inclined to do is flesh out the attached by updating
> the comments for remove_useless_results() and then push it. Later
> on we can look for why it's not finding the better join order; that's
> a separable issue, and if it is about avoid-clauseless-joins then we
> might choose to live with it rather than incur a lot of planner cost
> to fix it.
I couldn't resist poking into that, and it seems there's less here
than meets the eye. I'd been guessing that the presence or absence
of a t2 reference in the WHERE clause was affecting this, but no: the
SpecialJoinInfos look exactly the same for both queries, and the set
of joins considered is the same in both. What is causing the
different plan shape is that the selectivity estimates for these
WHERE clauses are a lot different:
regression=# explain
regression-# select t2.f1 from int4_tbl t2
regression-# left join int4_tbl t3 on t2.f1 > 0
regression-# where t3.f1 is null;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..2.49 rows=1 width=4)
Join Filter: (t2.f1 > 0)
Filter: (t3.f1 IS NULL)
-> Seq Scan on int4_tbl t2 (cost=0.00..1.05 rows=5 width=4)
-> Materialize (cost=0.00..1.07 rows=5 width=4)
-> Seq Scan on int4_tbl t3 (cost=0.00..1.05 rows=5 width=4)
(6 rows)
regression=# explain
regression-# select t2.f1 from int4_tbl t2
regression-# left join int4_tbl t3 on t2.f1 > 0
regression-# where t2.f1 <> coalesce(t3.f1, -1);
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..2.55 rows=8 width=4)
Join Filter: (t2.f1 > 0)
Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer))
-> Seq Scan on int4_tbl t2 (cost=0.00..1.05 rows=5 width=4)
-> Materialize (cost=0.00..1.07 rows=5 width=4)
-> Seq Scan on int4_tbl t3 (cost=0.00..1.05 rows=5 width=4)
and that ends up with the other join order looking better. We can
synthesize a different non-strict, t3-only qual with a similar
selectivity estimate:
regression=# explain
regression-# select t2.f1 from int4_tbl t2
regression-# left join int4_tbl t3 on t2.f1 > 0
regression-# where -1 <> coalesce(t3.f1, -1);
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..2.55 rows=8 width=4)
Join Filter: (t2.f1 > 0)
Filter: ('-1'::integer <> COALESCE(t3.f1, '-1'::integer))
-> Seq Scan on int4_tbl t2 (cost=0.00..1.05 rows=5 width=4)
-> Materialize (cost=0.00..1.07 rows=5 width=4)
-> Seq Scan on int4_tbl t3 (cost=0.00..1.05 rows=5 width=4)
and then if you plug that into the whole query you get a t4-last plan:
regression=# explain (costs off)
regression-# select * from int4_tbl t1
regression-# left join ((select t2.f1 from int4_tbl t2
regression(# left join int4_tbl t3 on t2.f1 > 0
regression(# where -1 <> coalesce(t3.f1, -1) ) s
regression(# left join tenk1 t4 on s.f1 > 1)
regression-# on s.f1 = t1.f1;
QUERY PLAN
-------------------------------------------------------------------------
Nested Loop Left Join
Join Filter: (t2.f1 > 1)
-> Hash Right Join
Hash Cond: (t2.f1 = t1.f1)
-> Nested Loop Left Join
Join Filter: (t2.f1 > 0)
Filter: ('-1'::integer <> COALESCE(t3.f1, '-1'::integer))
-> Seq Scan on int4_tbl t2
-> Materialize
-> Seq Scan on int4_tbl t3
-> Hash
-> Seq Scan on int4_tbl t1
-> Materialize
-> Seq Scan on tenk1 t4
(14 rows)
So, nothing to see here after all.
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, guofenglinux@gmail.com, 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: <3153450.1687208355@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