Re: Query result differences between PostgreSQL 17 vs 16

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