OptimizeExpand inserts a de-duplicating pre-aggregate under a non-distinct COUNT, returning the distinct-row count

XMLWordPrintableJSON

      This is a report of a correctness bug in the OptimizeExpand rule introduced by SPARK-56315 (released in 4.2.0). The rule is gated by the internal conf spark.sql.optimizer.optimizeExpandRatio (default -1 = disabled), so hitting it requires explicit opt-in.

       


       
      Background:

      • queries with multiple COUNT(DISTINCT) on different columns are rewritten by RewriteDistinctAggregates into an Expand that duplicates each input row once per distinct group. 
      • OptimizeExpand reduces that amplification by inserting a de-duplicating Aggregate on (grouping keys + distinct columns) beneath the Expand.
      • The de-duplication is sound only for pure distinct aggregates (collapsing duplicate rows changes the value of any duplicate-sensitive aggregate computed alongside) and the conf's own doc states exactly that precondition: "Only applies to pure distinct aggregates without non-distinct aggregates or FILTER clauses."

       
      The bug: the guard implementing that precondition tests attributes, not aggregate functions (OptimizeExpand.scala):

      val innerGroupByAttrs = AttributeSet(innerAgg.groupingExpressions.flatMap(_.references))
      if (!expand.producedAttributes.subsetOf(innerGroupByAttrs)) return false 

       
      The reasoning is that a non-distinct aggregate would force an Expand output column outside the inner GROUP BY. That only detects aggregates to which RewriteDistinctAggregates assigns a dedicated Expand slot.

      Any aggregate that reaches the inner Aggregate as count(1) — COUNT(1), COUNT, and COUNT(col) of a non-nullable column (which the rewrite normalizes to count(1)) — references no attribute at all and always passes the guard:

      SQL shape after RewriteDistinctAggregates dedicated Expand slot? guard result
      COUNT(1) / COUNT count(1) no passes wrong
      COUNT(a), a non-nullable normalized to count(1) no passes wrong
      COUNT(a), a nullable count(a) over its own slot yes rejects correct
      SUM(a) sum(a) over its own slot yes rejects correct

       

      Bug repro:

      CREATE TABLE oe USING parquet AS SELECT * FROM VALUES (1,5,7),(1,5,7),(1,5,7),(1,6,8),(2,9,9) AS t(k,a,b);
      
      SET spark.sql.optimizer.optimizeExpandRatio=2;
      
      SELECT k, COUNT(DISTINCT a), COUNT(DISTINCT b), COUNT(1) FROM oe GROUP BY k ORDER BY k;
      -- outputs: [1,2,2,2], [2,1,1,1]   WRONG (expected [1,2,2,4]: k=1 has four rows) 

      k=1 has four rows (three copies of (1,5,7) plus (1,6,8)), so COUNT(1) must be 4; the returned 2 is the number of distinct (k,a,b) rows. 

      COUNT behaves identically. With the conf unset (or the rule excluded) the answer is correct.

       
      The optimized plan shows the inserted pre-aggregate (last Aggregate [k, a, b]) feeding the Expand whose downstream count(1) was supposed to count base rows:

      Aggregate [k], [k, count(a) FILTER (gid=1), count(b) FILTER (gid=2),
                      coalesce(first(count(1)) FILTER (gid=0), 0)]
      +- Aggregate [k, a, b, gid], [k, a, b, gid, count(1)]
         +- Expand [[k,null,null,0], [k,a,null,1], [k,null,b,2]], [k, a, b, gid]
            +- Aggregate [k, a, b], [k, a, b]          <- inserted by OptimizeExpand
               +- Relation oe[k,a,b] parquet 

      With the rule excluded, the Expand's child is the relation itself and count(1) is 4.
       

      To fix this: check the inner aggregate's aggregate expressions rather than its grouping references — reject any AggregateExpression that is non-distinct and not duplicate-agnostic (cf. EliminateDistinct.isDuplicateAgnostic, the check RemoveRedundantAggregates uses for the same soundness question).

            Assignee:
            Unassigned
            Reporter:
            Josh Rosen
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: