[HIVE-5798] NULL is mistaken as exact data type in numeric operations

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.12.0
• Component/s: Types
• Assignee: Xuefu Zhang
• Reporter: Xuefu Zhang
• Created: 12/Nov/13 05:44
• Updated: 12/Nov/13 05:44

Description

If NULL literal appears in as an operand in an arithmetic operator, it was treated as if it has an exact numeric data type. The following demonstrates the behaviour:

hive> desc test;
OK
i                   	int                 	None                
b                   	boolean             	None                
d                   	double              	None                
s                   	string              	None                
dec                 	decimal(5,2)        	None                
Time taken: 0.272 seconds, Fetched: 5 row(s)
hive> create table test1 as select i + NULL from test limit 1;
hive> desc test1;
OK
_c0                 	int                 	None                

That is, integer type + NULL type = integer type. However, NULL means data missing or unknown. We don't know the type of a NULL literal. Thus, it should NOT be treated as a literal of an exact type.

MySQL, however, has a different behaviour, which seems more reasonable:

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| i     | int(11)      | YES  |     | NULL    |       |
| b     | tinyint(1)   | YES  |     | NULL    |       |
| d     | double       | YES  |     | NULL    |       |
| s     | varchar(5)   | YES  |     | NULL    |       |
| dd    | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
mysql> create table test24 as select i+NULL from test;
mysql> desc test24;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| i+NULL | double(17,0) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

Though the value in the column stays as NULL, the type of result column is different. Hive should follow MySQL in this aspect.