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

Hi,

On 2023-06-14 19:02:48 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2023-06-14 14:12:31 -0700, Andres Freund wrote:
> >> 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 did not study this example yet, but generally we ignore predicate
> indexes unless their predicates can be proven from base restrictions
> of their table (that's what predOK means). If the base restrictions
> are enforced at scan level, which they should be, then uniqueness
> should hold at any join level regardless of whether we actually
> scanned with that index or some other way. Maybe we broke that
> chain of reasoning somehow?

It doesn't really hold at lower join levels with partial unique indexes, at
least as far as inner_unique goes. In this case we have one partial unique
index on b(c_id) WHERE a_id IS NOT NULL, and we have a plain index on b(c_id).
inner_unique is set to true based on the partial index - but then we decide
use the non-partial index for the index scan. That ends up returning a row
which with a_is = NULL, which won't find a match in the upper join levels. But
because the join is inner_unique, it'll not try to find another row on the
inner side.

> > 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.
>
> Yeah, that would be the easy way out if we don't find a better answer.
> But right at the moment I don't understand why this is failing.

Hope the above made it a bit clearer?

Greetings,

Andres Freund