Re: BUG #17975: Nested Loop Index Scan returning wrong result

Hi,

On 2023-06-14 15:17:31 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17975
> Logged by: Tor Erik Linnerud
> Email address: tel(at)jklm(dot)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

Uh, huh. I can reproduce that. And it's not just 15, reproduces in old
versions too.

The plan doesn't look obviously wrong:
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=1.26..17.46 rows=1 width=8) (actual time=0.063..0.065 rows=0 loops=1) │
│ Output: a.id │
│ Inner Unique: true │
│ -> Nested Loop (cost=0.84..16.88 rows=1 width=8) (actual time=0.056..0.059 rows=1 loops=1) │
│ Output: b.a_id │
│ Inner Unique: true │
│ -> Index Scan using c_tag_idx on public.c (cost=0.42..8.44 rows=1 width=8) (actual time=0.034..0.036 rows=1 loops=1) │
│ Output: c.id, c.tag │
│ Index Cond: ((c.tag)::text = '13880'::text) │
│ -> Index Scan using index_b_c_id on public.b (cost=0.42..8.44 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=1) │
│ Output: b.id, b.c_id, b.a_id │
│ Index Cond: (b.c_id = c.id) │
│ -> Index Only Scan using a_pkey on public.a (cost=0.42..0.59 rows=1 width=8) (actual time=0.002..0.003 rows=0 loops=1) │
│ Output: a.id │
│ Index Cond: (a.id = b.a_id) │
│ Heap Fetches: 0 │
│ Planning Time: 0.998 ms │
│ Execution Time: 0.148 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(18 rows)

It's not an IOS issue, it happens without IOS as well.

Something seems to be off with the relevant param - it's NULL. Haven't dug
deeper.

Greetings,

Andres Freund