Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-8592

0 values convert to null if casting to or inserting to Hive DECIMAL where precision and scale are the same



    • Bug
    • Status: Resolved
    • Major
    • Resolution: Duplicate
    • 0.13.0
    • 0.13.0
    • Database/Schema, SQL
    • None
    • Running Apache Hive version 0.13.0 using HortonWorks with hadoop version, on Linux operating system centos5 (also occurs on centos6)


      I am trying to load zero values into Hive Decimal fields into a Hive table where the precision and scale are defined as the same e.g. DECIMAL(1,1) or DECIMAL(3,3) etc...
      However every time I run a hive ql insert statement to do this containing zero values or run a LOAD DATA command to load a text file of data containing zero values to these columns / fields, on performing a query on the table, these zero values are displayed and treated as NULL values.
      On further investigation, I was able to narrow the problem down to doing simple selects with casts. See example and output from Hive below. So attempting to do a cast for 0 or 0.0 or '.0' to DECIMAL(1,1) NULL is returned instead of 0. This is the same for precisions 1-38 where the scale is also the same
      If there is a work around for this then please let me know. Thanks!

      hive> select cast('.0' as DECIMAL(1,1)), cast('0.0' as DECIMAL(1,1)), cast('0' as DECIMAL(1,1)), cast(0 as DECIMAL(1,1)), cast(0.0 as DECIMAL(1,1));
      Query ID = xxxxxxx_20141024140000_e4dfdcc1-e4ad-4f84-bd48-198e29fd3757
      Total jobs = 1
      Launching Job 1 out of 1
      Number of reduce tasks is set to 0 since there's no reduce operator
      Starting Job = job_1413470329106_0052, Tracking URL = http://hdp8:8088/proxy/application_1413470329106_0052/
      Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_1413470329106_0052
      Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
      2014-10-24 14:01:10,256 Stage-1 map = 0%, reduce = 0%
      2014-10-24 14:01:27,644 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 6.51 sec
      MapReduce Total cumulative CPU time: 6 seconds 510 msec
      Ended Job = job_1413470329106_0052
      MapReduce Jobs Launched:
      Job 0: Map: 1 Cumulative CPU: 6.51 sec HDFS Read: 269 HDFS Write: 15 SUCCESS
      Total MapReduce CPU Time Spent: 6 seconds 510 msec
      Time taken: 36.281 seconds, Fetched: 1 row(s)




            Unassigned Unassigned
            aidan_semple Aidan Semple
            0 Vote for this issue
            2 Start watching this issue