[SPARK-28064] Order by does not accept a call to rank()

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Dylan Guedes
• Created: 16/Jun/19 12:02
• Updated: 25/Nov/20 10:27

Description

Currently in Spark, we can't use a call to `rank()` in a order by; we need to first rename the rank column to, for instance, `r` and then, use `order by r`. For example:
This does not work:

 SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;

However, this one does:

 SELECT depname, empno, salary, rank() OVER w as r FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY r;

By the way, I took this one from Postgres behavior: postgres accept both ways.