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

Hi Shihao,

Thank you for verifying the report and getting back to me. I've reviewed
the suggestion and agree that we can keep the anti-join for built-in cast
functions and use the SubPlan only for user-defined ones.

Regards,
Bhuvan
________________________________
From: shihao zhong <zhong950419(at)gmail(dot)com>
Sent: Sunday, November 1, 2026 8:45 AM
To: BHUVAN CHILUKUTI <c(dot)bhuvan(at)iitg(dot)ac(dot)in>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>; Guofenglinux <guofenglinux(at)gmail(dot)com>; Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Incorrect NOT IN results with implicit casts returning NULL after anti-join optimization

You don't often get email from zhong950419(at)gmail(dot)com(dot) Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Hi Bhuvan,

Thanks for reporting it. I can reproduce this on master and on
REL_19_STABLE. It is a v19 regression from 383eb21ebff.

One concern from code. The patch also removes the anti-join fo
built-in casts, which never return NULL.

numeric_col NOT IN (SELECT int_col ...)
text_col NOT IN (SELECT char_col ...)

Could we keep the anti-join for built-in cast functions, and use the
SubPlan only for user-defined ones?

Thanks,
Shihao