[SPARK-48006] add SortOrder for window function which has no orderSpec

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Target Version/s: 4.0.0
• Assignee: Unassigned
• Reporter: guihuawen
• Created: 26/Apr/24 09:27
• Updated: Yesterday 06:25

Description

I am doing Hive SQL to switch to Spark SQL.

 

In Hive SQL

 

hive> explain select *,row_number() over (partition by day) rn from testdb.zeropart_db;

OK
Explain

 

In Spark SQL

spark-sql> explain select *,row_number() over (partition by age ) rn  from testdb.zeropart_db;

plan

== Physical Plan ==

org.apache.spark.sql.AnalysisException: Window function row_number() requires window to be ordered, please add ORDER BY clause. For example SELECT row_number()(value_expr) OVER (PARTITION BY window_partition ORDER BY window_ordering) from table

Time taken: 0.172 seconds, Fetched 1 row(s)

 

For better compatibility with migration. For better compatibility with migration, new parameters are added to ensure compatibility with the same behavior as Hive SQL

 

Issue Links

• links to: GitHub Pull Request #46243