[SPARK-16721] Lead/lag needs to respect nulls

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.1, 2.1.0
• Component/s: SQL
• Labels: correctness
• Target Version/s: 2.0.1, 2.1.0
• Assignee: Yin Huai
• Reporter: Yin Huai
• Created: 25/Jul/16 23:14
• Updated: 31/Aug/16 21:38
• Resolved: 26/Jul/16 03:59

Description

Seems 2.0.0 changes the behavior of lead and lag to ignore nulls. This PR is changing the behavior back to 1.6's behavior, which is respecting nulls.

For example

SELECT
b,
lag(a, 1, 321) OVER (ORDER BY b) as lag,
lead(a, 1, 321) OVER (ORDER BY b) as lead
FROM (SELECT cast(null as int) as a, 1 as b
UNION ALL
select cast(null as int) as id, 2 as b) tmp

This query should return

+---+----+----+
|  b| lag|lead|
+---+----+----+
|  1| 321|null|
|  2|null| 321|
+---+----+----+

instead of

+---+---+----+
|  b|lag|lead|
+---+---+----+
|  1|321| 321|
|  2|321| 321|
+---+---+----+

Issue Links

• links to: [Github] Pull Request #14284 (yhuai)