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

Поиск
Список
Период
Сортировка
Искать

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

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      19852
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 19rc1
Operating system:   Linux x86_64; platform-independent (reproduced on
Description:        

**Description:**

The NOT IN -> anti-join conversion (new in 19) assumes that the equality
operator, for non-null inputs, never returns NULL — that is what allows `x
NOT IN (SELECT y ...)` to be rewritten as an anti-join. Commit b7eee09eab7
fixed one case where the assumption fails (sub-select outputs wrapped in a
coercion whose cast function can return NULL). But a simpler failure
remains: **the operator itself can be non-strict** — a user-defined operator
whose underlying function returns NULL for non-null inputs. The conversion
still fires, the NULL semantics of NOT IN are lost, and rows that must be
filtered out survive.

The project has already acknowledged this invariant elsewhere: commit
f5635c2786a ("Fix expr_is_nonnullable() for IS DISTINCT FROM") removed a
branch that assumed operators never return NULL for non-null inputs, with
the message "the result is NULL if the underlying equality operator returns
NULL, which nothing forbids. This could lead to wrong query results."

**Reproduction (self-contained SQL; verified on a build containing
b7eee09eab7):**

```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)

-- oracle (same expression forced through a SubPlan) - CORRECT:
SELECT x FROM onn WHERE (NOT (x ?= ANY (SELECT y FROM inn))) IS TRUE;
-- 3
ROLLBACK;
```

**Actual results:** the anti-join rewrite returns {3, 7}; the same
expression evaluated through a subplan (or by SQL's NOT IN semantics)
returns {3}.

**Expected behavior:** the NOT IN -> anti-join conversion must not fire (or
must preserve the NULL-filtering semantics) when the equality operator can
return NULL for non-null inputs — just as the conversion already declines
the case where the sub-select outputs need a coercion (b7eee09eab7), and
just as expr_is_nonnullable() no longer assumes operators are
non-NULL-returning (f5635c2786a).

**Related:** bug report by BHUVAN CHILUKUTI (2026-10-23, "Incorrect NOT IN
results with implicit casts returning NULL after anti-join optimization") —
same conversion, coerced-outputs case, fixed by b7eee09eab7; this report is
the operator-strictness case that the fix did not cover.




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

От:
Tender Wang <tndrwang@gmail.com>
Дата:
Hi,

PG Bug reporting form  于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


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

От:
Keyerror Smart <smartkeyerror@gmail.com>
Дата:
On Mon, Nov 9, 2026 at 12:59 AM Tender Wang <tndrwang@gmail.com> wrote:
> The ?= operator the reproducer created is not a member of any B-tree
> or hash operator family, so op_is_safe_index_member() should reject
> the conversion.

Yeah, the original script never gets to the conversion.  But if you
put that kind of operator into an opclass, it does, and the result is
wrong.  On current master:

CREATE FUNCTION eq_null(int4, int4) RETURNS bool AS $f$
  SELECT CASE WHEN $1 = 7 AND $2 <> 7 THEN NULL ELSE $1 = $2 END
$f$ LANGUAGE sql IMMUTABLE;
CREATE OPERATOR ?= (PROCEDURE = eq_null, LEFTARG = int4, RIGHTARG = int4);
CREATE OPERATOR CLASS weird_hash FOR TYPE int4 USING hash AS
  OPERATOR 1 ?=, FUNCTION 1 hashint4(int4);

CREATE TABLE onn (x int NOT NULL);
INSERT INTO onn VALUES (2), (3), (7);
CREATE TABLE inn (y int NOT NULL);
INSERT INTO inn VALUES (2), (4);

SELECT x FROM onn WHERE NOT (x ?= ANY (SELECT y FROM inn));
 x
---
 3
 7

That's a Nested Loop Anti Join; forcing the SubPlan with "... IS TRUE"
gives just 3.

Whether this counts as a bug is debatable.  A btree version of this is
plain user error, since btree.sgml requires "=" to be an equivalence
relation.  The hash case is fuzzier: the operator above is reflexive
and consistent with hashint4(), and I couldn't find anything in the
docs saying an opfamily member must not return NULL for non-null
inputs.  The planner just assumes it.

I don't think we can check this from the catalogs (strictness says
nothing about it), so the simplest fix is probably to document the
requirement rather than change the code.  Restricting the conversion
to built-in operators would also work, but it seems like overkill for
something you can only hit with a deliberately weird opclass.

Thoughts?

--
Zhenglong Li
FAQ