This is an automated email from the ASF dual-hosted git repository.

cloud-fan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new 9deb4037705a [SPARK-56898][SQL] Rewrite COUNT(DISTINCT IF) to 
COUNT(DISTINCT) FILTER for Expand reduction
9deb4037705a is described below

commit 9deb4037705a6a6e8d548ee803d323ddf75c693b
Author: James Xu <[email protected]>
AuthorDate: Mon Aug 3 15:23:03 2026 +0800

    [SPARK-56898][SQL] Rewrite COUNT(DISTINCT IF) to COUNT(DISTINCT) FILTER for 
Expand reduction
    
    ### What changes were proposed in this pull request?
    
    Canonicalizes:
    
    ```
      COUNT(DISTINCT IF(cond, base, NULL))
      COUNT(DISTINCT CASE WHEN cond THEN base END)
    ```
    
    into:
    
    ```
      COUNT(DISTINCT base) FILTER (WHERE cond)
    ```
    
    This reduces the number of distinct groups seen by 
RewriteDistinctAggregates from N (one per unique conditional expression) down 
to 1 (all share the same base column), collapsing the Expand factor from Nx to 
1x.
    
    Gated by spark.sql.optimizer.rewriteCountDistinctConditional.enabled 
(default: true).
    
    Includes comprehensive unit tests for rewrite patterns and safety 
boundaries.
    
    ### Why are the changes needed?
    
    When a query contains many COUNT(DISTINCT IF(cond_i, col, NULL)) 
expressions over the same base column, RewriteDistinctAggregates treats each 
unique IF(...) expression as a distinct group. N conditions → N distinct groups 
→ N× Expand amplification. In production workloads with 25–60 such expressions, 
this produces multi-terabyte shuffles and hour-long runtimes.
    
    ```
    SELECT
      user_id,
      COUNT(DISTINCT IF(dt >= '2026-04-16', order_id, NULL)) AS orders_30d,
      COUNT(DISTINCT IF(dt >= '2026-02-15', order_id, NULL)) AS orders_90d,
      COUNT(DISTINCT IF(pay_status = 'paid', order_id, NULL)) AS orders_paid,
      -- ... 50 more expressions
    FROM transactions
    GROUP BY user_id
    ```
    
    ### Does this PR introduce _any_ user-facing change?
    
    No.
    
    ### How was this patch tested?
    
    Unit Test.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    No.
    
    Closes #55925 from xumingming/count-distinct-filter-rewrite.
    
    Authored-by: James Xu <[email protected]>
    Signed-off-by: Wenchen Fan <[email protected]>
---
 .../optimizer/RewriteDistinctAggregates.scala      |  63 ++++-
 .../org/apache/spark/sql/internal/SQLConf.scala    |  18 ++
 .../optimizer/RewriteDistinctAggregatesSuite.scala | 308 ++++++++++++++++++++-
 ...teDistinctAggregatesConditionalQuerySuite.scala | 307 ++++++++++++++++++++
 4 files changed, 691 insertions(+), 5 deletions(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregates.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregates.scala
index 5aef82b64ed3..b33098892746 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregates.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregates.scala
@@ -21,7 +21,8 @@ import org.apache.spark.sql.catalyst.expressions._
 import org.apache.spark.sql.catalyst.expressions.aggregate._
 import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, Expand, 
LogicalPlan}
 import org.apache.spark.sql.catalyst.rules.Rule
-import org.apache.spark.sql.catalyst.trees.TreePattern.AGGREGATE
+import org.apache.spark.sql.catalyst.trees.TreePattern.{AGGREGATE, CASE_WHEN, 
IF}
+import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types.IntegerType
 import org.apache.spark.util.collection.Utils
 
@@ -223,8 +224,9 @@ object RewriteDistinctAggregates extends Rule[LogicalPlan] {
     case a: Aggregate if mayNeedtoRewrite(a) => rewrite(a)
   }
 
-  def rewrite(a: Aggregate): Aggregate = {
+  def rewrite(origAgg: Aggregate): Aggregate = {
 
+    val a = normalizeCountDistinctConditional(origAgg)
     val aggExpressions = collectAggregateExprs(a)
     val distinctAggs = aggExpressions.filter(_.isDistinct)
 
@@ -419,6 +421,63 @@ object RewriteDistinctAggregates extends Rule[LogicalPlan] 
{
     }
   }
 
+  /**
+   * Canonicalizes COUNT(DISTINCT IF(cond, base, NULL)) and
+   * COUNT(DISTINCT CASE WHEN cond THEN base END) to COUNT(DISTINCT base) 
FILTER (WHERE cond).
+   * This reduces the number of distinct groups: multiple conditional counts 
on the same base
+   * column collapse into one group, shrinking the Expand fan-out from Nx to 
1x.
+   *
+   * Note that the rewrite moves `base` out of the protective conditional 
branch: after the
+   * rewrite the Expand operator evaluates the distinct child for every input 
row, while
+   * originally it was only evaluated on rows where `cond` holds. To preserve 
the
+   * short-circuit semantics of IF/CASE WHEN, the rewrite is restricted to base
+   * expressions that can be evaluated unconditionally, i.e. cannot raise 
errors or change
+   * results when evaluated on extra rows (see 
[[ExprUtils.canEvaluateUnconditionally]]).
+   */
+  private def normalizeCountDistinctConditional(a: Aggregate): Aggregate = {
+    if (!SQLConf.get.rewriteCountDistinctConditionalEnabled) return a
+    a.transformExpressionsUpWithPruning(
+      _.containsAnyPattern(IF, CASE_WHEN)) {
+      case ae @ AggregateExpression(count: Count, _, true, None, _)
+          if count.children.size == 1 =>
+        extractCondAndBase(count.children.head) match {
+          case Some((cond, base)) =>
+            ae.copy(
+              aggregateFunction = Count(base),
+              filter = Some(cond))
+          case None => ae
+        }
+    }
+  }
+
+  /**
+   * Matches IF(cond, base, null), CASE WHEN cond THEN base END, and
+   * CASE WHEN cond THEN base ELSE NULL END (including null wrapped in Cast).
+   * Multi-branch CaseWhen is intentionally not rewritten -- Or-flattening is 
out of scope.
+   * The base must be safe to evaluate unconditionally (the rewrite evaluates 
it on rows
+   * where the original branch would not have been taken, see
+   * [[ExprUtils.canEvaluateUnconditionally]]); the condition needs no check 
because it is
+   * evaluated unconditionally as the IF/CASE WHEN predicate anyway.
+   * Returns None for anything else.
+   */
+  private def extractCondAndBase(expr: Expression): Option[(Expression, 
Expression)] =
+    expr match {
+      case If(cond, base, e) if isNullExpr(e) && 
ExprUtils.canEvaluateUnconditionally(base) =>
+        Some((cond, base))
+      case CaseWhen(Seq((cond, base)), None) if 
ExprUtils.canEvaluateUnconditionally(base) =>
+        Some((cond, base))
+      case CaseWhen(Seq((cond, base)), Some(e))
+          if isNullExpr(e) && ExprUtils.canEvaluateUnconditionally(base) =>
+        Some((cond, base))
+      case _ => None
+    }
+
+  private def isNullExpr(e: Expression): Boolean = e match {
+    case Literal(null, _) => true
+    case Cast(child, _, _, _) => isNullExpr(child)
+    case _ => false
+  }
+
   private def collectAggregateExprs(a: Aggregate): Seq[AggregateExpression] = {
     // Collect all aggregate expressions.
     a.aggregateExpressions.flatMap { _.collect {
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 11379a31604f..beb8d5ee1458 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -1601,6 +1601,21 @@ object SQLConf {
       .booleanConf
       .createWithDefault(true)
 
+  val REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED =
+    buildConf("spark.sql.optimizer.rewriteCountDistinctConditional.enabled")
+      .internal()
+      .doc("When true, rewrites COUNT(DISTINCT IF(cond, base, NULL)) and " +
+        "COUNT(DISTINCT CASE WHEN cond THEN base END) into " +
+        "COUNT(DISTINCT base) FILTER (WHERE cond). This reduces the Expand 
factor " +
+        "in RewriteDistinctAggregates from Nx to 1x when multiple conditional 
distinct " +
+        "counts share the same base column. The rewrite is only applied to 
base " +
+        "expressions that are safe to evaluate unconditionally (e.g. plain 
columns), " +
+        "so the short-circuit semantics of IF/CASE WHEN are preserved.")
+      .version("4.3.0")
+      .withBindingPolicy(ConfigBindingPolicy.SESSION)
+      .booleanConf
+      .createWithDefault(true)
+
   val ESCAPED_STRING_LITERALS = 
buildConf("spark.sql.parser.escapedStringLiterals")
     .internal()
     .doc("When true, string literals (including regex patterns) remain escaped 
in our SQL " +
@@ -9314,6 +9329,9 @@ class SQLConf extends Serializable with Logging with 
SqlApiConf {
   def decorrelateInnerQueryEnabledForExistsIn: Boolean =
     
!getConf(SQLConf.DECORRELATE_EXISTS_IN_SUBQUERY_LEGACY_INCORRECT_COUNT_HANDLING_ENABLED)
 
+  def rewriteCountDistinctConditionalEnabled: Boolean =
+    getConf(SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED)
+
   def maxConcurrentOutputFileWriters: Int = 
getConf(SQLConf.MAX_CONCURRENT_OUTPUT_FILE_WRITERS)
 
   def plannedWriteEnabled: Boolean = getConf(SQLConf.PLANNED_WRITE_ENABLED)
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregatesSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregatesSuite.scala
index 08dd4011f04d..b67eb88f0d68 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregatesSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RewriteDistinctAggregatesSuite.scala
@@ -18,11 +18,12 @@ package org.apache.spark.sql.catalyst.optimizer
 
 import org.apache.spark.sql.catalyst.dsl.expressions._
 import org.apache.spark.sql.catalyst.dsl.plans._
-import org.apache.spark.sql.catalyst.expressions.{Literal, Round}
-import org.apache.spark.sql.catalyst.expressions.aggregate.CollectSet
+import org.apache.spark.sql.catalyst.expressions.{AttributeReference, 
CaseWhen, Cast, Coalesce, Expression, GetMapValue, GetStructField, If, Literal, 
Lower, Round}
+import 
org.apache.spark.sql.catalyst.expressions.aggregate.{AggregateExpression, 
CollectSet, Count, Sum}
 import org.apache.spark.sql.catalyst.plans.PlanTest
 import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, Expand, 
LocalRelation, LogicalPlan}
-import org.apache.spark.sql.types.{IntegerType, StringType}
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.types.{IntegerType, MapType, StringType, 
StructField, StructType}
 
 class RewriteDistinctAggregatesSuite extends PlanTest {
   val nullInt = Literal(null, IntegerType)
@@ -125,4 +126,305 @@ class RewriteDistinctAggregatesSuite extends PlanTest {
       fail(s"Plan is not as expected:\n$rewrite")
     }
   }
+
+  // 
---------------------------------------------------------------------------
+  // COUNT(DISTINCT IF/CASE) canonicalization (SPARK-56898)
+  // 
---------------------------------------------------------------------------
+
+  val conditionalTestRelation = LocalRelation(
+    Symbol("a").int, Symbol("b").int, Symbol("c").int, Symbol("d").string)
+
+  private def countDistinctIf(cond: Expression, base: Expression): Expression 
= {
+    Count(If(cond, base, Literal(null))).toAggregateExpression(isDistinct = 
true)
+  }
+
+  private def countDistinctCaseWhen(cond: Expression, base: Expression): 
Expression = {
+    val caseWhen = CaseWhen(
+      Seq((cond, base)),
+      None)
+    Count(caseWhen).toAggregateExpression(isDistinct = true)
+  }
+
+  private def countDistinctCaseWhenElseNull(cond: Expression, base: 
Expression): Expression = {
+    val caseWhen = CaseWhen(
+      Seq((cond, base)),
+      Some(Literal(null)))
+    Count(caseWhen).toAggregateExpression(isDistinct = true)
+  }
+
+  private def collectAggregateExpressions(plan: LogicalPlan): 
Seq[AggregateExpression] = {
+    plan.collect { case a: Aggregate => a.aggregateExpressions }
+      .flatten
+      .flatMap(_.collect { case ae: AggregateExpression => ae })
+  }
+
+  /**
+   * Asserts that the optimized plan has exactly one Expand node with one 
projection,
+   * that the projection contains `baseColName` as a plain attribute, that it
+   * contains no expression of `removedWrapperType` (the IF/CaseWhen that was 
stripped),
+   * and that the outer aggregate has moved the condition into a FILTER clause.
+   */
+  private def assertSingleDistinctGroupExpand(
+      optimized: LogicalPlan,
+      baseColName: String,
+      removedWrapperType: Class[_]): Unit = {
+    val expand = optimized.collectFirst { case e: Expand => e }.get
+    assert(expand.projections.size == 1,
+      s"expected 1 distinct group but got ${expand.projections.size}")
+    val baseAttr = conditionalTestRelation.output.find(_.name == 
baseColName).get
+    assert(expand.projections.head.exists(_.semanticEquals(baseAttr)),
+      s"expected base column $baseColName in Expand projection")
+    assert(!expand.projections.head.exists(e => 
removedWrapperType.isInstance(e)),
+      s"${removedWrapperType.getSimpleName} wrapper should have been removed " 
+
+        "from Expand projection")
+    assert(collectAggregateExpressions(optimized).exists(_.filter.isDefined),
+      "expected at least one AggregateExpression with a FILTER clause")
+  }
+
+  test("conditional: disabled when config is false") {
+    withSQLConf(SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> 
"false") {
+      val input = conditionalTestRelation
+        .groupBy(Symbol("a"))(
+          countDistinctIf(Symbol("b") > 1, Symbol("c")).as("cnt1"),
+          countDistinctIf(Symbol("b") > 2, Symbol("c")).as("cnt2"))
+        .analyze
+      val optimized = RewriteDistinctAggregates(input)
+      // The general RewriteDistinctAggregates still fires, but conditional
+      // canonicalization is disabled so the two conditional counts stay as 2
+      // distinct groups instead of collapsing to 1.
+      val expands = optimized.collect { case e: Expand => e }
+      assert(expands.head.projections.size == 2,
+        "expected 2 distinct groups when conditional canonicalization is 
disabled")
+    }
+  }
+
+  test("conditional: rewrite COUNT(DISTINCT IF(cond, col, NULL)) to 
COUNT(DISTINCT col) FILTER") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        countDistinctIf(Symbol("b") > 1, Symbol("c")).as("cnt1"),
+        countDistinctIf(Symbol("b") > 2, Symbol("c")).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    assertSingleDistinctGroupExpand(optimized, "c", classOf[If])
+  }
+
+  test("conditional: rewrite COUNT(DISTINCT CASE WHEN cond THEN col END) to " +
+      "COUNT(DISTINCT col) FILTER") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        countDistinctCaseWhen(Symbol("b") > 1, Symbol("c")).as("cnt1"),
+        countDistinctCaseWhen(Symbol("b") > 2, Symbol("c")).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    assertSingleDistinctGroupExpand(optimized, "c", classOf[CaseWhen])
+  }
+
+  test("conditional: rewrite COUNT(DISTINCT CASE WHEN cond THEN col ELSE NULL 
END)") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        countDistinctCaseWhenElseNull(Symbol("b") > 1, Symbol("c")).as("cnt1"),
+        countDistinctCaseWhenElseNull(Symbol("b") > 2, Symbol("c")).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    assertSingleDistinctGroupExpand(optimized, "c", classOf[CaseWhen])
+  }
+
+  test("conditional: multiple conditional distinct counts collapse to single 
distinct group") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        countDistinctIf(Symbol("b") > 1, Symbol("c")).as("cnt1"),
+        countDistinctIf(Symbol("b") > 2, Symbol("c")).as("cnt2"),
+        countDistinctIf(Symbol("b") > 3, Symbol("c")).as("cnt3"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    // All three counts share the same base column c, collapsed to 1 distinct 
group.
+    assertSingleDistinctGroupExpand(optimized, "c", classOf[If])
+  }
+
+  test("conditional: single conditional distinct count is gated out by 
mayNeedtoRewrite") {
+    // A lone COUNT(DISTINCT IF(...)) must NOT be pushed onto the Expand path.
+    // The canonicalization runs inside rewrite(), which is only called when
+    // mayNeedtoRewrite returns true. A single conditional distinct count has 
no filter
+    // and forms only one distinct group, so mayNeedtoRewrite returns false, 
rewrite()
+    // is never called, and no Expand is produced.
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        countDistinctIf(Symbol("b") > 1, Symbol("c")).as("cnt1"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    val expands = optimized.collect { case e: Expand => e }
+    assert(expands.isEmpty, "single conditional distinct count should not 
produce an Expand")
+  }
+
+  test("conditional: do not rewrite IF with non-null else branch") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        Count(If(Symbol("b") > 1, Symbol("c"), Literal(0, IntegerType)))
+          .toAggregateExpression(isDistinct = true)
+          .as("cnt1"),
+        Count(If(Symbol("b") > 2, Symbol("c"), Literal(0, IntegerType)))
+          .toAggregateExpression(isDistinct = true)
+          .as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    // Plan is rewritten (2 distinct groups) but not canonicalized
+    checkRewrite(optimized)
+    val expands = optimized.collect { case e: Expand => e }
+    assert(expands.head.projections.size == 2,
+      "non-null else branch should not be collapsed to 1 distinct group")
+  }
+
+  test("conditional: do not rewrite non-distinct COUNT") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        Count(If(Symbol("b") > 1, Symbol("c"), Literal(null, IntegerType)))
+          .toAggregateExpression(isDistinct = false)
+          .as("cnt1"),
+        countDistinct(Symbol("c")).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    // Still a single distinct group (cnt2), no canonicalization of the 
non-distinct agg
+    comparePlans(optimized, input)
+  }
+
+  test("conditional: do not rewrite when FILTER already exists") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        Count(If(Symbol("b") > 1, Symbol("c"), Literal(null, IntegerType)))
+          .toAggregateExpression(isDistinct = true, filter = Some(Symbol("d") 
=== "x"))
+          .as("cnt1"),
+        Count(If(Symbol("b") > 2, Symbol("c"), Literal(null, IntegerType)))
+          .toAggregateExpression(isDistinct = true, filter = Some(Symbol("d") 
=== "y"))
+          .as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    checkRewrite(optimized)
+    val expands = optimized.collect { case e: Expand => e }
+    // 2 groups because existing FILTERs prevent canonicalization
+    assert(expands.head.projections.size == 2)
+  }
+
+  test("conditional: do not rewrite multi-branch CASE WHEN") {
+    val caseWhen1 = new CaseWhen(
+      Seq(
+        (Symbol("b") > Literal(1), Symbol("c")),
+        (Symbol("b") > Literal(2), Symbol("a"))),
+      Some(Literal(null)))
+    val caseWhen2 = new CaseWhen(
+      Seq(
+        (Symbol("b") > Literal(3), Symbol("c")),
+        (Symbol("b") > Literal(4), Symbol("a"))),
+      Some(Literal(null)))
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        Count(caseWhen1).toAggregateExpression(isDistinct = true).as("cnt1"),
+        Count(caseWhen2).toAggregateExpression(isDistinct = true).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    checkRewrite(optimized)
+    val expands = optimized.collect { case e: Expand => e }
+    // 2 groups - multi-branch CASE was not canonicalized
+    assert(expands.head.projections.size == 2)
+  }
+
+  test("conditional: do not rewrite SUM(DISTINCT IF(...))") {
+    val input = conditionalTestRelation
+      .groupBy(Symbol("a"))(
+        Sum(If(Symbol("b") > 1, Symbol("c"), Literal(null, IntegerType)))
+          .toAggregateExpression(isDistinct = true)
+          .as("sum1"),
+        Sum(If(Symbol("b") > 2, Symbol("c"), Literal(null, IntegerType)))
+          .toAggregateExpression(isDistinct = true)
+          .as("sum2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    checkRewrite(optimized)
+    val expands = optimized.collect { case e: Expand => e }
+    // 2 groups - SUM(DISTINCT IF) is not canonicalized
+    assert(expands.head.projections.size == 2)
+  }
+
+  /**
+   * Asserts that two conditional distinct counts over `base` collapse into a 
single
+   * distinct group with a FILTER clause, i.e. the conditional 
canonicalization fired.
+   */
+  private def assertCanonicalized(relation: LocalRelation, base: Expression): 
Unit = {
+    val input = relation
+      .groupBy(Symbol("a"))(
+        countDistinctIf(Symbol("b") > 1, base).as("cnt1"),
+        countDistinctIf(Symbol("b") > 2, base).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    val expand = optimized.collectFirst { case e: Expand => e }.get
+    assert(expand.projections.size == 1,
+      s"branch-safe base should collapse to 1 distinct group: $base")
+    // The Expand rewrite adds gid-routing filters to every distinct 
aggregate; the
+    // canonicalization additionally introduces filters referencing the user 
condition.
+    val hasUserConditionFilter = collectAggregateExpressions(optimized)
+      .flatMap(_.filter)
+      .exists(_.references.exists(_.name != "gid"))
+    assert(hasUserConditionFilter,
+      s"expected a user-condition FILTER clause after canonicalizing base: 
$base")
+  }
+
+  /**
+   * Asserts that two conditional distinct counts over `base` are rewritten by 
the
+   * general Expand path but are NOT canonicalized, i.e. they stay 2 distinct 
groups
+   * and no FILTER clause is introduced.
+   */
+  private def assertNotCanonicalized(relation: LocalRelation, base: 
Expression): Unit = {
+    val input = relation
+      .groupBy(Symbol("a"))(
+        countDistinctIf(Symbol("b") > 1, base).as("cnt1"),
+        countDistinctIf(Symbol("b") > 2, base).as("cnt2"))
+      .analyze
+    val optimized = RewriteDistinctAggregates(input)
+    checkRewrite(optimized)
+    val expand = optimized.collectFirst { case e: Expand => e }.get
+    assert(expand.projections.size == 2,
+      s"non-branch-safe base should stay 2 distinct groups: $base")
+    // The Expand rewrite adds gid-routing filters to every distinct 
aggregate; a
+    // user-condition filter introduced by the canonicalization would 
reference the
+    // condition's columns instead of only gid.
+    val hasUserConditionFilter = collectAggregateExpressions(optimized)
+      .flatMap(_.filter)
+      .exists(_.references.exists(_.name != "gid"))
+    assert(!hasUserConditionFilter,
+      s"non-branch-safe base should not produce a user-condition FILTER 
clause: $base")
+  }
+
+  test("conditional: do not rewrite when base is not branch-safe") {
+    val unsafeBases = Seq(
+      // Division may throw (e.g. divide-by-zero under ANSI mode) on rows 
where the
+      // original IF branch is not taken.
+      Symbol("c") / Symbol("b"),
+      // Cast may throw under ANSI mode.
+      Cast(Symbol("c"), StringType),
+      // String functions are not in the branch-safe whitelist.
+      Lower(Symbol("d")))
+    unsafeBases.foreach { base =>
+      assertNotCanonicalized(conditionalTestRelation, base)
+    }
+  }
+
+  test("conditional: rewrite branch-safe expression bases") {
+    // Predicate and null-handling bases on the flat test relation.
+    Seq(
+      Symbol("b") > Symbol("c"),
+      Coalesce(Seq(Symbol("b"), Literal(0, IntegerType))))
+      .foreach { base =>
+        assertCanonicalized(conditionalTestRelation, base)
+      }
+
+    // Total accessor bases need struct/map attributes.
+    val structAttr = AttributeReference("s", StructType(Seq(StructField("f", 
IntegerType))))()
+    val mapAttr = AttributeReference("m", MapType(IntegerType, StringType))()
+    val richRelation = LocalRelation(Symbol("a").int, Symbol("b").int, 
structAttr, mapAttr)
+    Seq(
+      GetStructField(structAttr, 0),
+      GetMapValue(mapAttr, Literal(1, IntegerType)))
+      .foreach { base =>
+        assertCanonicalized(richRelation, base)
+      }
+  }
 }
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/RewriteDistinctAggregatesConditionalQuerySuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/RewriteDistinctAggregatesConditionalQuerySuite.scala
new file mode 100644
index 000000000000..f99429cd1649
--- /dev/null
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/RewriteDistinctAggregatesConditionalQuerySuite.scala
@@ -0,0 +1,307 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *    http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql
+
+import org.apache.spark.sql.catalyst.expressions.aggregate.AggregateExpression
+import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, LogicalPlan}
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.test.SharedSparkSession
+
+class RewriteDistinctAggregatesConditionalQuerySuite extends QueryTest with 
SharedSparkSession {
+
+  private def checkRewriteAndResult(
+      conditionalSql: String,
+      filterSql: String): Unit = {
+    val expectedRows = spark.sql(filterSql).collect()
+
+    // Verify the rewrite produces the same result as the explicit FILTER form.
+    withSQLConf(
+      SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "true") {
+      checkAnswer(spark.sql(conditionalSql), expectedRows)
+    }
+
+    // Verify the non-rewritten form also matches the explicit FILTER form.
+    withSQLConf(
+      SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "false") {
+      checkAnswer(spark.sql(conditionalSql), expectedRows)
+    }
+  }
+
+  private def hasUserConditionFilter(plan: LogicalPlan): Boolean = {
+    val aggregateExpressions = plan.collect {
+      case a: Aggregate => a.aggregateExpressions
+    }.flatten
+    val aggregateExprs = aggregateExpressions.flatMap {
+      _.collect { case ae: AggregateExpression => ae }
+    }
+    // The Expand rewrite adds gid-routing filters to every distinct 
aggregate; we only
+    // care about user-condition filters introduced by the IF/CASE 
canonicalization.
+    aggregateExprs.flatMap(_.filter).exists(_.references.exists(_.name != 
"gid"))
+  }
+
+  test("rewrite COUNT(DISTINCT IF(cond, col, NULL)) correctness") {
+    withTempView("t") {
+      spark.range(7)
+        .selectExpr(
+          "cast(id % 3 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "case when id % 4 = 0 then null else cast(id * 100 as int) end as 
col2")
+        .createOrReplaceTempView("t")
+
+      // Two conditional distinct counts on the same base so the rewrite fires 
(size > 1).
+      checkRewriteAndResult(
+        """SELECT key,
+          |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)),
+          |  COUNT(DISTINCT IF(col1 > 20, col2, NULL))
+          |FROM t GROUP BY key""".stripMargin,
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t GROUP BY key""".stripMargin)
+    }
+  }
+
+  test("rewrite COUNT(DISTINCT CASE WHEN cond THEN col END) correctness") {
+    withTempView("t") {
+      spark.range(7)
+        .selectExpr(
+          "cast(id % 3 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "case when id % 4 = 0 then null else cast(id * 100 as string) end as 
col2")
+        .createOrReplaceTempView("t")
+
+      // Two CASE WHEN conditional distinct counts on the same base so the 
rewrite fires.
+      checkRewriteAndResult(
+        """SELECT key,
+          |  COUNT(DISTINCT CASE WHEN col1 > 10 THEN col2 END),
+          |  COUNT(DISTINCT CASE WHEN col1 > 20 THEN col2 END)
+          |FROM t GROUP BY key""".stripMargin,
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t GROUP BY key""".stripMargin)
+    }
+  }
+
+  test("rewrite COUNT(DISTINCT CASE WHEN cond THEN col ELSE NULL END) 
correctness") {
+    withTempView("t") {
+      spark.range(6)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "case when id % 4 = 0 then null else cast(id * 1.0 as double) end as 
col2")
+        .createOrReplaceTempView("t")
+
+      // Two CASE WHEN ... ELSE NULL counts on the same base so the rewrite 
fires.
+      checkRewriteAndResult(
+        """SELECT key,
+          |  COUNT(DISTINCT CASE WHEN col1 > 10 THEN col2 ELSE NULL END),
+          |  COUNT(DISTINCT CASE WHEN col1 > 20 THEN col2 ELSE NULL END)
+          |FROM t GROUP BY key""".stripMargin,
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t GROUP BY key""".stripMargin)
+    }
+  }
+
+  test("rewrite with no GROUP BY") {
+    withTempView("t") {
+      spark.range(5)
+        .selectExpr(
+          "cast(id * 10 as int) as col1",
+          "case when id % 3 = 0 then null else cast(id * 100 as int) end as 
col2")
+        .createOrReplaceTempView("t")
+
+      // Two counts so the rewrite fires without GROUP BY.
+      checkRewriteAndResult(
+        """SELECT
+          |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)),
+          |  COUNT(DISTINCT IF(col1 > 20, col2, NULL))
+          |FROM t""".stripMargin,
+        """SELECT
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t""".stripMargin)
+    }
+  }
+
+  test("rewrite with all NULLs in conditional branch") {
+    withTempView("t") {
+      spark.range(3)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id * 5 as int) as col1",
+          "cast(id * 100 as int) as col2")
+        .createOrReplaceTempView("t")
+
+      // col1 values are 0, 5, 10 - both thresholds (> 10 and > 20) yield zero 
matches,
+      // so both counts return 0. Two counts so the rewrite fires.
+      checkRewriteAndResult(
+        """SELECT key,
+          |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)),
+          |  COUNT(DISTINCT IF(col1 > 20, col2, NULL))
+          |FROM t GROUP BY key""".stripMargin,
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t GROUP BY key""".stripMargin)
+    }
+  }
+
+  test("rewrite with duplicates in base column") {
+    withTempView("t") {
+      spark.range(6)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "case when id % 3 = 0 then 100 when id % 3 = 1 then 100 else 200 end 
as col2")
+        .createOrReplaceTempView("t")
+
+      // Two counts on the same base (with duplicates) so the rewrite fires.
+      checkRewriteAndResult(
+        """SELECT key,
+          |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)),
+          |  COUNT(DISTINCT IF(col1 > 20, col2, NULL))
+          |FROM t GROUP BY key""".stripMargin,
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10),
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 20)
+          |FROM t GROUP BY key""".stripMargin)
+    }
+  }
+
+  test("multiple conditional distinct counts collapse and produce correct 
results") {
+    withTempView("t") {
+      spark.range(5)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "case when id % 3 = 0 then null else cast(id * 100 as int) end as 
col2",
+          "case when id % 4 = 0 then null else cast(id * 10 as string) end as 
col3")
+        .createOrReplaceTempView("t")
+
+      val conditionalSql =
+        """SELECT key,
+          |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)) as cnt1,
+          |  COUNT(DISTINCT IF(col1 > 5, col3, NULL)) as cnt2
+          |FROM t GROUP BY key""".stripMargin
+
+      val filterSql =
+        """SELECT key,
+          |  COUNT(DISTINCT col2) FILTER (WHERE col1 > 10) as cnt1,
+          |  COUNT(DISTINCT col3) FILTER (WHERE col1 > 5) as cnt2
+          |FROM t GROUP BY key""".stripMargin
+
+      checkRewriteAndResult(conditionalSql, filterSql)
+    }
+  }
+
+  test("rewrite does not affect COUNT(DISTINCT IF(cond, col, non_null))") {
+    withTempView("t") {
+      spark.range(3)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "cast(id * 100 as int) as col2")
+        .createOrReplaceTempView("t")
+
+      // Two counts so mayNeedtoRewrite fires and the non-null-else guard is 
exercised.
+      val sqlText =
+        """SELECT key,
+          |  COUNT(DISTINCT IF(col1 > 10, col2, 0)),
+          |  COUNT(DISTINCT IF(col1 > 20, col2, 0))
+          |FROM t GROUP BY key""".stripMargin
+
+      // Collect the conf-off result as the semantic baseline; the conf-on 
query must match it.
+      val withoutRewriteRows = withSQLConf(
+        SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "false") {
+        spark.sql(sqlText).collect()
+      }
+      withSQLConf(
+        SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "true") {
+        checkAnswer(spark.sql(sqlText), withoutRewriteRows)
+      }
+
+      // When the switch is enabled, the non-null ELSE branch must not be 
canonicalized
+      // into a user-condition FILTER; only the internal gid-routing filters 
introduced
+      // by the Expand rewrite may be present.
+      val hasFilter = withSQLConf(
+        SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "true") {
+        hasUserConditionFilter(spark.sql(sqlText).queryExecution.optimizedPlan)
+      }
+      assert(!hasFilter,
+        "Non-null ELSE branch should not produce a user-condition FILTER 
clause")
+    }
+  }
+
+  test("rewrite is not applied to error-prone base expressions (ANSI divide by 
zero)") {
+    withTempView("t") {
+      // Rows with x = 0 make both conditions false, so the IF branches 
short-circuit
+      // and 10 / x is never evaluated for them.
+      spark.range(5)
+        .selectExpr(
+          "cast(id % 2 + 1 as int) as key",
+          "cast(id % 2 as int) as x")
+        .createOrReplaceTempView("t")
+
+      // Two conditional distinct counts on the same error-prone base so that 
rewrite()
+      // is reached; the canonicalization must NOT fire for such a base.
+      val sqlText =
+        """SELECT key,
+          |  COUNT(DISTINCT IF(x <> 0, 10 / x, NULL)),
+          |  COUNT(DISTINCT IF(x > 0, 10 / x, NULL))
+          |FROM t GROUP BY key""".stripMargin
+
+      withSQLConf(
+        SQLConf.ANSI_ENABLED.key -> "true",
+        SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "true") {
+        // Must not raise DIVIDE_BY_ZERO: the canonicalization must leave the 
IFs intact
+        // so that their branch short-circuiting is preserved.
+        checkAnswer(spark.sql(sqlText), Seq(Row(1, 0L, 0L), Row(2, 1L, 1L)))
+        
assert(!hasUserConditionFilter(spark.sql(sqlText).queryExecution.optimizedPlan),
+          "error-prone base should not produce a user-condition FILTER clause")
+      }
+    }
+  }
+
+  test("rewrite is present in optimized plan") {
+    withTempView("t") {
+      spark.range(2)
+        .selectExpr(
+          "cast(id + 1 as int) as key",
+          "cast(id * 10 as int) as col1",
+          "cast(id * 100 as int) as col2")
+        .createOrReplaceTempView("t")
+
+      // Two conditional distinct counts on the same base column trigger 
canonicalization,
+      // so the optimized plan must contain user-condition FILTER clauses.
+      val hasFilter = withSQLConf(
+        SQLConf.REWRITE_COUNT_DISTINCT_CONDITIONAL_ENABLED.key -> "true") {
+        val df = spark.sql(
+          """SELECT key,
+            |  COUNT(DISTINCT IF(col1 > 10, col2, NULL)) as cnt1,
+            |  COUNT(DISTINCT IF(col1 > 5, col2, NULL)) as cnt2
+            |FROM t GROUP BY key""".stripMargin)
+        hasUserConditionFilter(df.queryExecution.optimizedPlan)
+      }
+
+      assert(hasFilter, "Optimized plan should contain user-condition FILTER 
clause")
+    }
+  }
+}


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to