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

On Thu, 15 Jun 2023 at 12:16, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Thu, 15 Jun 2023 at 11:59, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > 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.

Isn't it just a wrong assumption to use predOK for unique proofs for
all cases here. predOK seems ok to set to true for when it comes to
using that index for the query as by the time we finish evaluating all
the joins we'd have filtered out all rows that the partial index won't
contain... The problem just lies with the assumption that it's ok to
use the unique partial index as proofs before evaluation of all quals
that were used to determine predOK is true.

Perhaps we need another predOK field that just uses baserestrictinfos...

David