This is an automated email from the ASF dual-hosted git repository.
dongjoon-hyun 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 86d088cb9dad Revert "[SPARK-54833][SQL] Fix Mode sql() to display
correct sort direction for WITHIN GROUP"
86d088cb9dad is described below
commit 86d088cb9dadfd19b364663a3851b05914eb28c9
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Thu Jul 2 22:18:48 2026 -0700
Revert "[SPARK-54833][SQL] Fix Mode sql() to display correct sort direction
for WITHIN GROUP"
This reverts commit e6738fab2a2ccde3182991b87af307ddd69f4fac.
---
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, 43 insertions(+), 93 deletions(-)
diff --git a/python/pyspark/sql/functions/builtin.py
b/python/pyspark/sql/functions/builtin.py
index 06cfeb709793..d10fed1efa3f 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)|
- +----------------------------------+
- | -10|
- +----------------------------------+
+ +---------------------------------------+
+ |mode() WITHIN GROUP (ORDER BY col DESC)|
+ +---------------------------------------+
+ | -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 d4047f78f8d9..30e00ac68004 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})"
- } else {
s"$prettyName() WITHIN GROUP (ORDER BY ${child.sql} DESC)"
+ } else {
+ s"$prettyName() WITHIN GROUP (ORDER BY ${child.sql})"
}
}.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 c9383754acf4..15c85b1f83b2 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,11 +18,10 @@
package org.apache.spark.sql.catalyst.expressions.aggregate
import org.apache.spark.SparkFunSuite
-import org.apache.spark.sql.catalyst.analysis.{TypeCheckResult,
UnresolvedAttribute, UnresolvedWithinGroup}
+import org.apache.spark.sql.catalyst.analysis.{TypeCheckResult,
UnresolvedAttribute}
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
-import org.apache.spark.sql.catalyst.expressions.{Add, Ascending,
AttributeReference, AttributeSet, Descending, Literal, SortOrder}
+import org.apache.spark.sql.catalyst.expressions.{Add, AttributeSet, Literal}
import org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
-import org.apache.spark.sql.types.IntegerType
class AggregateExpressionSuite extends SparkFunSuite {
@@ -110,26 +109,6 @@ 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 6cb4a1810bc2..8028c344140f 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)#x]
+Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#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)#x]
+Aggregate [mode(col#x, 0, 0, Some(true)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#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)#x, mode(col#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY col
DESC)#x]
+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]
+- 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)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x]
+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]
+- 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)#x, mode(salary#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
salary DESC)#x]
+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]
+- 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)#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]
+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]
+- 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)#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]
++- 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]
+- 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) 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, 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]
+- 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) 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, 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]
+- 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) 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, 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]
+- 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) 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, 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]
+- 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)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY dt
DESC)#x]
+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]
+- 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)#x, mode(dt#x, 0, 0, Some(false)) AS mode() WITHIN GROUP (ORDER BY
dt DESC)#x]
++- 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]
+- 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 9aec71d2af83..70f253066d4f 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):int>
+struct<mode() WITHIN GROUP (ORDER BY col DESC):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):map<int,string>>
+struct<mode() WITHIN GROUP (ORDER BY col DESC):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):void,mode() WITHIN GROUP (ORDER BY
col DESC):void>
+struct<mode() WITHIN GROUP (ORDER BY col DESC):void,mode() WITHIN GROUP (ORDER
BY col):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):int,mode() WITHIN GROUP (ORDER BY
salary DESC):int>
+struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary):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):int,mode() WITHIN GROUP (ORDER BY
salary DESC):int>
+struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary):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):int,mode() WITHIN GROUP (ORDER BY
salary) FILTER (WHERE (salary > 10000)):int>
+struct<mode() WITHIN GROUP (ORDER BY salary DESC):int,mode() WITHIN GROUP
(ORDER BY salary DESC) 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):int,mode()
WITHIN GROUP (ORDER BY salary) FILTER (WHERE (salary > 10000)):int>
+struct<department:string,mode() WITHIN GROUP (ORDER BY salary DESC):int,mode()
WITHIN GROUP (ORDER BY salary DESC) 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) 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 DESC) 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) 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 DESC) 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) 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 DESC) 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) 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 DESC) 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):interval month,mode() WITHIN GROUP
(ORDER BY dt DESC):interval month>
+struct<mode() WITHIN GROUP (ORDER BY dt DESC):interval month,mode() WITHIN
GROUP (ORDER BY dt):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):interval second,mode() WITHIN
GROUP (ORDER BY dt DESC):interval month>
+struct<k:int,mode() WITHIN GROUP (ORDER BY ym DESC):interval second,mode()
WITHIN GROUP (ORDER BY dt):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 b11a14efb730..b18734d12e70 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,35 +4906,6 @@ 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]
