Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPzfB-000000014YG-3DhS for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Nov 2026 09:58:15 +0000 Received: from localhost ([127.0.0.1]:39208 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPzfC-00000005zLa-0K0Y for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Nov 2026 09:58:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:58562) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPuZd-00000005RqS-2h1s for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:32:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPuZZ-00000000oD4-3mYJ for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:32:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=YEGm43Fm+8+CLQh45QeutwP7H9j9d0fTEjVr/MRiWaQ=; b=nv7VbhHetxba5AihwXVynRnc3Y xLH9GGxB701DSR2fh0Tq9Lc6S8qwOynV6L/li1Yq1E2lejlL7OMOU9EQh0CGdbUOQ/pnh4hl78mtm rTtlzpCsJlx1Nqh34nMF2fep8z828uwmMqt/BionEeenP/zHW5oN5Spc0ldbSF/KuPMRR5DxTqvNh loYswFS9bTLOlCoiYZ9peepc0oBagHzpcqG2aCk0IavxSzW9EP497rJtzDE/8/1Ql9M0NbdgCucgp 4h0iNEQ9rBumD4iG/SOjxWKbushRYq4ZddcOqBEvIGSd9HW5eyr/N3XSnapd3o1aSuhjMAK5FBTQq HozzUiQA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPuZZ-00000002Hwu-1bku for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:32:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPuZX-00000005cDa-2RHm for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:32:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19852: NOT IN -> anti-join conversion gives wrong results when the equality operator returns NULL for non-n To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Sun, 08 Nov 2026 04:31:23 +0000 Message-ID: <19852-a9f0c3d01d964bde@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 **Description:** The NOT IN -> anti-join conversion (new in 19) assumes that the equality operator, for non-null inputs, never returns NULL =E2=80=94 that is what al= lows `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** =E2=80=94 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 =3D 7 THEN NULL ELSE $1 =3D $2 END $f$ LANGUAGE sql IMMUTABLE; CREATE OPERATOR ?=3D (PROCEDURE=3Deq_null, LEFTARG=3Dint4, RIGHTARG=3Dint4); 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 ?=3D 2): SELECT 7 ?=3D 2 AS v, (7 ?=3D 2) IS NULL AS is_null; -- (NULL) | t -- manual truth: NOT(7 ?=3D ANY(ARRAY[2,4])) is NULL, so the x=3D7 row must= be filtered: SELECT (NOT (7 ?=3D ANY (ARRAY[2,4]))) AS notin_7, (NOT (7 ?=3D ANY (ARRAY[2,4]))) IS NULL AS is_null; -- (NULL) | t -- candidate (rewritten to an anti-join) - WRONG: returns x=3D7 as well: SELECT x FROM onn WHERE NOT (x ?=3D ANY (SELECT y FROM inn)); -- 3 -- 7 <- wrong: x=3D7 must be filtered (NOT IN semantics is NULL -> false) -- oracle (same expression forced through a SubPlan) - CORRECT: SELECT x FROM onn WHERE (NOT (x ?=3D 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 =E2=80=94 just as the conversion already de= clines 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") = =E2=80=94 same conversion, coerced-outputs case, fixed by b7eee09eab7; this report is the operator-strictness case that the fix did not cover.