Re: Optimize IS DISTINCT FROM with non-nullable inputs - Mailing list pgsql-hackers

On Tue, Jan 27, 2026 at 11:32 AM Tender Wang <tndrwang@gmail.com> wrote:
> Interesting optimization. I look through the v2-0001 patch. In the
> commit message, it says:
> ...
>  This patch extends the optimization to cases where inputs are non-constant but
>  proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
>  folds to constant TRUE if "x" is known to be non-nullable.
> ...
>
> But I found that the case "x IS DISTINCT FROM NULL"  is converted to
> NullTest in transformAExprDistinct().
> It will be optimized in the "case T_NullTest:" not by this patch.

Well, while it's true that the parser would do this transformation for
"literal" NULLs, here we are talking more about "calculated" NULLs.
Consider "not_null_col IS DISTINCT FROM (1 + NULL)".

Another case is custom plans for prepared statements (e.g., WHERE
not_null_col IS DISTINCT FROM $1), which will inject NULL constants
directly into the DistinctExpr during planning, and the parser does
not have a chance to transform it into a NullTest.

> The test case can't cover the following codes:
> +                                       /*
> +                                        * If one input is an explicit
> NULL constant, and the
> +                                        * other is a non-nullable
> expression, the result is
> +                                        * always TRUE.
> +                                        */
> +                                       if (has_null_input)
> +                                               return
> makeBoolConst(true, false);

Right.  I'll need to adjust the test cases to cover this.

- Richard