[HIVE-12082] Null comparison for greatest and least operator

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.0.0
• Component/s: UDF
• Assignee: Szehon Ho
• Reporter: Szehon Ho
• Created: 09/Oct/15 21:11
• Updated: 25/May/17 11:45
• Resolved: 15/Oct/15 18:21

Description

In mysql comparisons if any of the entries are null, then the result is null.

https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html and https://dev.mysql.com/doc/refman/5.0/en/type-conversion.html.

This can be demonstrated by the following mysql query:

mysql> select greatest(1, null) from test;
+-------------------+
| greatest(1, null) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)

mysql> select greatest(-1, null) from test;
+--------------------+
| greatest(-1, null) |
+--------------------+
|               NULL |
+--------------------+
1 row in set (0.00 sec)

This is in contrast to Hive, where null are ignored in the comparisons.

hive> select greatest(null, 1) from test;
OK
1

Issue Links

• is related to: HIVE-14189 backport HIVE-13945 to branch-1
• relates to: HIVE-12070 Relax type restrictions on 'Greatest' and 'Least'
• relates to: HIVE-9402 Create GREATEST and LEAST udf
• links to: Review board