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

On Mon, Nov 9, 2026 at 12:59 AM Tender Wang <tndrwang(at)gmail(dot)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