0001-fix-having-case-collation.patch

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 933dcbf500..604e35b761 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1659,6 +1659,56 @@ having_collation_conflict_walker(Node *node, having_collation_ctx *ctx)
 		return false;
 	}
 
+	if (IsA(node, CaseExpr) && ((CaseExpr *) node)->arg != NULL)
+	{
+		CaseExpr   *cexpr = (CaseExpr *) node;
+		ListCell   *lc;
+		int			pushed = 0;
+		bool		found;
+
+		/*
+		 * The shorthand CASE rewrites to OpExpr(placeholder = valN) per WHEN,
+		 * where arg holds the GROUP Var.  Push each WHEN's inputcollid before
+		 * recursing into arg so we detect collation conflicts there.
+		 */
+		foreach(lc, cexpr->args)
+		{
+			CaseWhen   *cw = lfirst_node(CaseWhen, lc);
+			Oid			collid = exprInputCollation((Node *) cw->expr);
+
+			if (OidIsValid(collid))
+			{
+				ctx->ancestor_collids = lappend_oid(ctx->ancestor_collids,
+													collid);
+				pushed++;
+			}
+		}
+
+		found = having_collation_conflict_walker((Node *) cexpr->arg, ctx);
+
+		while (pushed-- > 0)
+			ctx->ancestor_collids =
+				list_delete_last(ctx->ancestor_collids);
+
+		if (found)
+			return true;
+
+		/* Walk WHEN expressions, results, and defresult normally. */
+		foreach(lc, cexpr->args)
+		{
+			CaseWhen   *cw = lfirst_node(CaseWhen, lc);
+
+			if (having_collation_conflict_walker((Node *) cw->expr, ctx))
+				return true;
+			if (having_collation_conflict_walker((Node *) cw->result, ctx))
+				return true;
+		}
+		if (having_collation_conflict_walker((Node *) cexpr->defresult, ctx))
+			return true;
+
+		return false;
+	}
+
 	this_collid = exprInputCollation(node);
 	if (OidIsValid(this_collid))
 		ctx->ancestor_collids = lappend_oid(ctx->ancestor_collids,
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 8c3a369e21..a6d81deecd 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2261,6 +2261,51 @@ SELECT x, count(*) FROM test3ci GROUP BY x HAVING current_setting('server_versio
          ->  Seq Scan on test3ci
 (5 rows)
 
+-- Negative: collation conflict hidden inside shorthand CASE
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_sensitive THEN true ELSE false END;
+                                   QUERY PLAN                                    
+---------------------------------------------------------------------------------
+ HashAggregate
+   Group Key: x
+   Filter: CASE x WHEN 'abc'::text COLLATE case_sensitive THEN true ELSE false END
+   ->  Seq Scan on test3ci
+(4 rows)
+
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_sensitive THEN true ELSE false END;
+  x  | count 
+-----+-------
+ abc |     2
+(1 row)
+
+-- Positive: shorthand CASE with matching collation, safe to push
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_insensitive THEN true ELSE false END;
+                                      QUERY PLAN                                       
+---------------------------------------------------------------------------------------
+ HashAggregate
+   Group Key: x
+   ->  Seq Scan on test3ci
+         Filter: CASE x WHEN 'abc'::text COLLATE case_insensitive THEN true ELSE false END
+(4 rows)
+
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_insensitive THEN true ELSE false END;
+  x  | count 
+-----+-------
+ abc |     2
+(1 row)
+
+-- Negative: nested CASE with collation conflict
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE WHEN CASE x WHEN 'abc' COLLATE case_sensitive THEN 1 ELSE 0 END = 1 THEN true ELSE false END;
+                                                    QUERY PLAN                                                     
+-------------------------------------------------------------------------------------------------------------------
+ HashAggregate
+   Group Key: x
+   Filter: CASE WHEN (CASE x WHEN 'abc'::text COLLATE case_sensitive THEN 1 ELSE 0 END = 1) THEN true ELSE false END
+   ->  Seq Scan on test3ci
+(4 rows)
+
 -- Positive: deterministic collation in GROUP BY: always safe to push, even if
 -- HAVING uses a nondeterministic collation
 EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index fdcdb2094f..20de47030f 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -801,6 +801,20 @@ SELECT x, count(*) FROM test3ci GROUP BY x HAVING ROW(x, 1) < ROW('ABC' COLLATE
 EXPLAIN (COSTS OFF)
 SELECT x, count(*) FROM test3ci GROUP BY x HAVING current_setting('server_version') = 'abc' COLLATE case_sensitive;
 
+-- Negative: collation conflict hidden inside shorthand CASE
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_sensitive THEN true ELSE false END;
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_sensitive THEN true ELSE false END;
+
+-- Positive: shorthand CASE with matching collation, safe to push
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_insensitive THEN true ELSE false END;
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE x WHEN 'abc' COLLATE case_insensitive THEN true ELSE false END;
+
+-- Negative: nested CASE with collation conflict
+EXPLAIN (COSTS OFF)
+SELECT x, count(*) FROM test3ci GROUP BY x HAVING CASE WHEN CASE x WHEN 'abc' COLLATE case_sensitive THEN 1 ELSE 0 END = 1 THEN true ELSE false END;
+
 -- Positive: deterministic collation in GROUP BY: always safe to push, even if
 -- HAVING uses a nondeterministic collation
 EXPLAIN (COSTS OFF)