Xuefu Zhang created HIVE-5798:
---------------------------------
Summary: NULL is mistaken as exact data type in numeric operations
Key: HIVE-5798
URL: https://issues.apache.org/jira/browse/HIVE-5798
Project: Hive
Issue Type: Bug
Components: Types
Affects Versions: 0.12.0
Reporter: Xuefu Zhang
Assignee: Xuefu Zhang
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:
{code}
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
{code}
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:
{code}
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 | |
+--------+--------------+------+-----+---------+-------+
{code}
Though the value in the column stays as NULL, the type of result column is
different. Hive should follow MySQL in this aspect.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
