[HIVE-28302] Let SUM UDF return NULL when all rows have non-numeric texts

• Type: Improvement
• Status: Patch Available
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: UDF
• Labels: pull-request-available
• Assignee: Shohei Okumiya
• Reporter: Shohei Okumiya
• Created: 05/Jun/24 06:14
• Updated: 07/Jun/24 10:31

Description

The ANSI standard says UDAF should return NULL when all inputs are NULL.

Hive is very generous and accepts non-numeric texts as input for SUM. To give some consistency to the generous specification, we believe `SUM(string_col)` should behave in the same way as `SUM(CAST(string_col AS DOUBLE))`.

However, Hive's SUM returns 0.0 in that case.

> SELECT SUM(CAST(null AS STRING)), SUM('invalid num'), SUM(CAST('invalid num' AS DOUBLE));
+-------+------+-------+
|  _c0  | _c1  |  _c2  |
+-------+------+-------+
| NULL  | 0.0  | NULL  |
+-------+------+-------+ 

We see some more discussions in https://github.com/apache/hive/pull/5091.

Issue Links

• relates to: HIVE-28082 HiveAggregateReduceFunctionsRule could generate an inconsistent result
• links to: GitHub Pull Request #5283