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.
Thank you in advance for any help,
Ron
