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-000000014YH-3KbC 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-0MMU for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Nov 2026 09:58:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:58778) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPuiI-00000005Tsm-2BjQ for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:41:06 +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 1xPuiF-00000000oGj-2Aex for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:41:05 +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=vCeZo5wmEZNdsordMV5dZnmZfQDfP6qBPD0Dfc8Nl4E=; b=WoBlGokEpSu4XT6BLv5y3N6Zdy oo7nFPsamLG3Yerc7zrPlmwdSXSV6mJd2t48tbCXmtHVObHQFhYyM9wlUedVVR76LsSy12ndagX2p rhy0AmzJf4AtwRMYw0ppDFdEfAskWlAuKkj0gKwMmCdrzCcLYsNz4v9Nwt2bkU7O/yvuuJ8WZBCRR 9edD4whOeAoxM430yaIvCf8galL9fZayd7O4ej/HrVDlporg/AfbHGGBP/ruY2xMNWO+Gz49ziBst 4MrOwKmYNJEyg5XdDGHF0MBDkXritLLoDbLwPl2OioUv9GFLokxtnlQ4QmJ9iCflF9bOH/PuVRI55 irWOPdVw==; 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 1xPuiG-00000002I75-1ZFO for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:41:04 +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 1xPuiE-00000005cZ5-2yH1 for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 04:41:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19853: Memoize returns wrong rows when the join operator is hashable but its equality semantics differ from 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:40:49 +0000 Message-ID: <19853-22fde551f71cbadb@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: 19853 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64; platform-independent (reproduced ide Description: =20 **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 '=3D' = =E2=80=94 here a user-defined `=3D=3D=3D` that compares the raw bytes (case-SENSITIVE= ) while the column uses a nondeterministic (case-insensitive) collation =E2=80=94 t= he default texteq treats 'abc' =3D=3D 'ABC', so the cache wrongly shares one e= ntry 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 =E2=80=94= 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 =E2=80=94 the operator dimension rema= ins: 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 =3D icu, locale =3D '@colStrength=3Dsecondary= ', deterministic =3D 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 =3D $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 =3D=3D=3D (LEFTARG =3D text, RIGHTARG =3D text, FUNCTION = =3D byte_eq, COMMUTATOR =3D =3D=3D=3D, HASHES); CREATE OPERATOR CLASS hops FOR TYPE text USING hash AS OPERATOR 1 =3D=3D=3D (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 =3D=3D=3D 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 =3D=3D=3D 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=3Doff; SELECT t1.x COLLATE "C" AS ox, t2.x COLLATE "C" AS ix, count(*) FROM o t1 JOIN i t2 ON t2.x =3D=3D=3D 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` =E2=80=94 the type's default equality =E2=80=94 = 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) =E2=80=94 otherwise cache key equality disa= grees 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") =E2=80=94 same symptom family, = collation dimension, fixed by 35273454065; this report is the operator dimension that the fix did not cover.