[ 
https://issues.apache.org/jira/browse/HIVE-14568?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15503847#comment-15503847
 ] 

Akhil Chalamalasetty edited comment on HIVE-14568 at 9/19/16 3:58 PM:
----------------------------------------------------------------------

Thanks for the elaborate explanation Zhang. We will workaround this issue by 
casting the column to a lower precision & scale. 
Since we have a few developers migrating from ORACLE and Postgres SQL, we 
thought this would be a feature request to ease the usage of Hive. Please let 
us know if there is a way to introduce such a mode on Hive and if that would 
have a any performance impacts once implemented.

Regards,
Akhil


was (Author: akhilnaidu):
Thanks Zhang. We will workaround this issue by casting the column to a lower 
precision & scale. 
Since we have a few developers migrating from ORACLE and Postgres SQL, we 
thought this would be a feature request to ease the usage of Hive. Please let 
us know if there is a way to introduce such a mode on Hive and if that would 
have a any performance impacts once implemented.

Regards,
AKhil

> Hive Decimal Returns NULL
> -------------------------
>
>                 Key: HIVE-14568
>                 URL: https://issues.apache.org/jira/browse/HIVE-14568
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 1.0.0, 1.2.0
>         Environment: Centos 6.7, Hadoop 2.7.2,hive 1.0.0,2.0
>            Reporter: gurmukh singh
>            Assignee: Xuefu Zhang
>
> Hi
> I was under the impression that the bug: 
> https://issues.apache.org/jira/browse/HIVE-5022 got fixed. But, I see the 
> same issue in Hive 1.0 and hive 1.2 as well.
> hive> desc mul_table;
> OK
> prc                           decimal(38,28)
> vol                           decimal(38,10)
> Time taken: 0.068 seconds, Fetched: 2 row(s)
> hive> select prc, vol, prc*vol as cost from mul_table;
> OK
> 1.2           200     NULL
> 1.44          200     NULL
> 2.14          100     NULL
> 3.004         50      NULL
> 1.2           200     NULL
> Time taken: 0.048 seconds, Fetched: 5 row(s)
> Rather then returning NULL, it should give error or round off.
> I understand that, I can use Double instead of decimal or can cast it, but 
> still returning "Null" will make many things go unnoticed.
> hive> desc mul_table2;
> OK
> prc                           double
> vol                           decimal(14,10)
> Time taken: 0.049 seconds, Fetched: 2 row(s)
> hive> select * from mul_table2;
> OK
> 1.4           200
> 1.34          200
> 7.34          100
> 7454533.354544        100
> Time taken: 0.028 seconds, Fetched: 4 row(s)
> hive> select prc, vol, prc*vol  as cost from mul_table3;
> OK
> 7.34          100     734.0
> 7.34          1000    7340.0
> 1.0004        1000    1000.4
> 7454533.354544        100     7.454533354544E8       <----- Wrong result
> 7454533.354544        1000    7.454533354544E9       <----- Wrong result
> Time taken: 0.025 seconds, Fetched: 5 row(s)
> Casting:
> hive> select prc, vol, cast(prc*vol as decimal(38,38)) as cost from 
> mul_table3;
> OK
> 7.34          100     NULL
> 7.34          1000    NULL
> 1.0004        1000    NULL
> 7454533.354544        100     NULL
> 7454533.354544        1000    NULL
> Time taken: 0.033 seconds, Fetched: 5 row(s)
> hive> select prc, vol, cast(prc*vol as decimal(38,10)) as cost from 
> mul_table3;
> OK
> 7.34          100     734
> 7.34          1000    7340
> 1.0004        1000    1000.4
> 7454533.354544        100     745453335.4544
> 7454533.354544        1000    7454533354.544
> Time taken: 0.026 seconds, Fetched: 5 row(s) 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to