Re: Query result differences between PostgreSQL 17 vs 16

On Fri, Feb 21, 2025 at 11:13:17AM -0500, Ronald Cruz wrote:
> Hi,
>
> 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.
>
> I've attached two files here that can be used to reproduce what I'm seeing:
>
> schema_plus_data.sql - This contains a pg_dump of a reproducible test case with
> a contrived schema and dataset of our use case.
>
> reproducer.sql - This isn't necessary, but perhaps you find it helpful. This is
> a SQL script used to generate the schema and random data that hits this edge
> case. It was used to generate the previous file and it has reliably hit the
> issue so far from the handful of times I've tried.
>
> The query I'm observing issues for is the following:
>
> SELECT * FROM rc1
>     LEFT JOIN rc2 ON rc2.rc1_reference = rc1.description
>     LEFT JOIN rc3 ON rc2.id = rc3.rc2_reference
>     LEFT JOIN LATERAL rc_select(rc3.id) ON rc3.id IS NOT NULL;
>
> Under PostgreSQL 17, I'm seeing ~400k results returned, whereas in PostgreSQL
> 16, I see ~6k. The results I believe to be erroneous are those that have 'BUG
> HIT' in the output for PostgreSQL 17. These are results joined from rc_select
> where rc3.id is null. I'm not expecting to see any of these rows as is the case
> in PostgreSQL 16 output (and 15 as well from prior experience).
>
> I've observed this behavior in the latest PostgreSQL 17.4 but have also
> encountered this in 17.2 and 17.3. The OS being used is RHEL 9.5 (plow). Please
> let me know if you need any more information.

We have a known problem with composite types and NULL constraints in PG
17 that I think we are fixing in PG 18. I saw IS NOT NULL in your
query so I thought I would mention it:

https://www.postgresql.org/message-id/Z37p0paENWWUarj-%40momjian.us

We do have several NULL optimizations in PG 17.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.