pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19852: NOT IN -> anti-join conversion gives wrong results when the equality operator returns NULL for non-n
Date: Sun, 08 Nov 2026 04:31:23 +0000
Message-ID: <19852-a9f0c3d01d964bde@postgresql.org> (raw)

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.








view thread (3+ messages)  latest in thread

Message-ID: <19852-a9f0c3d01d964bde@postgresql.org>
Permalink:  ../19852-a9f0c3d01d964bde@postgresql.org/
Also on:    postgresql.org/message-id/19852-a9f0c3d01d964bde@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19852: NOT IN -> anti-join conversion gives wrong results when the equality operator returns NULL for non-n
  In-Reply-To: <19852-a9f0c3d01d964bde@postgresql.org>
  References: <19852-a9f0c3d01d964bde@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox