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

Hi,

On 2023-06-14 19:59:26 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2023-06-14 19:02:48 -0400, Tom Lane wrote:
> >> 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).
>
> > 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 how did it decide that the partial index is predOK, if there's not
> a qual forcing a_id to not be null?

There is - but it's at a higher join level. That would prevent us from
returning a wrongly matching row, but in the inner_unique case we don't even
get to that point.

We obviously could make it correct by injecting the relevant check into the
index scan on the inner side, but it doesn't look trivial to do so.

Greetings,

Andres Freund