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

On Thu, 15 Jun 2023 at 11:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andres Freund <andres@anarazel.de> writes:
> > 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?

In check_index_predicates(), we seem to use the results of
generate_join_implied_equalities() as index predicate proofs. That
seems fishy because surely those are only valid to use in cases after
the join for the particular predicate is evaluated.

In this case a.id = b.a_id AND c.id = b.c_id are used as proofs. I
didn't debug all the way, but I assume we deduce that the NOT NULL
index is ok due to the strict join quals.

David