BUG #19853: Memoize returns wrong rows when the join operator is hashable but its equality semantics differ from

The following bug has been logged on the website:

Bug reference: 19853
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64; platform-independent (reproduced ide
Description:

**Description:**

Memoize decides "are these outer-scan parameters the same as a cached
entry's?" using the **type's default equality operator** (texteq for text),
not the operator the join clause actually uses. When the join uses a
hashable operator whose equality semantics differ from the default '=' —
here a user-defined `===` that compares the raw bytes (case-SENSITIVE) while
the column uses a nondeterministic (case-insensitive) collation — the
default texteq treats 'abc' == 'ABC', so the cache wrongly shares one entry
between the two outer values. The cached inner rows computed for one key are
then returned for the other: group counts silently merge across values the
join distinguishes.

A hash join on the very same tables/operator (control below) returns the
correct result, proving the operator, opclass and index are sound — only
Memoize is wrong.

This is the same invariant that the 2021 binary-comparison-mode fix
addressed for the -0.0/+0.0 case, and that commit 35273454065 (2026-11-04)
addressed for the collation dimension — the operator dimension remains:
cache key equality must agree with join-clause equality.

**Reproduction (self-contained SQL; verified identically on 18.6 and
master):**

```sql
CREATE COLLATION ci (provider = icu, locale = '@colStrength=secondary',
deterministic = false);

-- case-SENSITIVE byte equality (non-inlinable so the operator node
survives)
CREATE FUNCTION byte_eq(text, text) RETURNS bool AS
$$ BEGIN RETURN $1::bytea = $2::bytea; END $$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;
CREATE FUNCTION byte_hash(text) RETURNS integer AS
$$ SELECT hashtext($1) $$ LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE;

CREATE OPERATOR === (LEFTARG = text, RIGHTARG = text, FUNCTION = byte_eq,
COMMUTATOR = ===, HASHES);
CREATE OPERATOR CLASS hops FOR TYPE text USING hash AS
OPERATOR 1 === (text, text), FUNCTION 1 (text) byte_hash(text);

-- outer: 10 rows each of abc,ABC,def,DEF,ghi (nondeterministic collation)
CREATE TABLE o (x text COLLATE ci);
INSERT INTO o SELECT v FROM (VALUES ('abc'),('ABC'),('def'),('DEF'),('ghi'))
s(v), generate_series(1,10);
-- inner: 3x 'abc', 1x 'ABC', 1x 'def', 2x 'DEF', 1x 'ghi'
CREATE TABLE i (x text COLLATE ci);
INSERT INTO i VALUES
('abc'),('abc'),('abc'),('ABC'),('def'),('DEF'),('DEF'),('ghi');
CREATE INDEX i_idx ON i USING hash (x hops);
ANALYZE o; ANALYZE i;

-- A. HASH JOIN (control) - CORRECT: byte-equal pairs kept apart:
SELECT t1.x COLLATE "C" AS ox, t2.x COLLATE "C" AS ix, count(*)
FROM o t1 JOIN i t2 ON t2.x === t1.x GROUP BY 1,2 ORDER BY 1,2;
-- ABC|ABC|10 DEF|DEF|20 abc|abc|30 def|def|10 ghi|ghi|10

-- B. DEFAULT SETTINGS (planner picks Memoize) - WRONG: groups merged across
case:
SELECT t1.x COLLATE "C" AS ox, t2.x COLLATE "C" AS ix, count(*)
FROM o t1 JOIN i t2 ON t2.x === t1.x GROUP BY 1,2 ORDER BY 1,2;
-- ABC|abc|30 <- 'ABC' outer wrongly served the cache entry of 'abc'
-- DEF|def|10 <- same false merge; DEF|DEF|20 and abc rows lost their
true split

-- C. MEMOIZE OFF (oracle) - CORRECT (same as A):
SET enable_memoize=off;
SELECT t1.x COLLATE "C" AS ox, t2.x COLLATE "C" AS ix, count(*)
FROM o t1 JOIN i t2 ON t2.x === t1.x GROUP BY 1,2 ORDER BY 1,2;
RESET enable_memoize;
```

**Root cause:** `check_memoizable()` (initsplan.c) fills
`rinfo->left/right_hasheqoperator` from `lookup_type_cache(type,
TYPECACHE_EQ_OPR)->eq_opr` — the type's default equality — instead of the
join operator; `paraminfo_get_equal_hashops()` (joinpath.c) then builds the
cache keys with that default operator. Commit 35273454065 relabels the cache
keys with the clause's input collation but leaves this operator selection
untouched.

**Expected behavior:** when the join clause's operator differs from the
type's default equality operator, Memoize must not treat the default-equal
values as the same cache key (e.g. fall back to binary comparison, or
compare with the join operator) — otherwise cache key equality disagrees
with join equality, which is the same class of defect as the -0.0/+0.0 case
fixed in 2021.

**Related:** shihao zhong's report (2026-10-08, "[PG19] Wrong results from
Memoize with a nondeterministic collation") — same symptom family, collation
dimension, fixed by 35273454065; this report is the operator dimension that
the fix did not cover.