Re: BUG #19852: NOT IN -> anti-join conversion gives wrong results when the equality operator returns NULL for non-n

Hi,

PG Bug reporting form <noreply(at)postgresql(dot)org> 于2026年11月8日周日 17:54写道：
> ```sql
> BEGIN;
> CREATE FUNCTION eq_null(int4,int4) RETURNS bool AS $f$
> SELECT CASE WHEN $1 = 7 THEN NULL ELSE $1 = $2 END $f$
> LANGUAGE sql IMMUTABLE;
> CREATE OPERATOR ?= (PROCEDURE=eq_null, LEFTARG=int4, RIGHTARG=int4);
>
> CREATE TEMP TABLE onn (x int NOT NULL);
> INSERT INTO onn VALUES (2),(3),(7);
> CREATE TEMP TABLE inn (y int NOT NULL);
> INSERT INTO inn VALUES (2),(4);
>
> -- sanity: the operator returns NULL for non-null inputs (7 ?= 2):
> SELECT 7 ?= 2 AS v, (7 ?= 2) IS NULL AS is_null; -- (NULL) | t
>
> -- manual truth: NOT(7 ?= ANY(ARRAY[2,4])) is NULL, so the x=7 row must be
> filtered:
> SELECT (NOT (7 ?= ANY (ARRAY[2,4]))) AS notin_7,
> (NOT (7 ?= ANY (ARRAY[2,4]))) IS NULL AS is_null; -- (NULL) | t
>
> -- candidate (rewritten to an anti-join) - WRONG: returns x=7 as well:
> SELECT x FROM onn WHERE NOT (x ?= ANY (SELECT y FROM inn));
> -- 3
> -- 7 <- wrong: x=7 must be filtered (NOT IN semantics is NULL ->
> false)
>

I cannot reproduce the reported result with the supplied test case on
either 19rc1 or current master.
In both cases, the query returns only:

```
x
---
3
(1 row)
```

The plan on master is:

```
Seq Scan on onn
Filter: (NOT (ANY CASE WHEN (x = 7) THEN NULL::boolean
ELSE (x = (SubPlan any_1).col1) END))
SubPlan any_1
-> Materialize
-> Seq Scan on inn
```

So the expression is still evaluated using a SubPlan and has not been
converted to an anti-join.

Also, the `?=` operator created by the reproducer is not a member of
any B-tree or hash operator family, so `op_is_safe_index_member()`
should reject the conversion.

--
Thanks,
Tender Wang