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
