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