[SPARK-41668] DECODE function returns wrong results when passed NULL

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.0, 3.2.1, 3.3.0, 3.2.2, 3.3.1
• Fix Version/s: 3.2.3, 3.3.2, 3.4.0
• Component/s: Spark Core
• Assignee: Gengliang Wang
• Reporter: Serge Rielau
• Shepherd: Gengliang Wang
• Created: 21/Dec/22 19:09
• Updated: 22/Dec/22 05:57
• Resolved: 22/Dec/22 05:57

Description

The DECODE function was implemented for Oracle compatibility. It works similar to CASE expression, but it is supposed to have one major difference: NULL == NULL
https://docs.oracle.com/database/121/SQLRF/functions057.htm#SQLRF00631

The Spark implementation does not observe this however:

select decode(null, 6, 'Spark', NULL, 'SQL', 4, 'rocks');

NULL

 The result is supposed to be 'SQL'

Issue Links

• links to: [Github] Pull Request #39163 (gengliangwang)
• links to: [Github] Pull Request #39166 (gengliangwang)