Re: IS NOT DISTINCT FROM statement

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> On Sat, 9 Mar 2019 at 01:25, Artur Zając <azajac(at)ang(dot)com(dot)pl> wrote:
>> CREATE OR REPLACE FUNCTION smarteq(v1 int,v2 INT) RETURNS BOOL AS
>> $BODY$
>> SELECT (CASE WHEN v2 IS NULL THEN (v1 IS NULL) ELSE v1=v2 END);
>> $BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE;

> The transformation mentioned earlier could only work if the arguments
> of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with
> Params since the values are unknown to the planner.

Just looking at this example, I'm wondering if there'd be any value in
adding a rule to eval_const_expressions that converts IS DISTINCT FROM
with one constant-NULL argument into an IS NOT NULL test on the other
argument. Doing anything with the general case would be hard, as you
mentioned, but this "workaround" suggests that the OP isn't actually
concerned with the general case.

[ experiments... ] Oh, look at this:

regression=# explain verbose select f1 is distinct from null from int4_tbl;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on public.int4_tbl (cost=0.00..1.05 rows=5 width=1)
Output: (f1 IS NOT NULL)
(2 rows)

regression=# explain verbose select f1 is not distinct from null from int4_tbl;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on public.int4_tbl (cost=0.00..1.05 rows=5 width=1)
Output: (f1 IS NULL)
(2 rows)

So somebody already inserted this optimization, but I don't see it
happening in eval_const_expressions ... oh, it's way earlier,
in transformAExprDistinct:

/*
* If either input is an undecorated NULL literal, transform to a NullTest
* on the other input. That's simpler to process than a full DistinctExpr,
* and it avoids needing to require that the datatype have an = operator.
*/
if (exprIsNullConstant(rexpr))
return make_nulltest_from_distinct(pstate, a, lexpr);
if (exprIsNullConstant(lexpr))
return make_nulltest_from_distinct(pstate, a, rexpr);

I'm hesitant to call that wrong; the ability to avoid a dependency on an
"=" operator is kind of nice. But it doesn't help for cases requiring a
Param substitution.

So maybe if we *also* had a check for this in eval_const_expressions,
that would address the OP's problem. But the use-case would be a bit
narrow given that the parser is catching the simplest case.

regards, tom lane