From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, tel(at)jklm(dot)no, pgsql-bugs(at)lists(dot)postgresql(dot)org, Peter Geoghegan <pg(at)bowt(dot)ie> |
Subject: | Re: BUG #17975: Nested Loop Index Scan returning wrong result |
Date: | 2023-06-15 00:16:54 |
Message-ID: | CAApHDvrewBui0hCXSEaR=X4Tpyo3hRZu6kTRuf4r4kGEA3LC9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Thu, 15 Jun 2023 at 11:59, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andres Freund <andres(at)anarazel(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2023-06-15 00:23:59 | Re: BUG #17975: Nested Loop Index Scan returning wrong result |
Previous Message | Thomas Munro | 2023-06-15 00:12:19 | Re: BUG #17949: Adding an index introduces serialisation anomalies. |