postgres.git / summary / log / commit / refs
commit 8cdf3c9214feb48179a3cc0fbb0bae0e0f38f2cb
Author: David Rowley <drowley@postgresql.org>
Date: Wed Nov 11 01:32:11 2026 +0000
First released in: 19.0
Fix Memoize using the wrong equality operator for cache keys
Memoize compared cache keys with the type's default equality operator
rather than the operator used by the parameterized join clause. If a
hashable join operator considers fewer values equal than the type's
default equality operator, two outer values could share one cache
entry, and the inner rows cached for one value were returned for the
other. One example is a byte-wise equality operator on a column with
a case-insensitive nondeterministic collation.
To fix, have check_memoizable() take the hash equality operators from
the clause's hashjoinoperator. When both inputs are of the same type,
use the join operator itself. For cross-type operators, look up the
same-type equality members in the join operator's hash opfamily. If
no such member exists, don't set a hash equality operator, so Memoize
isn't used for that clause. Clauses that aren't hashjoinable keep
using the type's default equality operator along with binary mode, as
before.
Backpatch to all supported versions.
Bug: #19853
Reported-by: chunling qin <303677365@qq.com>
Author: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/19853-22fde551f71cbadb@postgresql.org
Backpatch-through: 15
src/backend/optimizer/plan/initsplan.c | 57 ++++++++++++++++++-------
src/test/regress/expected/collate.icu.utf8.out | 58 ++++++++++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 41 ++++++++++++++++++
3 files changed, 141 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index cec76342f6e..43491873ba8 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -4040,12 +4040,25 @@ check_hashjoinable(RestrictInfo *restrictinfo)
* If the restrictinfo's clause is suitable to be used for a Memoize node,
* set the left_hasheqoperator and right_hasheqoperator to the hash equality
* operator that will be needed during caching.
+ *
+ * When the clause is hashjoinable, the cache must use the same notion of
+ * equality as the join operator, as the type's default equality operator
+ * could consider values equal that the join operator does not. If both
+ * inputs are of the same type, we can just use the join operator itself;
+ * otherwise we look up the same-type equality operators in the join
+ * operator's hash opfamily, and if there are none, Memoize won't be used.
+ * When the clause is not hashjoinable, we use the type's default equality
+ * operator and Memoize uses binary mode.
+ *
+ * Note: check_hashjoinable() must be called on the given RestrictInfo before
+ * calling this function.
*/
static void
check_memoizable(RestrictInfo *restrictinfo)
{
TypeCacheEntry *typentry;
Expr *clause = restrictinfo->clause;
+ Oid hashop = restrictinfo->hashjoinoperator;
Oid lefttype;
Oid righttype;
@@ -4057,23 +4070,37 @@ check_memoizable(RestrictInfo *restrictinfo)
return;
lefttype = exprType(linitial(((OpExpr *) clause)->args));
-
- typentry = lookup_type_cache(lefttype, TYPECACHE_HASH_PROC |
- TYPECACHE_EQ_OPR);
-
- if (OidIsValid(typentry->hash_proc) && OidIsValid(typentry->eq_opr))
- restrictinfo->left_hasheqoperator = typentry->eq_opr;
-
righttype = exprType(lsecond(((OpExpr *) clause)->args));
- /*
- * Lookup the right type, unless it's the same as the left type, in which
- * case typentry is already pointing to the required TypeCacheEntry.
- */
- if (lefttype != righttype)
- typentry = lookup_type_cache(righttype, TYPECACHE_HASH_PROC |
+ if (OidIsValid(hashop))
+ {
+ if (lefttype == righttype)
+ restrictinfo->left_hasheqoperator =
+ restrictinfo->right_hasheqoperator = hashop;
+ else
+ (void) get_compatible_hash_operators(restrictinfo->hashjoinoperator,
+ &restrictinfo->left_hasheqoperator,
+ &restrictinfo->right_hasheqoperator);
+ }
+ else
+ {
+ /* No hashjoinoperator. Memoize will use binary mode. */
+ typentry = lookup_type_cache(lefttype, TYPECACHE_HASH_PROC |
TYPECACHE_EQ_OPR);
- if (OidIsValid(typentry->hash_proc) && OidIsValid(typentry->eq_opr))
- restrictinfo->right_hasheqoperator = typentry->eq_opr;
+ if (OidIsValid(typentry->hash_proc) && OidIsValid(typentry->eq_opr))
+ restrictinfo->left_hasheqoperator = typentry->eq_opr;
+
+ /*
+ * Lookup the right type, unless it's the same as the left type, in
+ * which case typentry is already pointing to the required
+ * TypeCacheEntry.
+ */
+ if (lefttype != righttype)
+ typentry = lookup_type_cache(righttype, TYPECACHE_HASH_PROC |
+ TYPECACHE_EQ_OPR);
+
+ if (OidIsValid(typentry->hash_proc) && OidIsValid(typentry->eq_opr))
+ restrictinfo->right_hasheqoperator = typentry->eq_opr;
+ }
}
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 4db10b3e8c1..636f0dfefc3 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2237,6 +2237,64 @@ GROUP BY t2.x ORDER BY t2.x;
ghi | 10
(4 rows)
+ROLLBACK;
+-- Likewise, but with a case-sensitive hashable join operator that is not the
+-- type's default equality operator. Memoize must use the join operator's
+-- notion of equality for its cache keys.
+BEGIN;
+CREATE FUNCTION test_memoize_byteeq(text, text) RETURNS bool
+ AS $$ BEGIN RETURN $1::bytea = $2::bytea; END $$
+ LANGUAGE plpgsql IMMUTABLE STRICT;
+CREATE FUNCTION test_memoize_bytehash(text) RETURNS int
+ AS $$ SELECT hashtext($1 COLLATE "C") $$
+ LANGUAGE sql IMMUTABLE STRICT;
+CREATE OPERATOR === (LEFTARG = text, RIGHTARG = text,
+ FUNCTION = test_memoize_byteeq, COMMUTATOR = ===, HASHES);
+CREATE OPERATOR CLASS test_memoize_byteops FOR TYPE text USING hash AS
+ OPERATOR 1 === (text, text),
+ FUNCTION 1 test_memoize_bytehash(text);
+CREATE TABLE test_memoize_ci (x text COLLATE case_insensitive);
+INSERT INTO test_memoize_ci SELECT x FROM test3ci, generate_series(1, 10);
+CREATE TABLE test_memoize_ci_inner (x text COLLATE case_insensitive);
+INSERT INTO test_memoize_ci_inner SELECT x FROM test3ci;
+CREATE INDEX ON test_memoize_ci_inner USING hash (x test_memoize_byteops);
+ANALYZE test_memoize_ci, test_memoize_ci_inner;
+SET LOCAL enable_hashjoin TO off;
+SET LOCAL enable_mergejoin TO off;
+SET LOCAL enable_seqscan TO off;
+-- Ensure we get a Memoize plan
+EXPLAIN (COSTS OFF)
+SELECT t1.x COLLATE "C", t2.x COLLATE "C", count(*) FROM test_memoize_ci t1
+ INNER JOIN test_memoize_ci_inner t2 ON t2.x === t1.x
+GROUP BY 1, 2 ORDER BY 1, 2;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: ((t1.x)::text) COLLATE "C", ((t2.x)::text) COLLATE "C"
+ -> HashAggregate
+ Group Key: (t1.x)::text, (t2.x)::text
+ -> Nested Loop
+ -> Seq Scan on test_memoize_ci t1
+ Disabled: true
+ -> Memoize
+ Cache Key: t1.x
+ Cache Mode: logical
+ -> Index Scan using test_memoize_ci_inner_x_idx on test_memoize_ci_inner t2
+ Index Cond: (x === t1.x)
+(12 rows)
+
+-- Ensure we get the expected results
+SELECT t1.x COLLATE "C", t2.x COLLATE "C", count(*) FROM test_memoize_ci t1
+ INNER JOIN test_memoize_ci_inner t2 ON t2.x === t1.x
+GROUP BY 1, 2 ORDER BY 1, 2;
+ x | x | count
+-----+-----+-------
+ ABC | ABC | 10
+ abc | abc | 10
+ def | def | 10
+ ghi | ghi | 10
+(4 rows)
+
ROLLBACK;
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 937efa17041..b2d6f7d64ca 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -821,6 +821,47 @@ GROUP BY t2.x ORDER BY t2.x;
ROLLBACK;
+-- Likewise, but with a case-sensitive hashable join operator that is not the
+-- type's default equality operator. Memoize must use the join operator's
+-- notion of equality for its cache keys.
+BEGIN;
+
+CREATE FUNCTION test_memoize_byteeq(text, text) RETURNS bool
+ AS $$ BEGIN RETURN $1::bytea = $2::bytea; END $$
+ LANGUAGE plpgsql IMMUTABLE STRICT;
+CREATE FUNCTION test_memoize_bytehash(text) RETURNS int
+ AS $$ SELECT hashtext($1 COLLATE "C") $$
+ LANGUAGE sql IMMUTABLE STRICT;
+CREATE OPERATOR === (LEFTARG = text, RIGHTARG = text,
+ FUNCTION = test_memoize_byteeq, COMMUTATOR = ===, HASHES);
+CREATE OPERATOR CLASS test_memoize_byteops FOR TYPE text USING hash AS
+ OPERATOR 1 === (text, text),
+ FUNCTION 1 test_memoize_bytehash(text);
+
+CREATE TABLE test_memoize_ci (x text COLLATE case_insensitive);
+INSERT INTO test_memoize_ci SELECT x FROM test3ci, generate_series(1, 10);
+CREATE TABLE test_memoize_ci_inner (x text COLLATE case_insensitive);
+INSERT INTO test_memoize_ci_inner SELECT x FROM test3ci;
+CREATE INDEX ON test_memoize_ci_inner USING hash (x test_memoize_byteops);
+ANALYZE test_memoize_ci, test_memoize_ci_inner;
+
+SET LOCAL enable_hashjoin TO off;
+SET LOCAL enable_mergejoin TO off;
+SET LOCAL enable_seqscan TO off;
+
+-- Ensure we get a Memoize plan
+EXPLAIN (COSTS OFF)
+SELECT t1.x COLLATE "C", t2.x COLLATE "C", count(*) FROM test_memoize_ci t1
+ INNER JOIN test_memoize_ci_inner t2 ON t2.x === t1.x
+GROUP BY 1, 2 ORDER BY 1, 2;
+
+-- Ensure we get the expected results
+SELECT t1.x COLLATE "C", t2.x COLLATE "C", count(*) FROM test_memoize_ci t1
+ INNER JOIN test_memoize_ci_inner t2 ON t2.x === t1.x
+GROUP BY 1, 2 ORDER BY 1, 2;
+
+ROLLBACK;
+
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
SET enable_indexonlyscan = off;
[parent: 0b355d4277e7]