Hi Shihao, > Yes, please post the hand-placed hunks for 14 to 16.
Attached, one per branch. In 14-16 paraminfo_get_equal_hashops has no list_member() dedup, so the block goes right after the clause's outer expr is chosen and before it is appended to *param_exprs, so the canonicalized expr is what becomes the cache key. I re-verified each on the current tip of its branch (14.24, 15.19, 16.15): make check passes, and the stress oracle (enable_memoize on vs off over a matrix of nondeterministic-collation shapes) goes from 39/52 wrong cases to 0/52, with identical row counts. For contrast I also built the mechanical fuzz-placed hunk: it compiles and make check still passes, but it puts the block after the append and leaves all 39 cases wrong, which is why these are placed by hand. The LEFT JOIN case is the one reachable on 14-16; the anti-join path is 19+ only. Regards, Manu
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index 429257f..596501f 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -435,6 +435,15 @@ paraminfo_get_equal_hashops(PlannerInfo *root, ParamPathInfo *param_info, else expr = (Node *) lsecond(opexpr->args); + /* + * Ensure the cache key exposes the clause's input collation, so + * that Memoize compares keys the same way the clause does. See + * comments for canonicalize_ec_expression. + */ + expr = (Node *) canonicalize_ec_expression((Expr *) expr, + exprType(expr), + opexpr->inputcollid); + *operators = lappend_oid(*operators, rinfo->hasheqoperator); *param_exprs = lappend(*param_exprs, expr);
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index bad3dd1..9b4da67 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -450,6 +450,15 @@ paraminfo_get_equal_hashops(PlannerInfo *root, ParamPathInfo *param_info, return false; } + /* + * Ensure the cache key exposes the clause's input collation, so + * that Memoize compares keys the same way the clause does. See + * comments for canonicalize_ec_expression. + */ + expr = (Node *) canonicalize_ec_expression((Expr *) expr, + exprType(expr), + opexpr->inputcollid); + *operators = lappend_oid(*operators, hasheqoperator); *param_exprs = lappend(*param_exprs, expr);
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index 9e488eb..349c58f 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -508,6 +508,15 @@ paraminfo_get_equal_hashops(PlannerInfo *root, ParamPathInfo *param_info, return false; } + /* + * Ensure the cache key exposes the clause's input collation, so + * that Memoize compares keys the same way the clause does. See + * comments for canonicalize_ec_expression. + */ + expr = (Node *) canonicalize_ec_expression((Expr *) expr, + exprType(expr), + opexpr->inputcollid); + *operators = lappend_oid(*operators, hasheqoperator); *param_exprs = lappend(*param_exprs, expr);
