This is an automated email from the ASF dual-hosted git repository.
szehon-ho pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new e6738fab2a2c [SPARK-54833][SQL] Fix Mode sql() to display correct sort
direction for WITHIN GROUP
e6738fab2a2c is described below
commit e6738fab2a2ccde3182991b87af307ddd69f4fac
Author: Xiaoxuan Li <[email protected]>
AuthorDate: Thu Jul 2 12:13:09 2026 -0700
[SPARK-54833][SQL] Fix Mode sql() to display correct sort direction for
WITHIN GROUP
### What changes were proposed in this pull request?
Fix the `Mode.sql()` method to correctly display the sort direction in the
generated SQL string for `WITHIN GROUP (ORDER BY ...)` clauses.
The `reverseOpt` field in `Mode` was being mapped to the wrong SQL output:
- `reverse=true` (Ascending) was incorrectly producing `DESC`
- `reverse=false` (Descending) was incorrectly omitting `DESC`
The fix swaps the two branches so the SQL output matches the original
user-specified sort direction.
### Why are the changes needed?
`mode() WITHIN GROUP (ORDER BY col)` was displaying as `ORDER BY col DESC`
in schema column names and analyzer output, and vice versa. This caused
incorrect schema names and broken SQL roundtrips.
### Does this PR introduce _any_ user-facing change?
Yes. Schema column names for `mode() WITHIN GROUP (ORDER BY ...)` now
correctly reflect the sort direction.
### How was this patch tested?
- Unit test in `AggregateExpressionSuite` verifying `sql()` output for both
ASC and DESC.
- Integration test in `DataFrameAggregateSuite` verifying schema column
names and SQL roundtrip.
- Updated SQL golden files (`mode.sql.out` analyzer-results and results).
### Was this patch authored or co-authored using generative AI tooling?
Yes, co-authored with Kiro.
Closes #54841 from xiaoxuandev/fix-mode-sort.
Authored-by: Xiaoxuan Li <[email protected]>
Signed-off-by: Szehon Ho <[email protected]>
(cherry picked from commit 95983793f44d75ed6e10f176b305b566b1c82db0)
Signed-off-by: Szehon Ho <[email protected]>
---
python/pyspark/sql/functions/builtin.py | 10 +++---
.../sql/catalyst/expressions/aggregate/Mode.scala | 4 +--
.../aggregate/AggregateExpressionSuite.scala | 25 +++++++++++--
.../sql-tests/analyzer-results/mode.sql.out | 42 +++++++++++-----------
.../test/resources/sql-tests/results/mode.sql.out | 26 +++++++-------
.../apache/spark/sql/DataFrameAggregateSuite.scala | 29 +++++++++++++++
6 files changed, 93 insertions(+), 43 deletions(-)
diff --git a/python/pyspark/sql/functions/builtin.py
b/python/pyspark/sql/functions/builtin.py
index d10fed1efa3f..06cfeb709793 100644
--- a/python/pyspark/sql/functions/builtin.py
+++ b/python/pyspark/sql/functions/builtin.py
@@ -1125,11 +1125,11 @@ def mode(col: "ColumnOrName", deterministic: bool =
False) -> Column:
+---------+
>>> df.select(sf.mode("col", True)).show()
- +---------------------------------------+
- |mode() WITHIN GROUP (ORDER BY col DESC)|
- +---------------------------------------+
- | -10|
- +---------------------------------------+
+ +----------------------------------+
+ |mode() WITHIN GROUP (ORDER BY col)|
+ +----------------------------------+
+ | -10|
+ +----------------------------------+
"""
from pyspark.sql.classic.column import _to_java_column
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Mode.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Mode.scala
index 30e00ac68004..d4047f78f8d9 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Mode.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/Mode.scala
@@ -190,9 +190,9 @@ case class Mode(
reverseOpt.map {
reverse =>
if (reverse) {
- s"$prettyName() WITHIN GROUP (ORDER BY ${child.sql} DESC)"
- } else {
s"$prettyName() WITHIN GROUP (ORDER BY ${child.sql})"
+ } else {
+ s"$prettyName() WITHIN GROUP (ORDER BY ${child.sql} DESC)"
}
}.getOrElse(super.sql(isDistinct))
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/AggregateExpressionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/AggregateExpressionSuite.scala
index 15c85b1f83b2..c9383754acf4 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/AggregateExpressionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/AggregateExpressionSuite.scala
@@ -18,10 +18,11 @@
package org.apache.spark.sql.catalyst.expressions.aggregate
import org.apache.spark.SparkFunSuite
-import org.apache.spark.sql.catalyst.analysis.{TypeCheckResult,
UnresolvedAttribute}
+import org.apache.spark.sql.catalyst.analysis.{TypeCheckResult,
UnresolvedAttribute, UnresolvedWithinGroup}
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
-import org.apache.spark.sql.catalyst.expressions.{Add, AttributeSet, Literal}
+import org.apache.spark.sql.catalyst.expressions.{Add, Ascending,
AttributeReference, AttributeSet, Descending, Literal, SortOrder}
import org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
+import org.apache.spark.sql.types.IntegerType
class AggregateExpressionSuite extends SparkFunSuite {
@@ -109,6 +110,26 @@ class AggregateExpressionSuite extends SparkFunSuite {
TypeCheckResult.TypeCheckSuccess)
}
+ test("SPARK-54833: sql() should display correct sort direction for WITHIN
GROUP") {
+ val col = AttributeReference("col", IntegerType)()
+
+ val modeAsc = Mode(UnresolvedWithinGroup).withOrderingWithinGroup(
+ Seq(SortOrder(col, Ascending))
+ ).asInstanceOf[Mode]
+ assert(modeAsc.reverseOpt === Some(true))
+ val ascSql = modeAsc.sql(isDistinct = false)
+ assert(!ascSql.contains("DESC"),
+ s"MODE() WITHIN GROUP (ORDER BY col ASC) should not contain DESC, but
got: $ascSql")
+
+ val modeDesc = Mode(UnresolvedWithinGroup).withOrderingWithinGroup(
+ Seq(SortOrder(col, Descending))
+ ).asInstanceOf[Mode]
+ assert(modeDesc.reverseOpt === Some(false))
+ val descSql = modeDesc.sql(isDistinct = false)
+ assert(descSql.contains("DESC"),
+ s"MODE() WITHIN GROUP (ORDER BY col DESC) should contain DESC, but got:
$descSql")
+ }
+
test("test regr_intercept input types") {
assert(RegrIntercept(Literal("a"), Literal(1)).checkInputDataTypes() ==
DataTypeMismatch(
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/mode.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/mode.sql.out
index 8028c344140f..6cb4a1810bc2 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/mode.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/mode.sql.out
@@ -108,7 +108,7 @@ Aggregate [mode(col#x, 0, 0, None) AS mode(col)#x]
-- !query
SELECT mode(col, true) FROM VALUES (-10), (0), (10) AS tab(col)
-- !query analysis
-Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#x]
+Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
col)#x]
+- SubqueryAlias tab
+- LocalRelation [col#x]
@@ -199,7 +199,7 @@ Aggregate [mode(col#x, 0, 0, None) AS mode(col)#x]
-- !query
SELECT mode(col, true) FROM VALUES (map(1, 'a')) AS tab(col)
-- !query analysis
-Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#x]
+Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
col)#x]
+- SubqueryAlias tab
+- LocalRelation [col#x]
@@ -210,7 +210,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY col DESC)
FROM VALUES (null), (null), (null) AS tab(col)
-- !query analysis
-Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#x, mode(col#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY col)#x]
+Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
col)#x, mode(col#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#x]
+- SubqueryAlias tab
+- LocalRelation [col#x]
@@ -222,7 +222,7 @@ SELECT
FROM basic_pays
WHERE salary > 20000
-- !query analysis
-Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER
BY salary)#x]
+Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x]
+- Filter (salary#x > 20000)
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x, salary#x])
@@ -238,7 +238,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY salary DESC)
FROM basic_pays
-- !query analysis
-Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER
BY salary)#x]
+Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x, salary#x])
+- Project [cast(employee_name#x as string) AS employee_name#x,
cast(department#x as string) AS department#x, cast(salary#x as int) AS salary#x]
@@ -253,7 +253,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY salary) FILTER (WHERE salary > 10000)
FROM basic_pays
-- !query analysis
-Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x, mode(salary#x, 0, 0, Some(true)) FILTER (WHERE (salary#x >
10000)) AS mode() WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE (salary >
10000))#x]
+Aggregate [mode(salary#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
salary)#x, mode(salary#x, 0, 0, Some(true)) FILTER (WHERE (salary#x > 10000))
AS mode() WITHIN GROUP (ORDER BY salary) FILTER (WHERE (salary > 10000))#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x, salary#x])
+- Project [cast(employee_name#x as string) AS employee_name#x,
cast(department#x as string) AS department#x, cast(salary#x as int) AS salary#x]
@@ -272,7 +272,7 @@ GROUP BY department
ORDER BY department
-- !query analysis
Sort [department#x ASC NULLS FIRST], true
-+- Aggregate [department#x], [department#x, mode(salary#x, 0, 0, Some(true))
AS mode() WITHIN GROUP (ORDER BY salary DESC)#x, mode(salary#x, 0, 0,
Some(true)) FILTER (WHERE (salary#x > 10000)) AS mode() WITHIN GROUP (ORDER BY
salary DESC) FILTER (WHERE (salary > 10000))#x]
++- Aggregate [department#x], [department#x, mode(salary#x, 0, 0, Some(true))
AS mode() WITHIN GROUP (ORDER BY salary)#x, mode(salary#x, 0, 0, Some(true))
FILTER (WHERE (salary#x > 10000)) AS mode() WITHIN GROUP (ORDER BY salary)
FILTER (WHERE (salary > 10000))#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x, salary#x])
+- Project [cast(employee_name#x as string) AS employee_name#x,
cast(department#x as string) AS department#x, cast(salary#x as int) AS salary#x]
@@ -291,9 +291,9 @@ FROM basic_pays
ORDER BY salary
-- !query analysis
Sort [salary#x ASC NULLS FIRST], true
-+- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x]
- +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary
DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x]
- +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), unboundedfollowing$())) AS mode() WITHIN GROUP (ORDER BY
salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x], [department#x]
++- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x]
+ +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary)
OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)#x]
+ +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), unboundedfollowing$())) AS mode() WITHIN GROUP (ORDER BY
salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x], [department#x]
+- Project [employee_name#x, department#x, salary#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x,
salary#x])
@@ -313,9 +313,9 @@ FROM basic_pays
ORDER BY salary
-- !query analysis
Sort [salary#x ASC NULLS FIRST], true
-+- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ORDER BY salary ASC NULLS
FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
- +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ORDER BY salary ASC NULLS
FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ORDER BY salary ASC NULLS
FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
- +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, salary#x ASC NULLS FIRST,
specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS
mode() WITHIN GROUP (ORDER BY salary DESC) OVER (PARTITION BY department ORDER
BY salary ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW)#x], [department#x], [salary#x ASC NULLS FIRST]
++- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ORDER BY salary ASC NULLS FIRST
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+ +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ORDER BY salary ASC NULLS FIRST
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ORDER BY salary ASC NULLS FIRST
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x]
+ +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, salary#x ASC NULLS FIRST,
specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS
mode() WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department ORDER BY
salary ASC NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)#x],
[department#x], [salary#x ASC NULLS FIRST]
+- Project [employee_name#x, department#x, salary#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x,
salary#x])
@@ -335,9 +335,9 @@ FROM basic_pays
ORDER BY salary
-- !query analysis
Sort [salary#x ASC NULLS FIRST], true
-+- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING)#x]
- +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary DESC) OVER
(PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)#x]
- +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), 1)) AS mode() WITHIN GROUP (ORDER BY salary DESC) OVER
(PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)#x],
[department#x]
++- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING)#x]
+ +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary) OVER
(PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)#x]
+ +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), 1)) AS mode() WITHIN GROUP (ORDER BY salary) OVER
(PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)#x],
[department#x]
+- Project [employee_name#x, department#x, salary#x]
+- SubqueryAlias basic_pays
+- View (`basic_pays`, [employee_name#x, department#x,
salary#x])
@@ -359,9 +359,9 @@ WINDOW w AS (PARTITION BY department)
ORDER BY salary
-- !query analysis
Sort [salary#x ASC NULLS FIRST], true
-+- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x]
- +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary
DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x]
- +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), unboundedfollowing$())) AS mode() WITHIN GROUP (ORDER BY
salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x], [department#x]
++- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x]
+ +- Project [employee_name#x, department#x, salary#x, mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x, mode() WITHIN GROUP (ORDER BY salary)
OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)#x]
+ +- Window [mode(salary#x, 0, 0, Some(true))
windowspecdefinition(department#x, specifiedwindowframe(RowFrame,
unboundedpreceding$(), unboundedfollowing$())) AS mode() WITHIN GROUP (ORDER BY
salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x], [department#x]
+- Project [employee_name#x, department#x, salary#x]
+- Filter (salary#x > 8900)
+- SubqueryAlias basic_pays
@@ -483,7 +483,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY dt DESC)
FROM intervals
-- !query analysis
-Aggregate [mode(dt#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY dt
DESC)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY dt)#x]
+Aggregate [mode(dt#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY
dt)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY dt
DESC)#x]
+- SubqueryAlias intervals
+- View (`intervals`, [k#x, dt#x, ym#x, dt2#x])
+- Project [cast(k#x as int) AS k#x, cast(dt#x as interval month) AS
dt#x, cast(ym#x as interval second) AS ym#x, cast(dt2#x as interval minute) AS
dt2#x]
@@ -502,7 +502,7 @@ GROUP BY k
ORDER BY k
-- !query analysis
Sort [k#x ASC NULLS FIRST], true
-+- Aggregate [k#x], [k#x, mode(ym#x, 0, 0, Some(true)) AS mode() WITHIN GROUP
(ORDER BY ym DESC)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP
(ORDER BY dt)#x]
++- Aggregate [k#x], [k#x, mode(ym#x, 0, 0, Some(true)) AS mode() WITHIN GROUP
(ORDER BY ym)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
dt DESC)#x]
+- SubqueryAlias intervals
+- View (`intervals`, [k#x, dt#x, ym#x, dt2#x])
+- Project [cast(k#x as int) AS k#x, cast(dt#x as interval month) AS
dt#x, cast(ym#x as interval second) AS ym#x, cast(dt2#x as interval minute) AS
dt2#x]
diff --git a/sql/core/src/test/resources/sql-tests/results/mode.sql.out
b/sql/core/src/test/resources/sql-tests/results/mode.sql.out
index 70f253066d4f..9aec71d2af83 100644
--- a/sql/core/src/test/resources/sql-tests/results/mode.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/mode.sql.out
@@ -85,7 +85,7 @@ struct<mode(col):int>
-- !query
SELECT mode(col, true) FROM VALUES (-10), (0), (10) AS tab(col)
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY col DESC):int>
+struct<mode() WITHIN GROUP (ORDER BY col):int>
-- !query output
-10
@@ -182,7 +182,7 @@ struct<mode(col):map<int,string>>
-- !query
SELECT mode(col, true) FROM VALUES (map(1, 'a')) AS tab(col)
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY col DESC):map<int,string>>
+struct<mode() WITHIN GROUP (ORDER BY col):map<int,string>>
-- !query output
{1:"a"}
@@ -193,7 +193,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY col DESC)
FROM VALUES (null), (null), (null) AS tab(col)
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY col DESC):void,mode() WITHIN GROUP (ORDER
BY col):void>
+struct<mode() WITHIN GROUP (ORDER BY col):void,mode() WITHIN GROUP (ORDER BY
col DESC):void>
-- !query output
NULL NULL
@@ -205,7 +205,7 @@ SELECT
FROM basic_pays
WHERE salary > 20000
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary):int>
+struct<mode() WITHIN GROUP (ORDER BY salary):int,mode() WITHIN GROUP (ORDER BY
salary DESC):int>
-- !query output
NULL NULL
@@ -216,7 +216,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY salary DESC)
FROM basic_pays
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary):int>
+struct<mode() WITHIN GROUP (ORDER BY salary):int,mode() WITHIN GROUP (ORDER BY
salary DESC):int>
-- !query output
5186 11798
@@ -227,7 +227,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY salary) FILTER (WHERE salary > 10000)
FROM basic_pays
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary DESC) FILTER (WHERE (salary > 10000)):int>
+struct<mode() WITHIN GROUP (ORDER BY salary):int,mode() WITHIN GROUP (ORDER BY
salary) FILTER (WHERE (salary > 10000)):int>
-- !query output
5186 10449
@@ -241,7 +241,7 @@ FROM basic_pays
GROUP BY department
ORDER BY department
-- !query schema
-struct<department:string,mode() WITHIN GROUP (ORDER BY salary DESC):int,mode()
WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE (salary > 10000)):int>
+struct<department:string,mode() WITHIN GROUP (ORDER BY salary):int,mode()
WITHIN GROUP (ORDER BY salary) FILTER (WHERE (salary > 10000)):int>
-- !query output
Accounting 6627 11472
IT 5186 NULL
@@ -258,7 +258,7 @@ SELECT
FROM basic_pays
ORDER BY salary
-- !query schema
-struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING):int>
+struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING):int>
-- !query output
Leslie Thompson IT 5186 5186
Anthony Bow Accounting 6627 6627
@@ -288,7 +288,7 @@ SELECT
FROM basic_pays
ORDER BY salary
-- !query schema
-struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ORDER BY salary ASC NULLS
FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):int>
+struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ORDER BY salary ASC NULLS FIRST
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):int>
-- !query output
Leslie Thompson IT 5186 5186
Anthony Bow Accounting 6627 6627
@@ -318,7 +318,7 @@ SELECT
FROM basic_pays
ORDER BY salary
-- !query schema
-struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING):int>
+struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING):int>
-- !query output
Leslie Thompson IT 5186 5186
Anthony Bow Accounting 6627 6627
@@ -350,7 +350,7 @@ WHERE salary > 8900
WINDOW w AS (PARTITION BY department)
ORDER BY salary
-- !query schema
-struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary DESC) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING):int>
+struct<employee_name:string,department:string,salary:int,mode() WITHIN GROUP
(ORDER BY salary) OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING):int>
-- !query output
Jeff Firrelli Accounting 8992 8992
Julie Firrelli Sales 9181 9181
@@ -466,7 +466,7 @@ SELECT
mode() WITHIN GROUP (ORDER BY dt DESC)
FROM intervals
-- !query schema
-struct<mode() WITHIN GROUP (ORDER BY dt DESC):interval month,mode() WITHIN
GROUP (ORDER BY dt):interval month>
+struct<mode() WITHIN GROUP (ORDER BY dt):interval month,mode() WITHIN GROUP
(ORDER BY dt DESC):interval month>
-- !query output
0-10 1-8
@@ -480,7 +480,7 @@ FROM intervals
GROUP BY k
ORDER BY k
-- !query schema
-struct<k:int,mode() WITHIN GROUP (ORDER BY ym DESC):interval second,mode()
WITHIN GROUP (ORDER BY dt):interval month>
+struct<k:int,mode() WITHIN GROUP (ORDER BY ym):interval second,mode() WITHIN
GROUP (ORDER BY dt DESC):interval month>
-- !query output
0 0 00:00:00.000000000 3-4
1 0 00:00:10.000000000 1-8
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 b18734d12e70..b11a14efb730 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
@@ -4906,6 +4906,35 @@ class DataFrameAggregateSuite extends SharedSparkSession
assert(unionRow.getAs[Double]("union_estimate") >= 5.0)
}
+ test("SPARK-54833: mode() WITHIN GROUP sql() roundtrip - sort direction
correctness") {
+ val df = spark.sql(
+ """SELECT
+ | mode() WITHIN GROUP (ORDER BY col) AS mode_asc,
+ | mode() WITHIN GROUP (ORDER BY col DESC) AS mode_desc
+ |FROM VALUES (10), (20), (20), (30), (30) AS tab(col)""".stripMargin)
+ checkAnswer(df, Row(20, 30))
+
+ // Without aliases, verify schema column names reflect the correct sort
direction
+ val df2 = spark.sql(
+ """SELECT
+ | mode() WITHIN GROUP (ORDER BY col),
+ | mode() WITHIN GROUP (ORDER BY col DESC)
+ |FROM VALUES (10), (10), (20), (20) AS tab(col)""".stripMargin)
+
+ val colNames = df2.schema.fieldNames
+ assert(!colNames(0).toUpperCase(Locale.ROOT).contains("DESC"),
+ s"ASC mode column name should not contain DESC, got: ${colNames(0)}")
+ assert(colNames(1).toUpperCase(Locale.ROOT).contains("DESC"),
+ s"DESC mode column name should contain DESC, got: ${colNames(1)}")
+ checkAnswer(df2, Row(10, 20))
+
+ // SQL roundtrip
+ df2.createOrReplaceTempView("mode_result")
+ val roundtrip = spark.sql(
+ s"SELECT `${colNames(0)}`, `${colNames(1)}` FROM mode_result")
+ checkAnswer(roundtrip, Row(10, 20))
+ }
+
test("SPARK-54179: tuple_sketch with null values") {
val df = Seq((1, Some(10)), (2, Some(20)), (3, None)).toDF("key",
"summary")
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
