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

Hi,

On 2023-06-14 14:12:31 -0700, Andres Freund wrote:
> I think it's a problem with the uniqueness determination / missing a
> qual / index selection.
> 
> There are two rows in b with b.c_id = 13880, except that one of them has a
> NULL a_id:
> 
> => SELECT * FROM b WHERE c_id = 13880;
> ┌────────┬───────┬────────┐
> │   id   │ c_id  │  a_id  │
> ├────────┼───────┼────────┤
> │ 326048 │ 13880 │ (null) │
> │ 572151 │ 13880 │ 955968 │
> └────────┴───────┴────────┘
> (2 rows)
> 
> . The uniqueness information comes from:
>   "index_a_cannot_share_c" UNIQUE, btree (c_id) WHERE a_id IS NOT NULL
> 
> But note that we aren't using that index, we use
>   "index_b_c_id" btree (c_id)
> 
> which of course also contains the a_id = NULL row.
> 
> 
> We either need to force the index that we got the uniqueness information to be
> used when it is partial, or add the quals from the partial unique index to all
> other index scans.

I suspect this is an issue going back to 9c7f5229. Indeed, 9.6 doesn't
reproduce the issue (9c7f5229 was in 10). I haven't bisected it down to that,
but it seems pretty likely - and if it's not that commit, it's a closely
related one.

It's not immediately obvious to me how to nicely fix this in a backpatchable
way. An easy fix would be to not allow predicate indexes at all anymore in
relation_has_unique_index_for(), but that's a pretty big cannon - fixes the
issue though.

Greetings,

Andres Freund