From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <bruce@momjian.us>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Ronald Cruz <cruz@rentec.com>
Cc: pgsql-bugs@lists.postgresql.org, "'Peter Ford'" <pford@rentec.com>
Cc: Aaron J. Garcia <agarcia@rentec.com>
Subject: Re: Query result differences between PostgreSQL 17 vs 16
Date: Fri, 21 Feb 2025 18:50:58 -0500
Message-ID: <3736132.1740181858@sss.pgh.pa.us> (raw)
In-Reply-To: <Z7kGplh70wbeX9su@momjian.us>
References: <f5320d3d-77af-4ce8-b9c3-4715ff33f213@rentec.com>
<Z7kGplh70wbeX9su@momjian.us>
Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Feb 21, 2025 at 11:13:17AM -0500, Ronald Cruz wrote:
>> We have observed an issue after upgrading to PostgreSQL 17 that caused us to
>> roll back to 16. Some of our queries are returning what I believe to be
>> erroneous results.
> We have a known problem with composite types and NULL constraints in PG
> 17 that I think we are fixing in PG 18.
There's no composite type at hand here. I think the problem is an
erroneous deduction from a column NOT NULL constraint. I can
reproduce a faulty plan in the regression database with
explain (costs off)
select * from tenk1
left join int4_tbl i on (unique1 = f1)
left join customer on (i.f1 = cid)
left join int4_tbl j on cid is not null;
v16 produces
Hash Left Join
Hash Cond: (tenk1.unique1 = i.f1)
-> Seq Scan on tenk1
-> Hash
-> Nested Loop Left Join
Join Filter: (customer.cid IS NOT NULL)
-> Hash Right Join
Hash Cond: (customer.cid = i.f1)
-> Seq Scan on customer
-> Hash
-> Seq Scan on int4_tbl i
-> Materialize
-> Seq Scan on int4_tbl j
but HEAD produces
Hash Left Join
Hash Cond: (tenk1.unique1 = i.f1)
-> Seq Scan on tenk1
-> Hash
-> Nested Loop Left Join
-> Hash Right Join
Hash Cond: (customer.cid = i.f1)
-> Seq Scan on customer
-> Hash
-> Seq Scan on int4_tbl i
-> Materialize
-> Seq Scan on int4_tbl j
Note the lack of any IS NOT NULL test. I think the planner has
convinced itself that the not null constraint on customer.cid
makes that test redundant, despite the fact that what it is
testing is a post-outer-join value that most certainly could
be null.
"git bisect" fingers this commit:
b262ad440edecda0b1aba81d967ab560a83acb8a is the first bad commit
commit b262ad440edecda0b1aba81d967ab560a83acb8a
Author: David Rowley <drowley@postgresql.org>
Date: Tue Jan 23 18:09:18 2024 +1300
Add better handling of redundant IS [NOT] NULL quals
I've not looked at the code, but I suspect that it is failing
to check varnullingrels before believing that it can trust
the applicability of table constraints.
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, bruce@momjian.us, dgrowleyml@gmail.com, cruz@rentec.com, pford@rentec.com, agarcia@rentec.com
Subject: Re: Query result differences between PostgreSQL 17 vs 16
In-Reply-To: <3736132.1740181858@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