Casting types with leading or trailing spaces

Learn about the fix to resolve inconsistent CAST behavior while casting string to numeric data types that have leading or trailing whitespace characters.

Before upgrade to CDP

The CAST function resulted in incorrect results while casting string values with leading or trailing spaces to numeric data types. For example,

select cast(' 1 ' as tinyint), cast(' 1 ' as smallint), cast(' 1 ' as int), cast(' 1 ' as bigint), cast(' 1 ' as float), cast(' 1 ' as double), cast(' 1 ' as decimal(10,2))

Output:

NULL    NULL    NULL    NULL    1.0     1.0     1

The numeric data types, such as tinyint, smallint, int, and bigint were not converted correctly because of leading and trailing spaces and returned NULL. However, float, double, and decimal returned the correct output.

After upgrade to CDP

HIVE-17782 provides the fix to ensure consistent cast behavior across data types. The fix trims leading or trailing spaces in the string value before passing the value to the number formatter.