Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE

Hi Denis,

On 11/7/26 10:09, Denis Smirnov wrote:
> 1. Other never-true forms are not folded
>
>   CREATE TEMP TABLE s (a int);
>   INSERT INTO s VALUES (1), (NULL);
>
>   EXPLAIN (COSTS OFF) SELECT * FROM s
>     WHERE a NOT IN (42, NULL);          -- One-Time Filter: false
>   EXPLAIN (COSTS OFF) SELECT * FROM s
>     WHERE a IN (NULL, NULL);            -- Seq Scan
>   EXPLAIN (COSTS OFF) SELECT * FROM s
>     WHERE a = ANY ('{}'::int[]);        -- Seq Scan
>
> With a strict operator, every comparison against an all-NULL array
> yields NULL. With an empty array, ANY is false whatever the operator.
> saop_never_true() recognizes neither.

I've extended saop_never_true() to recognize ANY over an all-NULL
constant array when the operator is strict, and ANY over an empty array
regardless of operator strictness. Both now fold to FALSE in qual contexts.

> 2. FULL JOIN and MERGE errors disappear
>
>   CREATE TEMP TABLE f1 (a int);
>   CREATE TEMP TABLE f2 (a int);
>   SELECT * FROM f1 FULL JOIN f2 ON f1.a NOT IN (1, NULL);
>
>   master: ERROR: FULL JOIN is only supported with merge-joinable or
>                  hash-joinable join conditions
>   v8:     succeeds
>
> Whether a statement works now depends on the parameter value:
>
>   PREPARE p(int[]) AS
>     SELECT * FROM f1 FULL JOIN f2 ON f1.a <> ALL ($1);
>   EXECUTE p('{1,NULL}');
>   EXECUTE p('{1,2}');
>
>   master: both fail with the same ERROR
>   v8:     the first succeeds, the second fails
>
> MERGE now modifies data where it used to fail:
>
>   CREATE TEMP TABLE mt (a int, b int);
>   CREATE TEMP TABLE ms (a int, b int);
>   INSERT INTO mt VALUES (1, 1), (2, 2);
>   INSERT INTO ms VALUES (1, 100), (3, 300);
>   MERGE INTO mt USING ms ON mt.a NOT IN (1, NULL)
>     WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = -2
>     WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (ms.a, ms.b);
>
>   master: the same ERROR
>   v8:     MERGE 4

I think allowing these statements to execute is appropriate. The
previous error reflects an implementation restriction: the planner
cannot build a supported FULL JOIN plan for the original condition. Once
the condition is proven never true, the existing support for FULL JOIN
ON FALSE can handle it correctly.

For MERGE, no source row matches a target row, so both NOT MATCHED
branches execute as specified. The example therefore updates the two
existing target rows and inserts the two source rows.

With a custom plan, the ability to prove the condition never true can
depend on the parameter value. I've documented this behavior in the
commit message and added regression tests for FULL JOIN, the
parameterized case, and the resulting MERGE data.

> 3. Evaluation errors disappear
>
>   CREATE TEMP TABLE d (a int);
>   INSERT INTO d VALUES (1);
>   SELECT * FROM d WHERE (a / 0) NOT IN (1, NULL);
>
>   master: ERROR: division by zero
>   v8:     0 rows
>
> master already behaves this way for similar forms, so this seems
> acceptable:
>
>   SELECT * FROM d WHERE (a / 0) = NULL;                -- 0 rows
>   SELECT * FROM d WHERE a NOT IN (NULL, 1 / (a - 1));  -- 0 rows

+1. Eliminating a never-true qual can skip evaluation of its scalar
argument, including any runtime error it would otherwise raise. This is
consistent with the existing expression evaluation rules and the
examples you gave. I've mentioned it in the commit message and added a
regression test.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/