CAST function results
When casting a decimal value which has only zeros in the fractional digits, the CAST function in CDP differs from CDH.
Before Upgrade to CDP
In CDH, fractional digits appear in the output of a cast of a decimal value that has only zeros in the fractional part.
For example, run this query that implicitly casts a number having insignificant decimal thousandths:
SELECT 123.000
The output is a 0 tenths decimal: 123.0.
Explicit casts produce similar incorrect results.
select cast(123.000 as varchar(10)) ;
The output is a 0 tenths decimal: 123.0.
Hive behavior is the same when selecting data from a table.
After Upgrade to CDP
In CDP, fractional decimal digits are dropped in the output of a cast of a decimal value that has only zeros in the fractional part (HIVE-15335).
SELECT 123.000;
The output is the whole number: 123.
Hive behavior is the same when you explicitly cast columns and select data from a table.
Action Required
To get the legacy CDH behavior in CDP, cast as follows:
select cast(’123.000’ as string) ;