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]