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 #19853: Memoize returns wrong rows when the join operator is hashable but its equality semantics differ from
Date: Sun, 08 Nov 2026 04:40:49 +0000
Message-ID: <19853-22fde551f71cbadb@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19853
Logged by:          chunling qin
Email address:      303677365@qq.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.








view thread (3+ messages)  latest in thread

Message-ID: <19853-22fde551f71cbadb@postgresql.org>
Permalink:  ../19853-22fde551f71cbadb@postgresql.org/
Also on:    postgresql.org/message-id/19853-22fde551f71cbadb@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 #19853: Memoize returns wrong rows when the join operator is hashable but its equality semantics differ from
  In-Reply-To: <19853-22fde551f71cbadb@postgresql.org>
  References: <19853-22fde551f71cbadb@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