Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization

On Sat, Oct 24, 2026 at 3:44 PM BHUVAN CHILUKUTI <c(dot)bhuvan(at)iitg(dot)ac(dot)in> wrote:
> I am reporting a query correctness issue in PostgreSQL involving the optimization of NOT IN subqueries into anti-joins when an implicit cast on the inner comparison operand can return SQL NULL for a non-NULL input.

I've pushed a fix for this bug.

As for keeping the conversion for built-in cast functions, checking
that the function is built-in is not enough, because a user can create
an implicit cast using a built-in function that returns NULL for some
non-null input.

One could argue that we should instead check whether the coercion
comes from a built-in pg_cast entry, but I'm not inclined to add that
much code for such a rare and AI-artificial case. I'd rather just
return false, in the spirit of the comment there:

* It is acceptable for this check not to be exhaustive. We can err on the
* side of conservatism: if we're not sure, it's okay to return FALSE.

- Richard