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) ;