Hive - double precision

问题:

I have been working on hive and found something peculiar. Basically, while using double as a datatype for your column we need not have any precision specified (hive takes the precision dynamically depending on the value). But, here is the issue. Whenever my values are 2 and 7 after the decimal point i see the returning value also changes number of positions in the precision.

Let me clear that with my simple example.

hive> select cast('34.2234' + '32.6554' -3.1 as double);
OK
63.7788
Time Taken 0.077 seconds, Fetched: 1 row(s)

When i use 1 after my decimal place (1 in 3.1 while subtracting) i could see the result seems to be good. But when 3.2 or 3.7 is given for subtraction, i see the below change

While using 3.2

hive> select cast('34.2234' + '32.6554' -3.2 as double);
OK
63.678799999999995
Time Taken 0.077 seconds, Fetched: 1 row(s)

While using 3.7

hive> select cast('34.2234' + '32.6554' -3.7 as double);
OK
63.178799999999995
Time Taken 0.077 seconds, Fetched: 1 row(s)

Though the result seems to be correct, why is the precision getting changed in this case. Its the same when we use any value before decimal point and only 2 or 7 after it (like 4.2, 4.7, 3.2, 2.7 etc). What is it so peculiar with 2 and 7 that it changes the precision to 15 values and why not for other values.

回答1:

This is how floating point arithmetic looks like

hive> select 1.1 + 0.1;
OK
1.2000000000000002

DOUBLE (8-byte double precision floating point number

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

If you want something accurate/predictable use decimal

hive>  select cast (1.1 as decimal(12,2)) + cast (0.1 as decimal(12,2));
OK
1.2

回答2:

I think you are confusing calculation precision with displayed decimals.

Hive will always use the same calculation precision, but does not display trailing zeros.

Hence it will sometimes return only a few decimals, but when you use a number that does not 'fit' well in the set of floating point numbers, it will just show you the decimals till the end.