Reply
Explorer
Posts: 8
Registered: ‎09-28-2016
Accepted Solution

Impala round function does not return expected result

Hi,

 

I'm using impala-2.7.0-cdh5.10.0, and I find execute round() in impala-shell may give unexpected result, while hive can give correct result.

 

e.g.

 

impala> select round(269586/334026 * 100, 2);
80.70999999999999

hive > select round(269586/334026 * 100, 2);
80.71

Is this a known issue?

Cloudera Employee
Posts: 307
Registered: ‎10-16-2013

Re: Impala round function does not return expected result

Looks like a client display issue. Both Impala and Hive return a DOUBLE for round(). A DOUBLE cannot precisely represent 80.71 so I'm guessing that technically the results are the same, it's just that the Hive client "pretty prints" the results in a different way than Impala's.

 

If you require precise rouding results it's recommended you use decimal:

 

select round(cast(269586 / 334026 * 100 as decimal(17, 15)), 2);

80.71

New Contributor
Posts: 1
Registered: ‎09-21-2018

Re: Impala round function does not return expected result

Hi Alex,

 

it works using the solution you provided, however if i use variable/column to indicate the number of digits after decimal point then it throws an error, which is :

 

"AnalysisException: round() must be called with a constant second argument."

 

e.g Below query works perfectly fine

 

select round(cast(test1 as decimal(17,15)),2) as test3
from (

select round(269586/334026 * 100,2) as test1,
1 as test2
)A

 

 

However this query does not work:

 

select round(cast(test1 as decimal(17,15)),test2) as test3
from (

select round(269586/334026 * 100,2) as test1,
1 as test2
)A

 

the problem lies with the decimal typecast, if i remove it then issue will disappaear however the original issue will appear.

 

Please help, i have been struggling for quite some time with this issue. for my requirement i need to use column name to indicate numbers of digits to round, because the same field(column) may have numbers rounded off to various decimal digits.

 

Thanks in advance.

 

 

Regards

Nitin

Announcements