BUG #17975: Nested Loop Index Scan returning wrong result - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17975
Logged by:          Tor Erik Linnerud
Email address:      tel@jklm.no
PostgreSQL version: 15.3
Operating system:   MacOS 13.4, Linux 5.16
Description:

Hi, first let me say thanks for all the hard work that goes into Postgres.


I ran into a very specific query + index + data combination that appears to
return the wrong result. After much trial and error I’ve been able to
construct a dump to reproduce the problem, when running ANALYZE after the
import.

1. Grab the DB dump (13 MB)

curl -L "https://www.dropbox.com/s/k1ai0765gc2k98f/bug5.sql?dl=1" -o
bug5.sql

2. Create an empty database, import the dump and analyze:

createdb bug5 && psql -d bug5 -f bug5.sql && psql -d bug5 -c "ANALYZE"

3. Run queries:

psql -d bug5 -c "set enable_indexscan = 'off'; SELECT a.id FROM a JOIN b ON
b.a_id = a.id JOIN c ON c.id = b.c_id WHERE c.tag = '13880'"

1 row expected, get 1

psql -d bug5 -c "set enable_indexscan = 'on'; SELECT a.id FROM a JOIN b ON
b.a_id = a.id JOIN c ON c.id = b.c_id WHERE c.tag = '13880'"

1 row expected, get 0

Thanks,

Tor Erik