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

asf-gitbox-commits 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 4eecc14b1a6c [SPARK-60150][SQL] Normalize float grouping keys in 
filters pushed below Aggregate
4eecc14b1a6c is described below

commit 4eecc14b1a6cd872dd819cb1bf5ff73494f1b6aa
Author: Cole Agard <[email protected]>
AuthorDate: Fri Oct 30 17:22:30 2026 -0700

    [SPARK-60150][SQL] Normalize float grouping keys in filters pushed below 
Aggregate
    
    ### What changes were proposed in this pull request?
    
    `PushPredicateThroughNonJoin` pushes a filter on grouping keys below an 
`Aggregate`. This PR makes the pushed filter read the *normalized* float/double 
grouping keys wherever the raw value could change its result. It then evaluates 
below the `Aggregate` exactly as it would above it.
    
    Float/double grouping keys are normalized during physical planning 
(`SparkStrategies`): `-0.0` becomes `0.0` and NaNs are canonicalized. The 
normalized key keeps the original `ExprId`, so everything above the `Aggregate` 
sees the normalized value, while a filter pushed below it sees the raw value.
    
    The change, in the `Aggregate` case of `PushPredicateThroughNonJoin`:
    - A new helper, `pushBelowAggregate`, splits the filter's conjuncts into 
the ones pushed below the `Aggregate` and the ones kept above it (the existing 
check that a conjunct only reads grouping keys moved into it), expands aliases 
as before, and rewrites each pushed conjunct with 
`normalizeGroupingExpressions`. It skips the rewrite when there are no 
float/double keys.
    - `normalizeGroupingExpressions` is a single rewrite. It replaces each 
occurrence of a float/double grouping expression with 
`NormalizeFloatingNumbers.normalize(groupingExpr)`, the same normalization the 
planner applies to the grouping keys, which also covers struct and array keys. 
There are two exceptions:
      - A float/double attribute that is a direct operand of `EqualTo`, 
`EqualNullSafe`, `LessThan`, `LessThanOrEqual`, `GreaterThan`, 
`GreaterThanOrEqual`, `In`, `InSet`, `InSubquery`, `IsNull`, `IsNotNull` or 
`IsNaN` is left as is. These already treat `-0.0`/`0.0` and all NaNs as equal 
(`SQLOrderingUtil.compareDoubles`, `CodegenContext.genEqual`; an IN subquery 
becomes a join on `EqualTo` conditions, whose keys are normalized on both sides 
when it is an equi-join), so they give the same [...]
      - A float attribute widened to double (`cast(f AS DOUBLE)`, as when a 
FLOAT key is compared with a DOUBLE or DECIMAL value) is left as is too, since 
widening keeps `-0.0` and NaN. Other casts, such as to STRING, are normalized.
      - A key that an earlier push already normalized (a filter pushed through 
stacked aggregates on the same key) is not normalized again. Any other 
normalized subtree is rewritten too, so a lower key inside a normalized upper 
key computed from it (e.g. `pow(x, -1) AS k ... GROUP BY k`) is normalized.
    
    For example, `x > 5.0 OR cast(x AS STRING) = '-0.0'` is pushed as `x > 5.0 
OR cast(norm(x) AS STRING) = '-0.0'`, and `x = 0.0` is pushed unchanged.
    
    `PushdownPredicatesAndPruneColumnsForCTEDef.removePushedDownFilter` mirrors 
this rule's translation through an `Aggregate` so it can find previously pushed 
filters. It now uses the same `pushBelowAggregate` split.
    
    The pushed filter reads normalized keys because the `Aggregate` outputs 
them normalized. `NormalizeFloatingNumbers` now states when that holds, and the 
rules that could break it respect it:
    - `outputsNormalizedKeys(aggregate)`: true unless all of the aggregate's 
functions are grouped-agg pandas UDFs, which are planned as 
`ArrowAggregatePythonExec` on the raw keys.
    - `mayChangeKeyForm(aggregate)`: true for an aggregate with any grouped-agg 
pandas UDF, whose key form is only settled once `ColumnPruning` has decided 
which of its aggregate functions to keep. `pushBelowAggregate` pushes below it 
only the conjuncts that normalizing leaves unchanged and keeps the others above 
it.
    - `readsNormalizedKeys(aggregate, outputs)`: whether a `Project` of those 
outputs over the aggregate's child would read a float/double key raw that the 
aggregate outputs normalized. `OptimizeOneRowPlan` and 
`RemoveRedundantAggregates`' group-only case no longer replace the `Aggregate` 
in that case, and `LimitPushDown` keeps it under `LIMIT 1` while still pushing 
`LocalLimit(1)` below it. (Normalizing the keys in the `Project` instead is not 
possible while keeping the outputs' `ExprId` [...]
    - `RemoveRedundantAggregates` keeps a lower `Aggregate` under a 
duplicate-insensitive upper one only if the upper one would read its 
float/double key raw: through a lower output computed from the key, inside an 
aggregate function, or in a computed grouping expression. A key the upper one 
groups by directly is normalized by it, so the lower one is still removed.
    - Streaming aggregates are left as before: the rules above still remove 
them, and conjuncts are pushed below them unnormalized (a normalized conjunct 
would disagree with a streaming aggregate those rules replace with a raw-key 
`Project`). Keeping an aggregate, or a filter between two aggregates, would add 
a stateful operator, which a query restarted from an existing checkpoint 
rejects (`STREAMING_STATEFUL_OPERATOR_NOT_MATCH_IN_STATE_METADATA`).
    - The equi-join and single-column null-aware anti join cases of 
`NormalizeFloatingNumbers` now normalize the join keys when either side needs 
it, not only the left side. An `IN`/`NOT IN` subquery over a pushed, normalized 
grouping key becomes a semi/anti join whose left key is already normalized, and 
the subquery side was left raw.
    
    Example:
    ```
    -- SELECT * FROM (SELECT DISTINCT x FROM t) WHERE cast(x AS STRING) = '-0.0'
    Aggregate [x], [x]
    +- Filter (cast(knownfloatingpointnormalized(normalizenanandzero(x)) as 
string) = -0.0)
       +- t
    ```
    
    ### Why are the changes needed?
    
    The rule assumes a grouping key has the same value above and below the 
`Aggregate`, and normalization breaks that assumption for float/double keys. A 
pushed filter can then return a row for which the predicate is false:
    
    ```python
    spark.createDataFrame([(-0.0,)], "x double").createOrReplaceTempView("t")
    
    spark.sql("SELECT x, cast(x AS STRING) = '-0.0' AS p FROM (SELECT DISTINCT 
x FROM t)").collect()
    # [Row(x=0.0, p=False)]
    
    spark.sql("SELECT * FROM (SELECT DISTINCT x FROM t) WHERE cast(x AS STRING) 
= '-0.0'").collect()
    # expected: []
    # before:   [Row(x=0.0)]
    ```
    
    The same happens with `GROUP BY`, `INTERSECT ALL`, `EXCEPT`/`subtract` and 
`dropDuplicates`. Reading the raw key below the aggregate can also split a 
group and change its aggregates:
    
    ```sql
    -- t.x = [-0.0, 0.0]
    SELECT x, count(*) FROM t GROUP BY x HAVING cast(x AS STRING) = '0.0'
    -- expected: (0.0, 2)
    -- before:   (0.0, 1)
    ```
    
    The alternatives were weaker:
    - Keeping such conjuncts above the `Aggregate` is correct, but it gives up 
early filtering.
    - Never pushing filters on float/double keys would also block plain 
comparisons like `x > 5` from reaching scans.
    
    This is the same assumption as in SPARK-60093 
(`RemoveRedundantAggregates`), in a different rule. Because a pushed, 
normalized filter combined with that rule also returned rows that fail the 
predicate, this PR keeps the lower `Aggregate` when the upper one would read 
its normalized keys raw, which also fixes the SPARK-60093 repro for batch 
queries.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, it fixes correctness bugs. Queries that filter an aggregate's 
float/double grouping key with a predicate that can tell `-0.0` from `0.0`, or 
one NaN from another, now return results consistent with the predicate. Such 
predicates are still pushed below the aggregate, now reading the normalized 
key. Other filters are pushed exactly as before.
    
    A `DISTINCT`/group-only aggregate on a float/double key over at most one 
row or under `LIMIT 1`, or a `DISTINCT` under a duplicate-insensitive aggregate 
that reads its key raw, is now kept rather than removed, so it returns the 
normalized key like any other aggregate (e.g. `0.0` instead of `-0.0`). 
`IN`/`NOT IN` subqueries on float/double keys normalize both sides of the join. 
Streaming queries are unchanged.
    
    ### How was this patch tested?
    
    New tests:
    - `FilterPushdownSuite`:
      - the ticket's filter is pushed with the normalized key;
      - only keys that can change the result are normalized: within an `OR` and 
across `AND` conjuncts, a key under a `Cast` is normalized while a direct 
comparison operand is not;
      - aliases computed from a key are normalized after alias expansion, while 
a plain rename is pushed unchanged;
      - comparisons on double/float keys are pushed unchanged, including a 
FLOAT key compared with DOUBLE values, `InSet` and an IN subquery;
      - a grouping expression (`GROUP BY x * 2.0`) and a struct key are 
normalized;
      - a filter pushed through two aggregates is not normalized twice, and a 
lower key inside a computed upper key is normalized;
      - a normalization-sensitive conjunct stays above an aggregate with a 
grouped-agg pandas UDF (all-pandas and mixed), while a plain comparison is 
pushed below it;
      - a filter is pushed below a streaming aggregate unnormalized, as before;
      - unit tests for `normalizeGroupingExpressions`, including matching 
grouping expressions by expression id rather than name.
    - `PredicateSuite`: every comparison that `normalizeGroupingExpressions` 
leaves unnormalized, including `InSet` and a FLOAT value widened to DOUBLE, 
gives the same result for a value and its normalized form, for `-0.0`, `0.0`, 
the canonical NaN, a NaN with a different bit pattern, `1.0` and `null`. The 
values come from the input row, so generated code sees their raw bits.
    - `NormalizeFloatingPointNumbersSuite`: join and null-aware anti join keys 
are normalized when only one side is already normalized.
    - `OptimizeOneRowPlanSuite`, `RemoveRedundantAggregatesSuite`, 
`LimitPushdownSuite`: aggregates that output normalized float/double keys are 
kept (with `LocalLimit(1)` still pushed below under `LIMIT 1`), and still 
removed when those keys are not read; a lower aggregate is removed when the 
upper one groups by its key directly and kept when the upper one reads the key 
raw; streaming aggregates are removed as before. Two existing 
`RemoveRedundantAggregatesSuite` tests used a double key  [...]
    - `PushdownPredicatesForCTEDefStalenessSuite`: a previously pushed, 
normalized filter, and one kept above a pandas UDF aggregate, are removed on 
rebuild. The first fails without the `removePushedDownFilter` change.
    - `DataFrameAggregateSuite` end-to-end tests:
      - `distinct`, `groupBy().count()`, `dropDuplicates`, `intersectAll`, 
`except`, a float key, a struct key, the ticket's SQL and an aliased key return 
the correct rows, and the split-group `count` case returns `(0.0, 2)`;
      - `IN` and `NOT IN` on a double key and on a struct key, with `0.0` vs 
`-0.0` across the subquery (the struct key is pushed normalized, so it needs 
the join-key change on both join paths);
      - one-row, `LIMIT 1`, `limit(1)` and `first()` over a group-only 
aggregate, and a `DISTINCT` under a duplicate-insensitive aggregate (including 
the SPARK-60093 repro, and the removed case with a single remaining aggregate), 
filtered with a sign-sensitive predicate;
      - a filter pushed through two aggregates with a computed upper key;
      - a comparison on a FLOAT key against a DOUBLE value is pushed to the 
Parquet scan.
    - `PythonUDFSuite`: a sign-sensitive filter above a grouped-agg pandas UDF 
aggregate, including a mixed aggregate whose SQL aggregate is pruned and an 
all-pandas one whose pandas output is pruned.
    - `StreamingAggregationSuite`: a streaming DISTINCT under a 
duplicate-insensitive aggregation, with and without a sign-sensitive filter, 
keeps one stateful operator. A query restarted from a checkpoint written with 
the previous plan was also checked by hand.
    
    Each new end-to-end test fails without its fix.
    
    Existing suites (all pass; Python tests with Python 3.11, pandas and 
pyarrow):
    - `build/sbt 'catalyst/test'`
    - `build/sbt 'sql/testOnly *SQLQueryTestSuite *DataFrameAggregateSuite 
*DataFrameSetOperationsSuite *DataFrameSuite *SubquerySuite *JoinSuite 
*SQLQuerySuite *DataFrameWindowFunctionsSuite *RemoveRedundantSortsSuite 
*ParquetV1FilterSuite *ParquetV2FilterSuite *PythonUDFSuite 
*StreamingAggregationSuite *StreamingDeduplicationSuite 
*MultiStatefulOperatorsSuite *CTEInlineSuiteAEOff *CTEInlineSuiteAEOn'`
    - `build/sbt 'sql/testOnly *PlanStabilitySuite'`. No golden plan changes.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Code (Claude Opus 5.5)
    
    🤖 Generated with [Claude Code](https://claude.com/claude-code)
    
    Closes #59403 from ctagard/SPARK-60150.
    
    Authored-by: Cole Agard <[email protected]>
    Signed-off-by: Holden Karau <[email protected]>
---
 .../optimizer/NormalizeFloatingNumbers.scala       |  77 ++++++-
 .../catalyst/optimizer/OptimizeOneRowPlan.scala    |   6 +-
 .../spark/sql/catalyst/optimizer/Optimizer.scala   | 121 +++++++++--
 ...ushdownPredicatesAndPruneColumnsForCTEDef.scala |  26 ++-
 .../optimizer/RemoveRedundantAggregates.scala      |  29 ++-
 .../sql/catalyst/expressions/PredicateSuite.scala  |  57 +++++
 .../catalyst/optimizer/FilterPushdownSuite.scala   | 231 +++++++++++++++++++++
 .../catalyst/optimizer/LimitPushdownSuite.scala    |  18 ++
 .../NormalizeFloatingPointNumbersSuite.scala       |  24 +++
 .../optimizer/OptimizeOneRowPlanSuite.scala        |  13 ++
 ...PushdownPredicatesForCTEDefStalenessSuite.scala |  62 +++++-
 .../optimizer/RemoveRedundantAggregatesSuite.scala |  73 ++++++-
 .../apache/spark/sql/DataFrameAggregateSuite.scala | 146 ++++++++++++-
 .../sql/execution/python/PythonUDFSuite.scala      |  23 ++
 .../sql/streaming/StreamingAggregationSuite.scala  |  21 ++
 15 files changed, 879 insertions(+), 48 deletions(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingNumbers.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingNumbers.scala
index cf776084046a..bced517f97b2 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingNumbers.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingNumbers.scala
@@ -18,10 +18,11 @@
 package org.apache.spark.sql.catalyst.optimizer
 
 import org.apache.spark.SparkException
-import org.apache.spark.sql.catalyst.expressions.{Alias, And, ArrayTransform, 
CaseWhen, Coalesce, CreateArray, CreateMap, CreateNamedStruct, EqualTo, 
ExpectsInputTypes, Expression, GetStructField, If, IsNull, 
KnownFloatingPointNormalized, LambdaFunction, Literal, NamedLambdaVariable, Or, 
TransformValues, UnaryExpression}
+import org.apache.spark.sql.catalyst.expressions.{Alias, And, ArrayTransform, 
CaseWhen, Coalesce, CreateArray, CreateMap, CreateNamedStruct, EqualTo, 
ExpectsInputTypes, Expression, GetStructField, If, IsNull, 
KnownFloatingPointNormalized, LambdaFunction, Literal, NamedExpression, 
NamedLambdaVariable, Or, PythonUDAF, TransformValues, UnaryExpression}
+import org.apache.spark.sql.catalyst.expressions.aggregate.AggregateExpression
 import org.apache.spark.sql.catalyst.expressions.codegen.{CodegenContext, 
ExprCode}
 import org.apache.spark.sql.catalyst.planning.{ExtractEquiJoinKeys, 
ExtractSingleColumnNullAwareAntiJoin}
-import org.apache.spark.sql.catalyst.plans.logical.{LogicalPlan, Window}
+import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, LogicalPlan, 
Window}
 import org.apache.spark.sql.catalyst.rules.Rule
 import org.apache.spark.sql.catalyst.trees.TreePattern._
 import org.apache.spark.sql.types._
@@ -78,9 +79,11 @@ object NormalizeFloatingNumbers extends Rule[LogicalPlan] {
       // join keys, assuming Joins with join keys are always planned as hash 
join or sort merge
       // join. It's very unlikely that we will break this assumption in the 
near future.
       case j @ ExtractEquiJoinKeys(_, leftKeys, rightKeys, condition, _, _, _, 
_)
-          // The analyzer guarantees left and right joins keys are of the same 
data type. Here we
-          // only need to check join keys of one side.
-          if leftKeys.exists(k => needNormalize(k)) =>
+          // The analyzer guarantees left and right join keys are of the same 
data type, but a key
+          // can already be normalized on one side only (e.g. a filter pushed 
below an Aggregate
+          // reads the normalized grouping key, and a subquery rewrite turns 
it into a join key),
+          // so check both sides.
+          if leftKeys.exists(needNormalize) || rightKeys.exists(needNormalize) 
=>
         val newLeftJoinKeys = leftKeys.map(normalize)
         val newRightJoinKeys = rightKeys.map(normalize)
         val newConditions = newLeftJoinKeys.zip(newRightJoinKeys).map {
@@ -90,7 +93,7 @@ object NormalizeFloatingNumbers extends Rule[LogicalPlan] {
 
       // The specialized NAAJ is a hash join, but its OR condition is not an 
equi-join shape.
       case j @ ExtractSingleColumnNullAwareAntiJoin(leftKeys, rightKeys)
-          if leftKeys.exists(needNormalize) =>
+          if leftKeys.exists(needNormalize) || rightKeys.exists(needNormalize) 
=>
         val equality = EqualTo(normalize(leftKeys.head), 
normalize(rightKeys.head))
         j.copy(condition = Some(Or(equality, IsNull(equality))))
 
@@ -100,6 +103,68 @@ object NormalizeFloatingNumbers extends Rule[LogicalPlan] {
       // Scaladoc just above.
     }
 
+  /**
+   * Whether `aggregate` outputs its float/double grouping keys normalized. 
The planner normalizes
+   * the grouping keys of every Aggregate (see `SparkStrategies.Aggregation`), 
except one whose
+   * aggregate functions are all grouped-agg pandas UDFs, planned as 
`ArrowAggregatePythonExec` on
+   * the raw keys. Rules that replace an Aggregate with a Project check 
[[readsNormalizedKeys]].
+   * The answer can still change during optimization, see [[mayChangeKeyForm]].
+   */
+  private[sql] def outputsNormalizedKeys(aggregate: Aggregate): Boolean = {
+    val aggregateFunctions = aggregate.aggregateExpressions.flatMap(_.collect {
+      case ae: AggregateExpression => ae.aggregateFunction
+    })
+    aggregateFunctions.isEmpty || 
!aggregateFunctions.forall(_.isInstanceOf[PythonUDAF])
+  }
+
+  /**
+   * Whether the form of the keys `aggregate` outputs may still change during 
optimization, so that
+   * a filter pushed below it must not depend on it. This is the case for an 
Aggregate with a
+   * grouped-agg pandas UDF: `ColumnPruning` can remove its other aggregate 
functions, so that it
+   * outputs the raw keys, or all of them, so that it becomes group-only and 
outputs normalized
+   * keys.
+   */
+  private[sql] def mayChangeKeyForm(aggregate: Aggregate): Boolean = {
+    aggregate.aggregateExpressions.exists(_.exists {
+      case ae: AggregateExpression => 
ae.aggregateFunction.isInstanceOf[PythonUDAF]
+      case _ => false
+    })
+  }
+
+  /**
+   * The float/double grouping expressions that `aggregate` outputs 
normalized, without aliases.
+   */
+  private[sql] def normalizedKeys(aggregate: Aggregate): Seq[Expression] = {
+    if (outputsNormalizedKeys(aggregate)) {
+      aggregate.groupingExpressions
+        .map {
+          case a: Alias => a.child
+          case e => e
+        }
+        .filter(e => needNormalize(e.dataType))
+    } else {
+      Nil
+    }
+  }
+
+  /**
+   * Whether a rule must not replace `aggregate` with a Project of `outputs` 
(a subset of
+   * `aggregate.aggregateExpressions`) over its child, because one of them 
reads a float/double
+   * grouping key that `aggregate` outputs normalized: the Project would 
output the raw key,
+   * unlike the Aggregate and unlike what a filter pushed below the Aggregate 
has read. Streaming
+   * Aggregates are exempt and keep the previous behavior: keeping one that 
such a rule used to
+   * remove would add a stateful operator, which a query restarted from an 
existing checkpoint
+   * rejects. Filters are not normalized below streaming Aggregates either (see
+   * `PushPredicateThroughNonJoin.pushBelowAggregate`).
+   */
+  private[sql] def readsNormalizedKeys(
+      aggregate: Aggregate,
+      outputs: Seq[NamedExpression]): Boolean = {
+    val keys = normalizedKeys(aggregate)
+    !aggregate.isStreaming && keys.nonEmpty &&
+      outputs.exists(_.exists(e => keys.exists(_.semanticEquals(e))))
+  }
+
   /**
    * Short circuit if the underlying expression is already normalized
    */
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlan.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlan.scala
index 6f732b2d0f20..8d3139961b8c 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlan.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlan.scala
@@ -29,7 +29,8 @@ import org.apache.spark.sql.internal.SQLConf
  *   - if the max rows per partition of the child of local sort is less than 
or equal to 1,
  *     remove the local sort
  *   - if the max rows of the child of aggregate is less than or equal to 1 
and its child and
- *     it's grouping only(include the rewritten distinct plan), convert 
aggregate to project
+ *     it's grouping only(include the rewritten distinct plan), convert 
aggregate to project,
+ *     unless the project would read float/double grouping keys that the 
aggregate normalizes
  *   - if the max rows of the child of aggregate is less than or equal to 1,
  *     set distinct to false in all aggregate expression
  *
@@ -51,7 +52,8 @@ object OptimizeOneRowPlan extends Rule[LogicalPlan] {
       case Sort(_, false, child, _) if child.maxRowsPerPartition.exists(_ <= 
1L) &&
         isChildEligible(child, enableForStreaming) => child
       case agg @ Aggregate(_, _, child, _) if agg.groupOnly && 
child.maxRows.exists(_ <= 1L) &&
-        isChildEligible(child, enableForStreaming) =>
+        isChildEligible(child, enableForStreaming) &&
+        !NormalizeFloatingNumbers.readsNormalizedKeys(agg, 
agg.aggregateExpressions) =>
         Project(agg.aggregateExpressions, child)
       case agg: Aggregate if agg.child.maxRows.exists(_ <= 1L) &&
         isChildEligible(agg.child, enableForStreaming) =>
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
index 9e2c4c6b972f..853b19e4986e 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
@@ -954,6 +954,23 @@ object LimitPushDown extends Rule[LogicalPlan] {
     }
   }
 
+  // Pushes `LocalLimit(1)` below a group-only Aggregate under `LIMIT 1` and 
replaces the Aggregate
+  // with a Project of its child. If that Project would read float/double keys 
that the Aggregate
+  // normalizes, the Aggregate is kept, with the limit still pushed below it.
+  private def pushLimitOneThroughGroupOnlyAggregate(
+      limitExpr: Expression,
+      agg: Aggregate): LogicalPlan = {
+    if (!NormalizeFloatingNumbers.readsNormalizedKeys(agg, 
agg.aggregateExpressions)) {
+      val newAgg = EliminateSorts(agg.copy(child = LocalLimit(limitExpr, 
agg.child)))
+        .asInstanceOf[Aggregate]
+      Project(newAgg.aggregateExpressions, newAgg.child)
+    } else if (agg.child.maxRowsPerPartition.exists(_ <= 1)) {
+      agg
+    } else {
+      EliminateSorts(agg.copy(child = LocalLimit(limitExpr, agg.child)))
+    }
+  }
+
   private def pushLocalLimitThroughJoin(limitExpr: Expression, join: Join): 
Join = {
     join.joinType match {
       case RightOuter if join.condition.nonEmpty =>
@@ -1007,11 +1024,9 @@ object LimitPushDown extends Rule[LogicalPlan] {
       LocalLimit(exp, project.copy(child = pushLocalLimitThroughJoin(exp, 
join)))
     // Push down limit 1 through Aggregate and turn Aggregate into Project if 
it is group only.
     case Limit(le @ IntegerLiteral(1), a: Aggregate) if a.groupOnly =>
-      val newAgg = EliminateSorts(a.copy(child = LocalLimit(le, 
a.child))).asInstanceOf[Aggregate]
-      Limit(le, Project(newAgg.aggregateExpressions, newAgg.child))
+      Limit(le, pushLimitOneThroughGroupOnlyAggregate(le, a))
     case Limit(le @ IntegerLiteral(1), p @ Project(_, a: Aggregate)) if 
a.groupOnly =>
-      val newAgg = EliminateSorts(a.copy(child = LocalLimit(le, 
a.child))).asInstanceOf[Aggregate]
-      Limit(le, p.copy(child = Project(newAgg.aggregateExpressions, 
newAgg.child)))
+      Limit(le, p.copy(child = pushLimitOneThroughGroupOnlyAggregate(le, a)))
     // Merge offset value and limit value into LocalLimit and pushes down 
LocalLimit through Offset.
     case LocalLimit(le, Offset(oe, grandChild)) =>
       Offset(oe, LocalLimit(Add(le, oe), grandChild))
@@ -2690,24 +2705,16 @@ object PushPredicateThroughNonJoin extends 
Rule[LogicalPlan] with PredicateHelpe
     // We can push down deterministic predicate through Aggregate, including 
throwable predicate.
     // If we can push down a filter through Aggregate, it means the filter 
only references the
     // grouping keys or constants. The Aggregate operator can't reduce 
distinct values of grouping
-    // keys so the filter won't see any new data after push down.
+    // keys so the filter won't see any new data after push down. Float/double 
grouping keys are
+    // normalized by the Aggregate, so the pushed filter reads them normalized 
wherever that could
+    // change its result (see `pushBelowAggregate`).
     case filter @ Filter(condition, aggregate: Aggregate)
       if aggregate.aggregateExpressions.forall(_.deterministic)
         && aggregate.groupingExpressions.nonEmpty =>
-      val aliasMap = getAliasMap(aggregate)
-
-      // For each filter, expand the alias and check if the filter can be 
evaluated using
-      // attributes produced by the aggregate operator's child operator.
-      val (pushDown, stayUp) = splitConjunctivePredicates(condition).partition 
{ cond =>
-        val replaced = replaceAlias(cond, aliasMap)
-        cond.deterministic && cond.references.nonEmpty &&
-          replaced.references.subsetOf(aggregate.child.outputSet)
-      }
+      val (pushDown, stayUp) = 
pushBelowAggregate(splitConjunctivePredicates(condition), aggregate)
 
       if (pushDown.nonEmpty) {
-        val pushDownPredicate = pushDown.reduce(And)
-        val replaced = replaceAlias(pushDownPredicate, aliasMap)
-        val newAggregate = aggregate.copy(child = Filter(replaced, 
aggregate.child))
+        val newAggregate = aggregate.copy(child = Filter(pushDown.reduce(And), 
aggregate.child))
         // If there is no more filter to stay up, just eliminate the filter.
         // Otherwise, create "Filter(stayUp) <- Aggregate <- 
Filter(pushDownPredicate)".
         if (stayUp.isEmpty) newAggregate else Filter(stayUp.reduce(And), 
newAggregate)
@@ -2814,6 +2821,86 @@ object PushPredicateThroughNonJoin extends 
Rule[LogicalPlan] with PredicateHelpe
     case _ => false
   }
 
+  // Splits `conds`, the conjuncts of a filter on top of `aggregate`, into the 
ones to push below
+  // it, rewritten in terms of the aggregate's child, and the ones that stay 
above it. A conjunct
+  // can be pushed if it is deterministic and, after alias expansion, only 
reads the aggregate's
+  // child (that is, grouping keys and constants). Expressions above the 
aggregate see its
+  // float/double grouping keys normalized (see NormalizeFloatingNumbers: -0.0 
becomes 0.0 and every
+  // NaN becomes one canonical NaN), so a pushed conjunct reads them 
normalized too, wherever the
+  // raw value could change its result. If the form of the keys the aggregate 
outputs may still
+  // change (with grouped-agg pandas UDFs, see 
NormalizeFloatingNumbers.mayChangeKeyForm), a
+  // conjunct that normalizing would change stays above it instead, where it 
reads whatever the
+  // final plan outputs. Streaming aggregates keep the previous behavior, with 
conjuncts pushed
+  // below them unnormalized: rules can still replace them with a Project of 
the raw keys (see
+  // NormalizeFloatingNumbers.readsNormalizedKeys), and keeping a conjunct 
above one could separate
+  // two aggregates that RemoveRedundantAggregates would merge, adding a 
stateful operator. The CTE
+  // rule PushdownPredicatesAndPruneColumnsForCTEDef uses this too, to find 
the filters it pushed.
+  private[optimizer] def pushBelowAggregate(
+      conds: Seq[Expression],
+      aggregate: Aggregate): (Seq[Expression], Seq[Expression]) = {
+    val aliasMap = getAliasMap(aggregate)
+    val keys = if (aggregate.isStreaming) {
+      Nil
+    } else {
+      aggregate.groupingExpressions
+        .filter(e => NormalizeFloatingNumbers.needNormalize(e.dataType))
+        .map(trimAliases)
+    }
+    val mayChangeKeyForm = NormalizeFloatingNumbers.mayChangeKeyForm(aggregate)
+    val pushDown = Seq.newBuilder[Expression]
+    val stayUp = Seq.newBuilder[Expression]
+    conds.foreach { cond =>
+      val replaced = replaceAlias(cond, aliasMap)
+      val pushable = cond.deterministic && cond.references.nonEmpty &&
+        replaced.references.subsetOf(aggregate.child.outputSet)
+      val normalized = if (pushable && keys.nonEmpty) {
+        normalizeGroupingExpressions(replaced, keys)
+      } else {
+        replaced
+      }
+      if (pushable && (!mayChangeKeyForm || normalized.fastEquals(replaced))) {
+        pushDown += normalized
+      } else {
+        stayUp += cond
+      }
+    }
+    (pushDown.result(), stayUp.result())
+  }
+
+  // Rewrites `e` so that it gives the same result on the raw values of 
`grouping` as `e` gives on
+  // their normalized values (see NormalizeFloatingNumbers). Each occurrence 
of a grouping
+  // expression is wrapped in its normalized form, with one exception: a 
float/double attribute
+  // that is a direct operand of one of the comparisons matched below is left 
as is, and so is a
+  // float attribute widened to double (as when a FLOAT column is compared 
with a DOUBLE or
+  // DECIMAL value), since widening keeps -0.0 and NaN. Those comparisons 
already treat -0.0 and
+  // 0.0 as equal and all NaNs as equal (see SQLOrderingUtil.compareDoubles and
+  // CodegenContext.genEqual), and so does InSet, an IN list that OptimizeIn 
converted. IsNull,
+  // IsNotNull and IsNaN cannot tell normalized values apart either, nor can 
an IN subquery, which
+  // becomes a join on EqualTo conditions (whose keys NormalizeFloatingNumbers 
normalizes on both
+  // sides when it is an equi-join). So wrapping would not change the result, 
and leaving them as
+  // is keeps plain comparisons usable by data sources and partition pruning.
+  private[optimizer] def normalizeGroupingExpressions(
+      e: Expression,
+      grouping: Seq[Expression]): Expression = e match {
+    // A grouping key already normalized when the filter was pushed through an 
Aggregate above on
+    // the same key. Any other normalized expression may be computed from a 
grouping key (when the
+    // upper Aggregate groups by an expression of this one's key), so the key 
inside it still has
+    // to be normalized. This also matches array and map keys: `normalize` 
builds new lambda
+    // variables for them, but `semanticEquals` compares canonicalized trees, 
which renumber them.
+    case _: KnownFloatingPointNormalized
+        if grouping.exists(g => 
NormalizeFloatingNumbers.normalize(g).semanticEquals(e)) => e
+    case _: EqualTo | _: EqualNullSafe | _: LessThan | _: LessThanOrEqual | _: 
GreaterThan |
+         _: GreaterThanOrEqual | _: In | _: InSet | _: InSubquery | _: IsNull 
| _: IsNotNull |
+         _: IsNaN =>
+      e.mapChildren {
+        case a: Attribute if a.dataType == FloatType || a.dataType == 
DoubleType => a
+        case c @ Cast(a: Attribute, DoubleType, _, _) if a.dataType == 
FloatType => c
+        case c => normalizeGroupingExpressions(c, grouping)
+      }
+    case _ if grouping.exists(_.semanticEquals(e)) => 
NormalizeFloatingNumbers.normalize(e)
+    case _ => e.mapChildren(normalizeGroupingExpressions(_, grouping))
+  }
+
   private def pushDownPredicate(
       filter: Filter,
       grandchild: LogicalPlan)(insertFilter: Expression => LogicalPlan): 
LogicalPlan = {
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesAndPruneColumnsForCTEDef.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesAndPruneColumnsForCTEDef.scala
index 43b6845aa01f..f5da82385be3 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesAndPruneColumnsForCTEDef.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesAndPruneColumnsForCTEDef.scala
@@ -185,9 +185,11 @@ object PushdownPredicatesAndPruneColumnsForCTEDef extends 
Rule[LogicalPlan] with
    * `plan`, wherever predicate push-down rules sharing the fixedPoint batches 
with this rule
    * (e.g. `PushDownPredicates`) may have moved it. The descent mirrors the 
cases of
    * `PushPredicateThroughNonJoin` and `PushPredicateThroughJoin`, translating 
`predicate` back
-   * the same way they translate the pushed condition (for `Project` and 
`Aggregate` it uses
-   * the very same `AliasHelper` utilities, so the two cannot drift apart): 
through projection
-   * and grouping-key aliases, positionally into each branch (`Union`), and 
unchanged
+   * the same way they translate the pushed condition (for `Project` it uses 
the very same
+   * `AliasHelper` utilities, and for `Aggregate` it calls
+   * `PushPredicateThroughNonJoin.pushBelowAggregate`, so the two cannot drift 
apart): through
+   * projection aliases, through grouping-key aliases with float/double 
grouping keys normalized
+   * as `pushBelowAggregate` does, positionally into each branch (`Union`), 
and unchanged
    * through operators that pass the referenced attributes verbatim (`Join`, 
`Window`, and
    * output-preserving unary nodes like `Filter`, `Sort`, `Repartition`). The 
descent is recursive
    * because the filter can end up several operators down -- in particular 
between the projections
@@ -239,13 +241,17 @@ object PushdownPredicatesAndPruneColumnsForCTEDef extends 
Rule[LogicalPlan] with
         }
         if (removedAny) (u.withNewChildren(newChildren), true) else (u, false)
       case agg: Aggregate =>
-        // Mirror PushPredicateThroughNonJoin: translate the target through 
the grouping
-        // aliases with the same helper it uses to move grouping-key filters 
below the
-        // aggregate (filters referencing aggregate expressions stay up, so 
they are always
-        // found above this node).
-        val translated = replaceAlias(target, getAliasMap(agg))
-        val (newChild, removed) = remove(agg.child, translated)
-        if (removed) (agg.withNewChildren(Seq(newChild)), true) else (agg, 
false)
+        // Mirror PushPredicateThroughNonJoin: split and translate the target 
with the same helper
+        // it uses to move filters below the aggregate. A filter it keeps 
entirely above this node
+        // has been found above already, and one it splits is not entirely 
below it.
+        val (pushDown, stayUp) =
+          
PushPredicateThroughNonJoin.pushBelowAggregate(splitConjunctivePredicates(target),
 agg)
+        if (pushDown.isEmpty || stayUp.nonEmpty) {
+          (agg, false)
+        } else {
+          val (newChild, removed) = remove(agg.child, pushDown.reduce(And))
+          if (removed) (agg.withNewChildren(Seq(newChild)), true) else (agg, 
false)
+        }
       case w: Window =>
         // PushDownPredicates pushes filters referencing only partition 
columns below the
         // window unchanged (partition columns are input attributes, so no 
translation).
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregates.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregates.scala
index b4602d0ddcc9..d4f844c1f9ae 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregates.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregates.scala
@@ -17,7 +17,7 @@
 
 package org.apache.spark.sql.catalyst.optimizer
 
-import org.apache.spark.sql.catalyst.expressions.{AliasHelper, AttributeSet, 
ExpressionSet}
+import org.apache.spark.sql.catalyst.expressions.{AliasHelper, Attribute, 
AttributeSet, Expression, ExpressionSet}
 import org.apache.spark.sql.catalyst.expressions.aggregate.AggregateExpression
 import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, LogicalPlan, 
Project}
 import org.apache.spark.sql.catalyst.rules.Rule
@@ -34,7 +34,8 @@ object RemoveRedundantAggregates extends Rule[LogicalPlan] 
with AliasHelper {
       val projectList = 
lower.aggregateExpressions.filter(upper.references.contains(_))
       upper.copy(child = Project(projectList, lower.child))
     case agg @ Aggregate(groupingExps, _, child, _)
-        if agg.groupOnly && 
child.distinctKeys.exists(_.subsetOf(ExpressionSet(groupingExps))) =>
+        if agg.groupOnly && 
child.distinctKeys.exists(_.subsetOf(ExpressionSet(groupingExps))) &&
+          !NormalizeFloatingNumbers.readsNormalizedKeys(agg, 
agg.aggregateExpressions) =>
       Project(agg.aggregateExpressions, child)
   }
 
@@ -60,7 +61,29 @@ object RemoveRedundantAggregates extends Rule[LogicalPlan] 
with AliasHelper {
     // relation, while a global aggregation will return a single row.
     lazy val lowerIsGlobalAgg = lower.groupingExpressions.isEmpty
 
-    upperHasNoDuplicateSensitiveAgg && upperRefsOnlyDeterministicNonAgg && 
!lowerIsGlobalAgg
+    // The lower aggregation outputs its float/double grouping keys 
normalized, so it is not
+    // redundant if the upper aggregation would read them raw without it: 
through an output computed
+    // from a key, or inside an aggregate function or a computed grouping 
expression. A key the
+    // upper aggregation groups by directly is normalized by it. Streaming 
plans keep the previous
+    // behavior, see NormalizeFloatingNumbers.readsNormalizedKeys.
+    lazy val upperReadsNormalizedKeys = !lower.isStreaming && {
+      val keys = NormalizeFloatingNumbers.normalizedKeys(lower)
+      val (keyOutputs, otherOutputs) = lower.aggregateExpressions
+        .filter(upper.references.contains(_))
+        .partition(output => 
keys.exists(_.semanticEquals(trimAliases(output))))
+      val keyAttributes = AttributeSet(keyOutputs.map(_.toAttribute))
+      def readsKey(e: Expression): Boolean = 
e.references.intersect(keyAttributes).nonEmpty
+      NormalizeFloatingNumbers.readsNormalizedKeys(lower, otherOutputs) ||
+        keyAttributes.nonEmpty && (
+          upper.groupingExpressions.exists(g => !g.isInstanceOf[Attribute] && 
readsKey(g)) ||
+          upper.aggregateExpressions.exists(_.exists {
+            case ae: AggregateExpression => readsKey(ae)
+            case _ => false
+          }))
+    }
+
+    upperHasNoDuplicateSensitiveAgg && upperRefsOnlyDeterministicNonAgg && 
!lowerIsGlobalAgg &&
+      !upperReadsNormalizedKeys
   }
 
   private def isDuplicateSensitive(ae: AggregateExpression): Boolean = {
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/PredicateSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/PredicateSuite.scala
index fc3e80d89004..6bd6a8bcab2d 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/PredicateSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/PredicateSuite.scala
@@ -27,6 +27,7 @@ import org.apache.spark.sql.catalyst.{CatalystTypeConverters, 
InternalRow}
 import org.apache.spark.sql.catalyst.analysis.TypeCheckResult
 import org.apache.spark.sql.catalyst.encoders.ExamplePointUDT
 import org.apache.spark.sql.catalyst.expressions.codegen.CodegenContext
+import org.apache.spark.sql.catalyst.optimizer.NormalizeNaNAndZero
 import org.apache.spark.sql.catalyst.parser.CatalystSqlParser
 import org.apache.spark.sql.catalyst.util.{ArrayData, GenericArrayData}
 import org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
@@ -571,6 +572,62 @@ class PredicateSuite extends SparkFunSuite with 
ExpressionEvalHelper {
     checkEvaluation(GreaterThan(Literal(0.0F), Literal(-0.0F)), false)
   }
 
+  test("SPARK-60150: comparisons exempt from normalization agree with 
normalized values") {
+    // PushPredicateThroughNonJoin pushes these comparisons below an Aggregate 
without
+    // normalizing a float/double grouping key that is a direct operand, so 
each must give the
+    // same result for a value and for its normalized form: -0.0 and 0.0, and 
NaNs with different
+    // bit patterns. The values come from the input row rather than literals, 
because literal
+    // codegen emits every NaN as the canonical NaN, and pushed filters read 
raw input values.
+    val binary: Seq[(Expression, Expression) => Expression] = Seq(
+      EqualTo, EqualNullSafe, LessThan, LessThanOrEqual, GreaterThan, 
GreaterThanOrEqual,
+      (l, r) => In(l, Seq(r)))
+    val unary: Seq[Expression => Expression] = Seq(IsNull, IsNotNull, IsNaN)
+
+    def check(dataType: DataType, values: Seq[Any]): Unit = {
+      val left = BoundReference(0, dataType, nullable = true)
+      val right = BoundReference(1, dataType, nullable = true)
+      def norm(v: Any): Any = NormalizeNaNAndZero(Literal.create(v, 
dataType)).eval()
+      for (v <- values; w <- values; op <- binary) {
+        val e = op(left, right)
+        checkEvaluation(e, e.eval(InternalRow(norm(v), w)), InternalRow(v, w))
+        checkEvaluation(e, e.eval(InternalRow(v, norm(w))), InternalRow(v, w))
+      }
+      for (v <- values; op <- unary) {
+        val e = op(left)
+        checkEvaluation(e, e.eval(InternalRow(norm(v))), InternalRow(v))
+      }
+      // InSet, with the HashSet that OptimizeIn builds from an IN list of 
literals. A null in the
+      // list is left out: interpreted and generated code differ on NaN INSET 
(null), for raw and
+      // normalized NaNs alike.
+      for (v <- values; w <- values if w != null) {
+        val e = InSet(left, HashSet[Any](w))
+        checkEvaluation(e, e.eval(InternalRow(norm(v))), InternalRow(v))
+      }
+    }
+
+    val otherDoubleNaN = java.lang.Double.longBitsToDouble(0x7ff8000000000001L)
+    assert(java.lang.Double.doubleToRawLongBits(otherDoubleNaN) !=
+      java.lang.Double.doubleToRawLongBits(Double.NaN))
+    val doubles = Seq(-0.0d, 0.0d, Double.NaN, otherDoubleNaN, 1.0d, null)
+    check(DoubleType, doubles)
+
+    val otherFloatNaN = java.lang.Float.intBitsToFloat(0x7fc00001)
+    assert(java.lang.Float.floatToRawIntBits(otherFloatNaN) !=
+      java.lang.Float.floatToRawIntBits(Float.NaN))
+    val floats = Seq(-0.0f, 0.0f, Float.NaN, otherFloatNaN, 1.0f, null)
+    check(FloatType, floats)
+
+    // A FLOAT key compared with a DOUBLE or DECIMAL value is widened to 
DOUBLE, and the widened
+    // key is not normalized either.
+    val widened = Cast(BoundReference(0, FloatType, nullable = true), 
DoubleType)
+    val value = BoundReference(1, DoubleType, nullable = true)
+    def normFloat(v: Any): Any = NormalizeNaNAndZero(Literal.create(v, 
FloatType)).eval()
+    for (v <- floats; w <- doubles; op <- binary) {
+      val e = op(widened, value)
+      checkEvaluation(e, e.eval(InternalRow(normFloat(v), w)), InternalRow(v, 
w))
+    }
+  }
+
   test("SPARK-32110: compare special double/float values in array") {
     def createUnsafeDoubleArray(d: Double): Literal = {
       Literal(UnsafeArrayData.fromPrimitiveArray(Array(d)), 
ArrayType(DoubleType))
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
index 5e1d2b9f2fd6..84f68fa018c1 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/FilterPushdownSuite.scala
@@ -2349,4 +2349,235 @@ class FilterPushdownSuite extends PlanTest {
     val optimizedC = Optimize.execute(queryC)
     comparePlans(optimizedC, queryC, checkAnalysis = false)
   }
+
+  test("SPARK-60150: push a filter on a float/double grouping key with the 
normalized key") {
+    val x = $"x".double
+    val relation = LocalRelation(x)
+    val normX = NormalizeFloatingNumbers.normalize(x)
+
+    // SELECT * FROM (SELECT DISTINCT x FROM t) WHERE cast(x AS STRING) = 
'-0.0'
+    // DISTINCT becomes Aggregate(x, x) in the optimizer, so build that shape 
directly.
+    val originalQuery = relation.groupBy(x)(x).where(x.cast(StringType) === 
"-0.0")
+    val optimized = Optimize.execute(originalQuery.analyze)
+
+    // The Aggregate normalizes -0.0 to 0.0 (NormalizeFloatingNumbers), so the 
pushed predicate
+    // must read the normalized key.
+    val correctAnswer = relation
+      .where(normX.cast(StringType) === "-0.0")
+      .groupBy(x)(x)
+      .analyze
+    comparePlans(optimized, correctAnswer)
+  }
+
+  test("SPARK-60150: normalize only the keys that can change a pushed filter's 
result") {
+    val x = $"x".double
+    val y = $"y".float
+    val relation = LocalRelation(x, y)
+    val normX = NormalizeFloatingNumbers.normalize(x)
+
+    // Within an OR, the key is normalized under the Cast but not as a direct 
comparison operand.
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(x, y)(x, y).where(x > 5.0 || x.cast(StringType) === 
"-0.0").analyze),
+      relation.where(x > 5.0 || normX.cast(StringType) === "-0.0")
+        .groupBy(x, y)(x, y).analyze)
+
+    // Across conjuncts, only the one that reads the key through a Cast 
changes.
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(x, y)(x, y).where(y > 1.0f && x.cast(StringType) === 
"-0.0").analyze),
+      relation.where(y > 1.0f && normX.cast(StringType) === "-0.0")
+        .groupBy(x, y)(x, y).analyze)
+  }
+
+  test("SPARK-60150: normalize aliases computed from a float/double key after 
alias expansion") {
+    val x = $"x".double
+    val relation = LocalRelation(x)
+    val normX = NormalizeFloatingNumbers.normalize(x)
+
+    // The alias is computed from the normalized key above the Aggregate, even 
when the alias
+    // itself is not float/double or is compared directly.
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(x)(x.cast(StringType).as("s")).where($"s" === 
"-0.0").analyze),
+      relation.where(normX.cast(StringType) === "-0.0")
+        .groupBy(x)(x.cast(StringType).as("s")).analyze)
+
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(x)(Length(x.cast(StringType)).as("k")).where($"k" === 
4).analyze),
+      relation.where(Length(normX.cast(StringType)) === 4)
+        .groupBy(x)(Length(x.cast(StringType)).as("k")).analyze)
+
+    // A plain rename of the key compared directly is pushed down unchanged.
+    comparePlans(
+      Optimize.execute(relation.groupBy(x)(x.as("k")).where($"k" === 
0.0).analyze),
+      relation.where(x === 0.0).groupBy(x)(x.as("k")).analyze)
+  }
+
+  test("SPARK-60150: push down comparisons on a float/double grouping key 
unchanged") {
+    val x = $"x".double
+    val y = $"y".float
+    val relation = LocalRelation(x, y, $"b".int)
+
+    // Comparisons treat -0.0/0.0 and all NaNs as equal, so they need no 
normalization. That
+    // includes a FLOAT key widened to DOUBLE to be compared with a DOUBLE 
value, and InSet.
+    val comparisons = x > 1.0 || (y === 0.0f && !IsNaN(x)) || x.in(2.0, 3.0) 
|| x.isNull ||
+      y > 1.5 || y.in(1.5, 2.5) || InSet(x, Set(4.0, 5.0))
+    val originalQuery = relation
+      .groupBy(x, y)(x, y, count($"b").as("c"))
+      .where(comparisons)
+
+    val optimized = Optimize.execute(originalQuery.analyze)
+
+    val correctAnswer = relation
+      .where(comparisons)
+      .groupBy(x, y)(x, y, count($"b").as("c"))
+      .analyze
+    comparePlans(optimized, correctAnswer)
+  }
+
+  test("SPARK-60150: normalize grouping expressions and complex keys in pushed 
filters") {
+    val x = $"x".double
+    val s = $"s".struct(StructType(Seq(StructField("d", DoubleType))))
+    val relation = LocalRelation(x, s)
+
+    // The grouping expression, not the column it reads, is what the Aggregate 
normalizes.
+    val k = x * 2.0
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(k)(k.as("k")).where($"k".cast(StringType) === 
"-0.0").analyze),
+      relation.where(NormalizeFloatingNumbers.normalize(k).cast(StringType) 
=== "-0.0")
+        .groupBy(k)(k.as("k")).analyze)
+
+    // A struct key is normalized field by field.
+    val field = GetStructField(s, 0)
+    comparePlans(
+      Optimize.execute(relation.groupBy(s)(s).where(field.cast(StringType) === 
"-0.0").analyze),
+      relation.where(GetStructField(NormalizeFloatingNumbers.normalize(s), 0)
+        .cast(StringType) === "-0.0").groupBy(s)(s).analyze)
+  }
+
+  test("SPARK-60150: don't normalize a pushed filter twice") {
+    val x = $"x".double
+    val relation = LocalRelation(x)
+    val normX = NormalizeFloatingNumbers.normalize(x)
+
+    comparePlans(
+      Optimize.execute(
+        relation.groupBy(x)(x).groupBy(x)(x).where(x.cast(StringType) === 
"-0.0").analyze),
+      relation.where(normX.cast(StringType) === 
"-0.0").groupBy(x)(x).groupBy(x)(x).analyze)
+
+    // The upper key is computed from the lower key, so the lower key inside 
the normalized upper
+    // key is normalized too.
+    val k = Pow(x, Literal(-1.0)).as("k")
+    val lower = relation.groupBy(x)(k)
+    val upperKey = k.toAttribute
+    comparePlans(
+      Optimize.execute(
+        lower.groupBy(upperKey)(upperKey).where(upperKey.cast(StringType) === 
"Infinity").analyze),
+      relation
+        .where(NormalizeFloatingNumbers.normalize(Pow(normX, 
Literal(-1.0))).cast(StringType) ===
+          "Infinity")
+        .groupBy(x)(k)
+        .groupBy(upperKey)(upperKey)
+        .analyze)
+  }
+
+  test("SPARK-60150: keep a normalization-sensitive filter above a pandas UDF 
aggregate") {
+    val x = $"x".double
+    val v = $"v".int
+    val relation = LocalRelation(x, v)
+    // Whether such an aggregate outputs normalized keys depends on which of 
its aggregate
+    // functions column pruning keeps later, so a conjunct that normalizing 
would change stays
+    // above it, while a plain comparison is still pushed below it.
+    val pandasAgg = PythonUDAF("pandasAgg", null, IntegerType, Seq(v), 
udfDeterministic = true)
+      .toAggregateExpression().as("p")
+    val sensitive = x.cast(StringType) === "-0.0"
+    for (aggregates <- Seq(Seq(pandasAgg), Seq(pandasAgg, count(v).as("c")))) {
+      comparePlans(
+        Optimize.execute(
+          relation.groupBy(x)(x +: aggregates: _*).where(sensitive && x > 
1.0).analyze),
+        relation.where(x > 1.0).groupBy(x)(x +: aggregates: 
_*).where(sensitive).analyze)
+    }
+  }
+
+  test("SPARK-60150: push a filter below a streaming aggregate unnormalized, 
as before") {
+    val x = $"x".double
+    val streaming = LocalRelation(Seq(x, $"v".int), isStreaming = true)
+    // A streaming aggregate can still be replaced with a Project of the raw 
keys, and a filter kept
+    // above it could add a stateful operator.
+    val condition = x.cast(StringType) === "-0.0" && x > 1.0
+    comparePlans(
+      Optimize.execute(streaming.groupBy(x)(x, 
count($"v").as("c")).where(condition).analyze),
+      streaming.where(condition).groupBy(x)(x, count($"v").as("c")).analyze)
+  }
+
+  test("SPARK-60150: normalizeGroupingExpressions") {
+    val x = $"x".double
+    val f = $"f".float
+    val y = $"y".int
+    val s = $"s".struct(StructType(Seq(StructField("d", DoubleType))))
+    val normX = NormalizeFloatingNumbers.normalize(x)
+    val normS = NormalizeFloatingNumbers.normalize(s)
+    val arr = $"arr".array(DoubleType)
+    def normalize(e: Expression): Expression =
+      PushPredicateThroughNonJoin.normalizeGroupingExpressions(e, Seq(x, f, s, 
arr))
+    def unchanged(e: Expression): Unit = assert(normalize(e) == e)
+
+    // A grouping key on its own is normalized; other columns and literals are 
not.
+    assert(normalize(x) == normX)
+    unchanged(y)
+    unchanged(Literal(0.0))
+
+    // A float/double key that is a direct operand of these comparisons is 
left as is.
+    unchanged(x === 0.0)
+    unchanged(EqualNullSafe(x, Literal(0.0)))
+    unchanged(x < 1.0)
+    unchanged(f >= 1.0f)
+    unchanged(x === x)
+    unchanged(x.in(1.0, 2.0))
+    unchanged(InSet(x, Set(1.0, 2.0)))
+    unchanged(InSubquery(Seq(x), ListQuery(LocalRelation($"y".double))))
+    unchanged(x.isNull)
+    unchanged(x.isNotNull)
+    unchanged(IsNaN(x))
+
+    // A key under any other expression is normalized, even inside a 
comparison.
+    assert(normalize(x.cast(StringType) === "-0.0") == (normX.cast(StringType) 
=== "-0.0"))
+    assert(normalize(Length(x.cast(StringType)) === 4) == 
(Length(normX.cast(StringType)) === 4))
+    assert(normalize((x + 0.0) > 5.0) == ((normX + 0.0) > 5.0))
+
+    // A float key widened to double is left as is too, but other casts of it 
are normalized.
+    unchanged(f.cast(DoubleType) > 1.5)
+    unchanged(f.cast(DoubleType).in(1.5, 2.5))
+    assert(normalize(f.cast(StringType) === "-0.0") ==
+      (NormalizeFloatingNumbers.normalize(f).cast(StringType) === "-0.0"))
+
+    // Only float/double attributes are left as is, so a struct key is 
normalized.
+    assert(normalize(s.isNull) == normS.isNull)
+    assert(normalize(GetStructField(s, 0) === 0.0) == (GetStructField(normS, 
0) === 0.0))
+
+    // Boolean connectives are rewritten child by child.
+    unchanged(x === 0.0 || x > 5.0)
+    unchanged(!(x === 0.0))
+    assert(normalize(x > 5.0 || x.cast(StringType) === "-0.0") ==
+      (x > 5.0 || normX.cast(StringType) === "-0.0"))
+    assert(normalize(y === 1 && !(x.cast(StringType) === "-0.0")) ==
+      (y === 1 && !(normX.cast(StringType) === "-0.0")))
+
+    // An already normalized key, including an array key, is not normalized 
again, but a key
+    // inside another normalized expression is.
+    unchanged(normX.cast(StringType) === "-0.0")
+    unchanged(NormalizeFloatingNumbers.normalize(arr).cast(StringType) === 
"[0.0]")
+    assert(normalize(NormalizeFloatingNumbers.normalize(Pow(x, Literal(-1.0))) 
> 0.0) ==
+      (NormalizeFloatingNumbers.normalize(Pow(normX, Literal(-1.0))) > 0.0))
+
+    // Only the given grouping expressions count, matched semantically rather 
than by name.
+    assert(PushPredicateThroughNonJoin.normalizeGroupingExpressions(
+      x.cast(StringType), Seq.empty) == x.cast(StringType))
+    val renamed = 
x.withName("X").withQualifier(Seq("t")).withNullability(false)
+    assert(normalize(renamed) == NormalizeFloatingNumbers.normalize(renamed))
+    unchanged($"x".double)
+  }
 }
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LimitPushdownSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LimitPushdownSuite.scala
index 2dcab5cfd29c..3a6d7becef94 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LimitPushdownSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LimitPushdownSuite.scala
@@ -329,6 +329,24 @@ class LimitPushdownSuite extends PlanTest {
       x.groupBy("x.a".attr)("x.a".attr, count("x.a".attr)).limit(1).analyze)
   }
 
+  test("SPARK-60150: Push down limit 1 below a group only Aggregate on a float 
key") {
+    val d = $"d".double
+    val i = $"i".int
+    val relation = LocalRelation.fromExternalRows(Seq(d, i), Seq(Row(-0.0d, 
1), Row(0.0d, 2)))
+    // The aggregate outputs the float key normalized, and a project would 
output it raw, so the
+    // aggregate is kept and only the limit is pushed below it.
+    comparePlans(
+      Optimize.execute(relation.groupBy(d)(d).limit(1).analyze),
+      LocalLimit(1, relation).groupBy(d)(d).limit(1).analyze)
+    comparePlans(
+      Optimize.execute(relation.groupBy(d)(d).select(d).limit(1).analyze),
+      LocalLimit(1, relation).groupBy(d)(d).select(d).limit(1).analyze)
+    // Replaced by a project when the float key is not output.
+    comparePlans(
+      Optimize.execute(relation.groupBy(d, i)(i).limit(1).analyze),
+      LocalLimit(1, relation).select(i).limit(1).analyze)
+  }
+
   test("Push down limit 1 through Offset") {
     comparePlans(
       Optimize.execute(testRelation.offset(2).limit(1).analyze),
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingPointNumbersSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingPointNumbersSuite.scala
index 6298642c620b..1d0aadba02b2 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingPointNumbersSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/NormalizeFloatingPointNumbersSuite.scala
@@ -97,6 +97,30 @@ class NormalizeFloatingPointNumbersSuite extends PlanTest {
     comparePlans(doubleOptimized, correctAnswer)
   }
 
+  test("SPARK-60150: normalize join keys when only one side is already 
normalized") {
+    val normA = KnownFloatingPointNormalized(NormalizeNaNAndZero(a))
+    val normB = KnownFloatingPointNormalized(NormalizeNaNAndZero(b))
+
+    // A key normalized on one side (e.g. by a filter pushed below an 
Aggregate that a subquery
+    // rewrite turned into a join) must not stop the other side from being 
normalized.
+    comparePlans(
+      Optimize.execute(testRelation1.join(testRelation2, condition = 
Some(normA === b))),
+      testRelation1.join(testRelation2, condition = Some(normA === normB)))
+    comparePlans(
+      Optimize.execute(testRelation1.join(testRelation2, condition = Some(a 
=== normB))),
+      testRelation1.join(testRelation2, condition = Some(normA === normB)))
+
+    val equality = normA === b
+    val normalizedEquality = normA === normB
+    comparePlans(
+      Optimize.execute(testRelation1.join(
+        testRelation2, joinType = LeftAnti, condition = Some(equality || 
IsNull(equality)))),
+      testRelation1.join(
+        testRelation2,
+        joinType = LeftAnti,
+        condition = Some(normalizedEquality || IsNull(normalizedEquality))))
+  }
+
   test("normalize floating points in join keys (equal null safe) - 
idempotence") {
     val query = testRelation1.join(testRelation2, condition = Some(a <=> b))
 
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlanSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlanSuite.scala
index ba549a030f4a..4e8c7ae42487 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlanSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/OptimizeOneRowPlanSuite.scala
@@ -78,6 +78,19 @@ class OptimizeOneRowPlanSuite extends PlanTest {
     comparePlans(Optimize.execute(plan5), plan5)
   }
 
+  test("SPARK-60150: Keep group only aggregate that normalizes a float key") {
+    val d = $"d".double
+    val i = $"i".int
+    val oneRow = LocalRelation.fromExternalRows(Seq(d, i), data = 
Seq(Row(-0.0d, 1)))
+    // The aggregate outputs the float key normalized, and a project would 
output it raw.
+    val plan1 = oneRow.groupBy(d)(d).analyze
+    comparePlans(Optimize.execute(plan1), plan1)
+    val plan2 = oneRow.groupBy(d + 1.0)((d + 1.0).as("k")).analyze
+    comparePlans(Optimize.execute(plan2), plan2)
+    // Converted when the float key is not output.
+    comparePlans(Optimize.execute(oneRow.groupBy(d, i)(i).analyze), 
oneRow.select(i).analyze)
+  }
+
   test("Remove distinct in aggregate expression") {
     val plan1 = t1.groupBy($"a")(sumDistinct($"a").as("s")).analyze
     val expected1 = t1.groupBy($"a")(sum($"a").as("s")).analyze
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesForCTEDefStalenessSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesForCTEDefStalenessSuite.scala
index af17bbf55f7b..21eb30c71830 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesForCTEDefStalenessSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushdownPredicatesForCTEDefStalenessSuite.scala
@@ -18,9 +18,9 @@
 package org.apache.spark.sql.catalyst.optimizer
 
 import org.apache.spark.sql.catalyst.expressions.{Alias, And, Attribute, 
AttributeReference}
-import org.apache.spark.sql.catalyst.expressions.{Ascending, CurrentRow, 
RowFrame, RowNumber}
+import org.apache.spark.sql.catalyst.expressions.{Ascending, Cast, CurrentRow, 
RowFrame, RowNumber}
 import org.apache.spark.sql.catalyst.expressions.{EqualTo, Expression, 
GreaterThan}
-import org.apache.spark.sql.catalyst.expressions.{IsNotNull, Literal}
+import org.apache.spark.sql.catalyst.expressions.{IsNotNull, 
KnownFloatingPointNormalized, Literal, PythonUDAF}
 import org.apache.spark.sql.catalyst.expressions.{SortOrder, 
SpecifiedWindowFrame, UnboundedPreceding}
 import org.apache.spark.sql.catalyst.expressions.{WindowExpression, 
WindowSpecDefinition}
 import org.apache.spark.sql.catalyst.plans.{Inner, PlanTest}
@@ -28,7 +28,7 @@ import 
org.apache.spark.sql.catalyst.plans.logical.{Aggregate, CTERelationDef, C
 import org.apache.spark.sql.catalyst.plans.logical.{Filter, Join, JoinHint, 
LocalRelation}
 import org.apache.spark.sql.catalyst.plans.logical.{LogicalPlan, Project, 
Union, Window, WithCTE}
 import org.apache.spark.sql.catalyst.trees.TreeNodeTag
-import org.apache.spark.sql.types.IntegerType
+import org.apache.spark.sql.types.{DoubleType, IntegerType, StringType}
 
 /**
  * Regression test for a staleness defect in
@@ -217,6 +217,62 @@ class PushdownPredicatesForCTEDefStalenessSuite extends 
PlanTest {
       "previous push-down was not removed below the aggregate")
   }
 
+  test("SPARK-60150: rebuild removes a push-down normalized below a 
float/double aggregate") {
+    val a = AttributeReference("a", DoubleType, nullable = true)()
+    val b = AttributeReference("b", IntegerType, nullable = true)()
+    val pa = Alias(a, "pa")()
+    val aggregate = Aggregate(Seq(pa), Seq(pa), LocalRelation(a, b))
+
+    val cteId = 0L
+    val cteDef = CTERelationDef(aggregate, cteId)
+    // The first predicate can tell -0.0 from 0.0, so it is pushed with the 
normalized key.
+    val plan = withTwoRefs(cteDef)(
+      out => EqualTo(Cast(out(0), StringType), Literal("-0.0")),
+      out => EqualTo(out(0), Literal(7.0)))
+
+    val afterPass1 = PushdownPredicatesAndPruneColumnsForCTEDef.apply(plan)
+
+    val consumed = 
addIsNotNullToRef(PushPredicateThroughNonJoin.apply(afterPass1),
+      cteId, Literal(7.0))
+    assert(theOnlyDef(consumed).child match {
+      case Aggregate(_, _, f: Filter, _) =>
+        f.condition.exists(_.isInstanceOf[KnownFloatingPointNormalized])
+      case _ => false
+    }, "test setup failed: push-down did not move a normalized filter below 
the aggregate")
+
+    assertSingleEnrichedTopFilter(consumed,
+      "previous normalized push-down was not removed below the aggregate")
+  }
+
+  test("SPARK-60150: rebuild removes a push-down kept above a pandas UDF 
aggregate") {
+    val a = AttributeReference("a", DoubleType, nullable = true)()
+    val b = AttributeReference("b", IntegerType, nullable = true)()
+    val pa = Alias(a, "pa")()
+    val pandas = Alias(PythonUDAF("pandasAgg", null, IntegerType, Seq(b), 
udfDeterministic = true)
+      .toAggregateExpression(), "p")()
+    val aggregate = Aggregate(Seq(pa), Seq(pa, pandas), LocalRelation(a, b))
+
+    val cteId = 0L
+    val cteDef = CTERelationDef(aggregate, cteId)
+    // The first predicate can tell -0.0 from 0.0, so the combined predicate 
stays above the
+    // aggregate, which has a grouped-agg pandas UDF.
+    val plan = withTwoRefs(cteDef)(
+      out => EqualTo(Cast(out(0), StringType), Literal("-0.0")),
+      out => EqualTo(out(0), Literal(7.0)))
+
+    val afterPass1 = PushdownPredicatesAndPruneColumnsForCTEDef.apply(plan)
+
+    val consumed = 
addIsNotNullToRef(PushPredicateThroughNonJoin.apply(afterPass1),
+      cteId, Literal(7.0))
+    assert(theOnlyDef(consumed).child match {
+      case Filter(_, _: Aggregate) => true
+      case _ => false
+    }, "test setup failed: the filter was not kept above the aggregate")
+
+    assertSingleEnrichedTopFilter(consumed,
+      "previous push-down kept above the aggregate was not removed")
+  }
+
   test("rebuild removes the previous push-down pushed below a window") {
     val a = AttributeReference("a", IntegerType, nullable = true)()
     val b = AttributeReference("b", IntegerType, nullable = true)()
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregatesSuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregatesSuite.scala
index 40b3d36d4bfc..ac4fce160ac1 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregatesSuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/RemoveRedundantAggregatesSuite.scala
@@ -24,7 +24,7 @@ import 
org.apache.spark.sql.catalyst.expressions.Literal.TrueLiteral
 import org.apache.spark.sql.catalyst.plans.{LeftAnti, LeftSemi, PlanTest}
 import org.apache.spark.sql.catalyst.plans.logical.{Distinct, LocalRelation, 
LogicalPlan}
 import org.apache.spark.sql.catalyst.rules.RuleExecutor
-import org.apache.spark.sql.types.IntegerType
+import org.apache.spark.sql.types.{IntegerType, StringType}
 
 class RemoveRedundantAggregatesSuite extends PlanTest {
 
@@ -119,13 +119,15 @@ class RemoveRedundantAggregatesSuite extends PlanTest {
   }
 
   test("Remove redundant aggregate with non-deterministic lower") {
+    // An integer key: the lower aggregate normalizes a float/double key, so 
it would be kept.
+    val nondeterministic = (rand(0) * 10).cast(IntegerType)
     val query = relation
-      .groupBy($"a", $"c")($"a", rand(0) as "c")
+      .groupBy($"a", $"c")($"a", nondeterministic as "c")
       .groupBy($"a", $"c")($"a", $"c")
       .analyze
     val expected = relation
       .select($"a", $"b", rand(0) as "_nondeterministic")
-      .select($"a", $"_nondeterministic" as "c")
+      .select($"a", ($"_nondeterministic" * 10).cast(IntegerType) as "c")
       .groupBy($"a", $"c")($"a", $"c")
       .analyze
     val optimized = Optimize.execute(query)
@@ -176,17 +178,28 @@ class RemoveRedundantAggregatesSuite extends PlanTest {
   }
 
   test("Remove non-redundant aggregate - upper references non-deterministic 
non-grouping") {
+    // An integer c: an upper aggregate on a float/double key c would be kept.
+    val c = (($"a" + rand(0)) * 10).cast(IntegerType)
     val query = relation
-      .groupBy($"a")($"a", ($"a" + rand(0)) as "c")
+      .groupBy($"a")($"a", c as "c")
       .groupBy($"a", $"c")($"a", $"c")
       .analyze
     val expected = relation
-      .groupBy($"a")($"a", ($"a" + rand(0)) as "c")
+      .groupBy($"a")($"a", c as "c")
       .analyze
     val optimized = Optimize.execute(query)
     comparePlans(optimized, expected)
   }
 
+  test("SPARK-60150: Keep upper aggregate on a non-deterministic double key") {
+    // The upper aggregate normalizes the double key c, which a project would 
not.
+    val query = relation
+      .groupBy($"a")($"a", ($"a" + rand(0)) as "c")
+      .groupBy($"a", $"c")($"a", $"c")
+      .analyze
+    comparePlans(Optimize.execute(query), query)
+  }
+
   test("SPARK-36194: Remove aggregation from left semi/anti join if 
aggregation the same") {
     Seq(LeftSemi, LeftAnti).foreach { joinType =>
       val originalQuery = x.groupBy($"a", $"b")($"a", $"b")
@@ -256,6 +269,56 @@ class RemoveRedundantAggregatesSuite extends PlanTest {
     comparePlans(optimized, correctAnswer)
   }
 
+  test("SPARK-60150: Lower aggregate on a float key is kept only if the upper 
one reads it raw") {
+    val d = $"d".double
+    val a = $"a".int
+    val doubles = LocalRelation(d, a)
+    val lower = doubles.groupBy(d, a)(d, a)
+    // Grouped by directly, the key is normalized by the upper aggregate 
itself.
+    comparePlans(
+      Optimize.execute(lower.groupBy(d)(d, max(a).as("m")).analyze),
+      doubles.groupBy(d)(d, max(a).as("m")).analyze)
+    // Inside an aggregate function or a computed grouping expression, the 
upper aggregate would
+    // read the raw key without the lower one.
+    val inFunction = lower.groupBy(a)(a, max(d).as("m")).analyze
+    comparePlans(Optimize.execute(inFunction), inFunction)
+    val inGrouping = 
lower.groupBy(d.cast(StringType))(d.cast(StringType).as("s")).analyze
+    comparePlans(Optimize.execute(inGrouping), inGrouping)
+    // So would an output of the lower aggregate computed from the key.
+    val computed = doubles.groupBy(d)(d.cast(StringType).as("s"))
+    val readsComputed = computed.groupBy($"s")($"s").analyze
+    comparePlans(Optimize.execute(readsComputed), readsComputed)
+    // Removed when the upper aggregate does not read the float key.
+    comparePlans(
+      Optimize.execute(lower.groupBy(a)(a).analyze),
+      doubles.select(a).groupBy(a)(a).analyze)
+  }
+
+  test("SPARK-60150: Streaming aggregates on a float key are removed as 
before") {
+    val d = $"d".double
+    val a = $"a".int
+    val streaming = LocalRelation(Seq(d, a), isStreaming = true)
+    // Keeping them would add stateful operators, which existing checkpoints 
cannot restore.
+    comparePlans(
+      Optimize.execute(streaming.groupBy(d, a)(d, a).groupBy(a)(a, 
max(d).as("m")).analyze),
+      streaming.groupBy(a)(a, max(d).as("m")).analyze)
+    val lower = streaming.groupBy(d)(d, count(a).as("cnt"))
+    val cnt = lower.analyze.output(1)
+    comparePlans(Optimize.execute(lower.groupBy(d, cnt)(d, cnt).analyze), 
lower.analyze)
+  }
+
+  test("SPARK-60150: Keep group only aggregate that normalizes a float key") {
+    val d = $"d".double
+    val lower = LocalRelation(d, $"b".int).groupBy(d)(d, count($"b").as("cnt"))
+    val cnt = lower.analyze.output(1)
+    // The rule only sees that the upper aggregate outputs the float key 
normalized. It does not
+    // check whether the child already does, so it keeps the upper aggregate.
+    val originalQuery = lower.groupBy(d, cnt)(d, cnt).analyze
+    comparePlans(Optimize.execute(originalQuery), originalQuery)
+    // Removed when the float key is not output.
+    comparePlans(Optimize.execute(lower.groupBy(d, cnt)(cnt).analyze), 
lower.select(cnt).analyze)
+  }
+
   test("SPARK-36194: Child distinct keys are subsets and aggregateExpressions 
are foldable") {
     val originalQuery = x.groupBy($"a", $"b")($"a", $"b")
       .join(y, LeftSemi, Some("x.a".attr === "y.a".attr && "x.b".attr === 
"y.b".attr))
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/DataFrameAggregateSuite.scala 
b/sql/core/src/test/scala/org/apache/spark/sql/DataFrameAggregateSuite.scala
index 28e482ce2647..55360cd44987 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/DataFrameAggregateSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/DataFrameAggregateSuite.scala
@@ -27,10 +27,10 @@ import org.scalatest.matchers.must.Matchers.the
 import org.scalatestplus.scalacheck.ScalaCheckDrivenPropertyChecks
 
 import org.apache.spark.{SparkArithmeticException, SparkRuntimeException}
-import org.apache.spark.sql.catalyst.plans.logical.Expand
+import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, Expand}
 import org.apache.spark.sql.catalyst.util.AUTO_GENERATED_ALIAS
 import org.apache.spark.sql.errors.DataTypeErrors.toSQLId
-import org.apache.spark.sql.execution.WholeStageCodegenExec
+import org.apache.spark.sql.execution.{FileSourceScanExec, 
WholeStageCodegenExec}
 import org.apache.spark.sql.execution.adaptive.AdaptiveSparkPlanHelper
 import org.apache.spark.sql.execution.aggregate.{HashAggregateExec, 
ObjectHashAggregateExec, SortAggregateExec}
 import org.apache.spark.sql.execution.exchange.ShuffleExchangeExec
@@ -1271,6 +1271,148 @@ class DataFrameAggregateSuite extends SharedSparkSession
     )
   }
 
+  test("SPARK-60150: filter above an aggregate sees normalized float/double 
grouping keys") {
+    // Two rows, so that OptimizeOneRowPlan does not remove the aggregate.
+    val df = Seq(-0.0d, -0.0d).toDF("x")
+    val isNegZero = col("x").cast("string") === "-0.0"
+
+    // The aggregate outputs -0.0 as 0.0, for which the predicate is false.
+    checkAnswer(df.distinct().where(isNegZero), Nil)
+    checkAnswer(df.groupBy("x").count().where(isNegZero), Nil)
+    checkAnswer(df.dropDuplicates().where(isNegZero), Nil)
+    checkAnswer(df.intersectAll(df).where(isNegZero), Nil)
+    checkAnswer(df.except(Seq(1.0d).toDF("x")).where(isNegZero), Nil)
+    checkAnswer(Seq(-0.0f, -0.0f).toDF("x").distinct().where(isNegZero), Nil)
+    checkAnswer(
+      
df.select(struct(col("x")).as("s")).distinct().where(col("s.x").cast("string") 
=== "-0.0"),
+      Nil)
+
+    withTempView("t") {
+      df.createOrReplaceTempView("t")
+      checkAnswer(
+        sql("SELECT * FROM (SELECT DISTINCT x FROM t) WHERE cast(x AS STRING) 
= '-0.0'"),
+        Nil)
+      // An alias computed from the key is also evaluated on the normalized 
value.
+      checkAnswer(
+        sql("SELECT * FROM (SELECT cast(x AS STRING) AS s FROM t GROUP BY x) 
WHERE s = '-0.0'"),
+        Nil)
+    }
+
+    // Reading the raw key below the aggregate would split the group and 
change the count.
+    val zeros = Seq(-0.0d, 0.0d).toDF("x")
+    checkAnswer(
+      zeros.groupBy("x").count().where(col("x").cast("string") === "0.0"),
+      Row(0.0d, 2))
+    // Comparisons treat -0.0 and 0.0 as equal, so they are pushed down 
without normalization.
+    checkAnswer(zeros.groupBy("x").count().where(col("x") === 0.0d), Row(0.0d, 
2))
+  }
+
+  test("SPARK-60150: IN and NOT IN subqueries on a float/double grouping key") 
{
+    withTempView("t", "u") {
+      // Two rows, so that OptimizeOneRowPlan does not remove the aggregate. 
Nullable columns, so
+      // that NOT IN is planned as a null-aware anti join.
+      Seq(Some(0.0d), Some(0.0d)).toDF("x").createOrReplaceTempView("t")
+      Seq(Some(-0.0d)).toDF("y").createOrReplaceTempView("u")
+
+      // A float/double key is pushed unnormalized, and the join normalizes 
both sides.
+      checkAnswer(
+        sql("SELECT * FROM (SELECT DISTINCT x FROM t) WHERE x IN (SELECT y 
FROM u)"),
+        Row(0.0d))
+      checkAnswer(
+        sql("SELECT * FROM (SELECT DISTINCT x FROM t) WHERE x NOT IN (SELECT y 
FROM u)"),
+        Nil)
+      // A struct key is pushed normalized, so the subquery side of the join 
must be normalized too,
+      // so that 0.0 matches -0.0. The structs are nullable, like the columns 
above.
+      def struct(c: String): String = s"IF($c IS NULL, NULL, named_struct('a', 
$c))"
+      val structs = s"SELECT * FROM (SELECT DISTINCT ${struct("x")} AS s FROM 
t)"
+      val structY = s"(SELECT ${struct("y")} FROM u)"
+      checkAnswer(sql(s"$structs WHERE s IN $structY"), Row(Row(0.0d)))
+      checkAnswer(sql(s"$structs WHERE s NOT IN $structY"), Nil)
+    }
+  }
+
+  test("SPARK-60150: filter above a group only aggregate over at most one row 
or under LIMIT 1") {
+    withTempView("t") {
+      Seq(-0.0d).toDF("x").createOrReplaceTempView("t")
+      // The child has at most one row (OptimizeOneRowPlan). The aggregate 
outputs 0.0.
+      val oneRow =
+        "SELECT cast(x AS STRING) FROM (SELECT DISTINCT x FROM (SELECT x FROM 
t LIMIT 1))"
+      checkAnswer(sql(s"$oneRow WHERE cast(x AS STRING) = '0.0'"), Row("0.0"))
+      checkAnswer(sql(s"$oneRow WHERE cast(x AS STRING) = '-0.0'"), Nil)
+    }
+
+    // LIMIT 1 above the aggregate (LimitPushDown).
+    val distinct = Seq(-0.0d, -0.0d).toDF("x").distinct()
+    val isZero = col("x").cast("string") === "0.0"
+    
checkAnswer(distinct.where(isZero).limit(1).select(col("x").cast("string")), 
Row("0.0"))
+    assert(distinct.where(col("x").cast("string") === 
"-0.0").limit(1).collect().isEmpty)
+    val first = distinct.where(isZero).first().getDouble(0)
+    assert(first == 0.0d && 1.0d / first > 0, s"$first is not 0.0")
+  }
+
+  test("SPARK-60150: filter pushed below a DISTINCT that an upper aggregate 
makes redundant") {
+    withTempView("t") {
+      Seq(-0.0d, -0.0d).toDF("x").createOrReplaceTempView("t")
+      // The DISTINCT outputs 0.0, which the filter keeps and the upper 
aggregate groups as '0.0'.
+      checkAnswer(
+        sql("SELECT cast(x AS STRING) AS k FROM (SELECT DISTINCT x FROM t) " +
+          "WHERE cast(x AS STRING) = '0.0' GROUP BY 1"),
+        Row("0.0"))
+      // Without a filter too (SPARK-60093): the upper aggregate reads the 
DISTINCT's 0.0.
+      checkAnswer(
+        sql("SELECT cast(x AS STRING) AS k, max(x) AS m FROM " +
+          "(SELECT DISTINCT x FROM VALUES (-0.0D), (0.0D) t(x)) GROUP BY 1"),
+        Row("0.0", 0.0d))
+    }
+
+    withTempView("u") {
+      Seq((-0.0d, 1), (0.0d, 2)).toDF("d", "a").createOrReplaceTempView("u")
+      // The upper aggregate groups by the key directly and normalizes it 
itself, so the DISTINCT
+      // is removed, with and without a filter pushed below it.
+      val distinct = "(SELECT DISTINCT d, a FROM u)"
+      val grouped = sql(s"SELECT cast(d AS STRING), max(a) FROM $distinct 
GROUP BY d")
+      checkAnswer(grouped, Row("0.0", 2))
+      assert(grouped.queryExecution.optimizedPlan.collect { case a: Aggregate 
=> a }.size == 1)
+      // The filter reads the DISTINCT's normalized 0.0, so it keeps both rows 
for '0.0' and none
+      // for '-0.0'.
+      checkAnswer(
+        sql(s"SELECT cast(d AS STRING), max(a) FROM $distinct " +
+          "WHERE cast(d AS STRING) = '0.0' GROUP BY d"),
+        Row("0.0", 2))
+      checkAnswer(
+        sql(s"SELECT cast(d AS STRING), max(a) FROM $distinct " +
+          "WHERE cast(d AS STRING) = '-0.0' GROUP BY d"),
+        Nil)
+    }
+  }
+
+  test("SPARK-60150: filter pushed through two aggregates whose upper key is 
computed") {
+    withTempView("t") {
+      Seq(-0.0d, -0.0d).toDF("x").createOrReplaceTempView("t")
+      // The lower aggregate computes pow on the normalized key 0.0, so k is 
Infinity.
+      checkAnswer(
+        sql("SELECT * FROM (SELECT k, count(*) AS c FROM " +
+          "(SELECT pow(x, -1) AS k FROM t GROUP BY x) GROUP BY k) " +
+          "WHERE cast(k AS STRING) = 'Infinity'"),
+        Row(Double.PositiveInfinity, 1L))
+    }
+  }
+
+  test("SPARK-60150: comparisons on a FLOAT grouping key are pushed to the 
data source") {
+    withTempPath { path =>
+      Seq(-0.0f, 1.0f, 2.0f).toDF("f").write.parquet(path.getCanonicalPath)
+      val df = spark.read.parquet(path.getCanonicalPath)
+        .groupBy("f").count().where(col("f") > 1.5)
+      checkAnswer(df, Row(2.0f, 1L))
+      // The FLOAT key is compared with a DOUBLE value through a widening 
cast. That comparison is
+      // pushed below the aggregate without normalizing the key, so the scan 
can still use it.
+      val pushedFilters = df.queryExecution.sparkPlan.collectFirst {
+        case scan: FileSourceScanExec => scan.metadata("PushedFilters")
+      }
+      assert(pushedFilters.exists(_.contains("GreaterThan(f,1.5)")), 
pushedFilters)
+    }
+  }
+
   test("SPARK-57329: mode normalizes -0.0/0.0 in the frequency buffer") {
     checkAnswer(
       Seq(0.0d, 0.0d, -0.0d, -0.0d, 9.0d, 9.0d, 
9.0d).toDF("d").select(expr("mode(d)")),
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/python/PythonUDFSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/python/PythonUDFSuite.scala
index 43dee0602039..f600711f12a3 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/python/PythonUDFSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/python/PythonUDFSuite.scala
@@ -126,6 +126,29 @@ class PythonUDFSuite extends SharedSparkSession {
     
assert(df.agg(pandasTestUDF(df("id"))).schema.fieldNames.exists(_.startsWith(udfName)))
   }
 
+  test("SPARK-60150: filter on a float key above a grouped-agg pandas UDF 
aggregate") {
+    assume(shouldTestPandasUDFs)
+    val pandasTestUDF = TestGroupedAggPandasUDF(name = "pandas_udf")
+    // The aggregate outputs the raw key -0.0, and the filter pushed below it 
must read it raw.
+    val grouped = Seq(-0.0d, 
-0.0d).toDF("x").groupBy("x").agg(pandasTestUDF(col("x")))
+    assert(grouped.where(col("x").cast("string") === "-0.0").collect().length 
== 1)
+    assert(grouped.where(col("x").cast("string") === "0.0").collect().isEmpty)
+
+    // Column pruning removes an aggregate function after the filter 
push-down, which keeps the
+    // filter above the aggregate. With count(*) pruned, the aggregate outputs 
the raw key -0.0,
+    // which the filter keeps.
+    val df = Seq((-0.0d, 1.0d), (-0.0d, 2.0d)).toDF("x", "v")
+    val mixed = df.groupBy("x").agg(pandasTestUDF(col("v")).as("p"), 
count("*").as("c"))
+      .where(col("x").cast("string") === "-0.0").select("x", "p")
+    assert(mixed.collect().length == 1)
+    // With the pandas UDF pruned, the aggregate is group-only and outputs the 
normalized key 0.0,
+    // which the filter then reads, pushed below it in normalized form.
+    def keys(key: String): Array[String] = 
df.groupBy("x").agg(pandasTestUDF(col("v")).as("p"))
+      .where(col("x").cast("string") === 
key).select(col("x").cast("string")).as[String].collect()
+    assert(keys("0.0").toSeq == Seq("0.0"))
+    assert(keys("-0.0").isEmpty)
+  }
+
   test("SPARK-27052: Python UDF in a higher order function lambda") {
     assume(shouldTestPythonUDFs)
     // SPARK-48706 originally rejected this. `ExtractPythonUDFFromLambda` now 
rewrites it so the
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/streaming/StreamingAggregationSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/streaming/StreamingAggregationSuite.scala
index a31491407efc..78c0d3707576 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/streaming/StreamingAggregationSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/streaming/StreamingAggregationSuite.scala
@@ -1303,6 +1303,27 @@ class StreamingAggregationSuite extends 
StateStoreMetricsTest with Assertions {
     // Append actually guarantees -- is covered by the CheckLastBatch 
assertions above.
   }
 
+  test("SPARK-60150: streaming aggregations on float/double keys keep their 
stateful operators") {
+    // As before, a DISTINCT below a duplicate-insensitive aggregation is 
removed, also with a
+    // filter on the key, which stays below it. So these queries keep one 
stateful operator and can
+    // restart from existing checkpoints.
+    for (where <- Seq("", "WHERE cast(d AS STRING) = '0.0' ")) 
withTempView("s") {
+      val input = MemoryStream[(Long, Double)]
+      input.toDF().toDF("t", "d")
+        .withColumn("ts", timestamp_seconds($"t"))
+        .withWatermark("ts", "10 seconds")
+        .createOrReplaceTempView("s")
+      val query = spark.sql("SELECT w.start AS start, max(d) AS m FROM " +
+        s"(SELECT DISTINCT window(ts, '1 minute') AS w, d FROM s) 
${where}GROUP BY w")
+      testStream(query, Append)(
+        AddData(input, (0L, -0.0d), (1L, 0.0d)),
+        ProcessAllAvailable(),
+        AssertOnQuery { q =>
+          q.lastExecution.executedPlan.collect { case s: StateStoreSaveExec => 
s }.size == 1
+        })
+    }
+  }
+
   // The micro-batch aggregation operator (stateStoreSave) and the streamline 
operator share the
   // same StreamingAggregationStateManager and state format, so a checkpoint 
written by one can be
   // read by the other. The operator-name check in IncrementalExecution must 
therefore treat the


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

Reply via email to