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

Andres Freund <andres@anarazel.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?

            regards, tom lane