[SPARK-30335] Clarify behavior of FIRST and LAST without OVER caluse.

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.0
• Fix Version/s: 3.1.0
• Component/s: Documentation, SQL
• Assignee: Hyukjin Kwon
• Reporter: xqods9o5ekm3
• Created: 23/Dec/19 19:03
• Updated: 12/Dec/22 18:10
• Resolved: 17/Mar/20 08:29

Description

Unlike many databases, Spark SQL allows usage of FIRST and LAST in non-analytic contexts.

 

At the moment FIRST

 

> first(expr[, isIgnoreNull]) - Returns the first value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.

 

and LAST

 

> last(expr[, isIgnoreNull]) - Returns the last value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.

 

descriptions, suggest that their behavior is deterministic and many users assume that it return specific values for example when query
 

SELECT first(foo)
FROM (
    SELECT * FROM table ORDER BY bar
)

That however doesn't seem to be the case.

To make situation worse, it seems to work (for example on small samples in local mode).

Issue Links

• links to: GitHub Pull Request #27099