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

From: Denis Smirnov <darthunix(at)gmail(dot)com>
To: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
Cc: Rustam ALLAKOV <rustamallakov(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
Subject: Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE
Date: 2026-11-17 02:26:23
Message-ID: 4A4E003B-83B4-48A7-8EAD-8A3FAEBD2FBD@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Ilia,

A few more things in v9:

1. A prepared FULL JOIN fails after five executions

CREATE TEMP TABLE f1 (a int);
CREATE TEMP TABLE f2 (a int);
INSERT INTO f1 VALUES (1), (2);
INSERT INTO f2 VALUES (3);
PREPARE p(int[]) AS
SELECT count(*) FROM f1 FULL JOIN f2 ON f1.a <> ALL ($1);

EXECUTE p('{1,NULL}'); -- runs 1-5: 3
EXECUTE p('{1,NULL}'); -- run 6 and later:
ERROR: FULL JOIN is only supported with merge-joinable or
hash-joinable join conditions

The parameter value is the same every time. After five custom
plans, the plan cache builds a generic plan to compare costs. The
generic plan cannot prove the condition never true, so planning
fails, and every later execution fails the same way. The new
regression test sets force_custom_plan, so it does not show this.

MERGE behaves the same way: the first five executions modify data,
then it fails. So does a PL/pgSQL function: the sixth call fails.

2. A NULL scalar argument is not folded

CREATE TEMP TABLE ta (arr int[]);
EXPLAIN (COSTS OFF) SELECT * FROM ta WHERE NULL::int = ANY (arr);

Seq Scan on ta
Filter: (NULL::integer = ANY (arr))

With a strict operator, this is NULL for a non-empty array and
FALSE for an empty one, so it is never
"WHERE $1 = ANY (arr)" with $1 = NULL behaves the same.

3. The planner_est tests from c95cd299 no longer test the estimator

SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 99, NULL]);
- Seq Scan on tenk1 (cost=N..N rows
- Filter: (unique1 <> ALL ('{1,2,99,NULL}'::integer[]))
+ Result (cost=N..N rows=0 width=N)
+ Replaces: Scan on tenk1
+ One-Time Filter: false

SELECT * FROM tenk1
WHERE unique1 <> ALL (ARRAY[1, 2, 98, (SELECT 99), NULL]);
- Seq Scan on tenk1 (cost=N..N rows
- Filter: (unique1 <> ALL (ARRAY[1, 2, 98, (InitPlan expr_1).col1, NULL::integer]))
- InitPlan expr_1
- -> Result (cost=N..N rows=1 width=N)
+ Result (cost=N..N rows=0 width=N)
+ Replaces: Scan on tenk1
+ One-Time Filter: false

These tests check the NULL short-circuw
both queries are folded, and the planner never reaches that code.
Adding "OR unique2 = 1" keeps them the
with the NULL and rows=9997 without it.

Best regards,
Denis Smirnov

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message shihao zhong 2026-11-17 02:04:46 Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns