Re: Convert NOT IN sublinks to anti-joins when safe

Zhang Mingli <zmlpostgres(at)gmail(dot)com> 于2026年2月4日周三 12:50写道：
>
> Hi,
>
> On Feb 3, 2026 at 15:13 +0800, Richard Guo <guofenglinux(at)gmail(dot)com>, wrote:
>
>
> This topic has been discussed several times in the past. Due to the
> semantic mismatch regarding NULL handling, NOT IN is not ordinarily
> equivalent to an anti-join. However, if we can prove that neither the
> outer expressions nor the subquery outputs can yield NULL values, it
> should be safe to convert NOT IN to an anti-join.
>
>
>
> Thanks for working on this!
> I've reviewed the patch and it looks good overall.
>
> I noticed several minor issues in the test case comments:
>
> 1. The comment doesn't match the SQL:
>
> +-- ANTI JOIN: outer side is defined NOT NULL, inner side is forced nonnullable
> +-- by qual clause
> +SELECT * FROM not_null_tab
> +WHERE id NOT IN (
> + SELECT t1.id
> + FROM null_tab t1
> + INNER JOIN null_tab t2 ON t1.id = t2.id
> + LEFT JOIN null_tab t3 ON TRUE
> +);
>
> The comment says "forced nonnullable by qual clause", but there's no explicit IS NOT NULL qual here.

I guess that it means "t1.id = t2.id". This join clause makes t1.id
forced non-nullable.

--
Thanks,
Tender Wang