[IMPALA-8664] Inconsistent result by integer multiply NULL

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: Impala 2.11.0
• Component/s: Frontend
• Epic Color: ghx-label-4
• Assignee: Unassigned
• Reporter: Hajime Osako
• Created: 13/Jun/19 01:53
• Updated: 13/Jun/19 17:11

Description

A: "SELECT 1 * (NULL)" and "SELECT 1 * (CASE ...)" returns different result:

Expected result "null":

0: jdbc:hive2://test> SELECT 1 * (NULL);
null

The CASE part returns NULL, so expected same as above, but actually Exception:

0: jdbc:hive2://test> select 1 * (CASE WHEN '1' IS NULL THEN NULL ELSE NULL END);
Error: AnalysisException: Arithmetic operation requires numeric operands: 1 * (CASE WHEN '1' IS NULL THEN NULL ELSE NULL END) (state=HY000,code=0)

B: Using aggregation in CASE or without returns different result:

As per A, below query failing is kind of understandable:

0: jdbc:hive2://test> SELECT (-1 * CASE WHEN SUM(1) IS NULL THEN NULL WHEN COALESCE(NULL, 0) = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as col1, 2 as col2) dummy_t;
Error: AnalysisException: Arithmetic operation requires numeric operands: (-1 * CASE WHEN sum(1) IS NULL THEN NULL ELSE NULL END) (state=HY000,code=0)

But, removing SUM returns different result (no exception...):

0: jdbc:hive2://test> SELECT (-1 * CASE WHEN COALESCE(NULL, 0) = 0 THEN NULL ELSE 0 END) FROM (SELECT 1 as col1, 2 as col2) dummy_t;
null