| 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
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | shihao zhong | 2026-11-17 02:04:46 | Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns |