Hive
  1. Hive
  2. HIVE-5798

NULL is mistaken as exact data type in numeric operations

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.12.0
    • Fix Version/s: None
    • Component/s: Types
    • Labels:
      None

      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.

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Xuefu Zhang
            Reporter:
            Xuefu Zhang
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development