Conversion Functions

During expression evaluations, Velox doesn’t implicitly convert expression arguments to the correct types. If such a conversion is necessary, two provided conversion functions can be used explicitly cast values to a particular type.

Conversion Functions

cast(value AS type) → type

Explicitly cast a value as a type. This can be used to cast a varchar to a numeric value type and vice versa.

try_cast(value AS type) → type

Like cast(), but returns null if the cast fails. try_cast(x AS type) is different from try(cast(x AS type)) in that try_cast only suppresses errors happening during the casting itself but not those during the evaluation of its argument. For example, try_cast(x / 0 as double) throws a divide-by-0 error, while try(cast(x / 0 as double)) returns a NULL.

Supported Conversions

The supported conversions are listed below, with from-types given at rows and to-types given at columns. Conversions of ARRAY, MAP, and ROW types are supported if the conversion of their element types are supported. In addition, supported conversions to/from JSON are listed in JSON Functions.

	tinyint	smallint	integer	bigint	boolean	real	double	varchar	timestamp	timestamp with time zone	date	decimal
tinyint	Y	Y	Y	Y	Y	Y	Y	Y				Y
smallint	Y	Y	Y	Y	Y	Y	Y	Y				Y
integer	Y	Y	Y	Y	Y	Y	Y	Y				Y
bigint	Y	Y	Y	Y	Y	Y	Y	Y				Y
boolean	Y	Y	Y	Y	Y	Y	Y	Y
real	Y	Y	Y	Y	Y	Y	Y	Y
double	Y	Y	Y	Y	Y	Y	Y	Y
varchar	Y	Y	Y	Y	Y	Y	Y	Y	Y		Y
timestamp								Y	Y	Y	Y
timestamp with time zone									Y	Y
date									Y
decimal	Y	Y	Y	Y	Y	Y	Y	Y				Y

Cast to Integral Types

Integral types include bigint, integer, smallint, and tinyint.

From integral types

Casting one integral type to another is allowed when the input value is within the range of the result type. Casting from invalid input values throws.

Valid examples:

SELECT cast(1234567 as bigint); -- 1234567
SELECT cast(12 as tinyint); -- 12

Invalid examples:

SELECT cast(1234 as tinyint); -- Out of range
SELECT cast(1234567 as smallint); -- Out of range

From floating-point types

Casting from floating-point input to an integral type rounds the input value to the closest integral value. It is allowed when the rounded result is within the range of the result type. Casting from invalid input values throws.

Valid examples

SELECT cast(12345.12 as bigint); -- 12345
SELECT cast(12345.67 as bigint); -- 12346
SELECT cast(127.1 as tinyint); -- 127
SELECT cast(nan() as integer); -- 0
SELECT cast(nan() as smallint); -- 0
SELECT cast(nan() as tinyint); -- 0

Invalid examples

SELECT cast(127.8 as tinyint); -- Out of range
SELECT cast(1234567.89 as smallint); -- Out of range
SELECT cast(infinity() as bigint); -- Out of range

Casting NaN to bigint returns 0 in Velox but throws in Presto. We keep the behavior of Velox by intention because this is more consistent with other supported cases.

SELECT cast(nan() as bigint); -- 0

From strings

Casting a string to an integral type is allowed if the string represents an integral number within the range of the result type. By default, casting from strings that represent floating-point numbers is not allowed.

If cast_to_int_by_truncate is set to true, and the string represents a floating-point number, the decimal part will be truncated for casting to an integer.

Casting from invalid input values throws.

Valid examples

SELECT cast('12345' as bigint); -- 12345
SELECT cast('+1' as tinyint); -- 1
SELECT cast('-1' as tinyint); -- -1

Valid examples if cast_to_int_by_truncate=true

SELECT cast('12345.67' as tinyint); -- 12345
SELECT cast('1.2' as tinyint); -- 1
SELECT cast('-1.8' as tinyint); -- -1
SELECT cast('1.' as tinyint); -- 1
SELECT cast('-1.' as tinyint); -- -1
SELECT cast('0.' as tinyint); -- 0
SELECT cast('.' as tinyint); -- 0
SELECT cast('-.' as tinyint); -- 0

Invalid examples

SELECT cast('1234567' as tinyint); -- Out of range
SELECT cast('1a' as tinyint); -- Invalid argument
SELECT cast('' as tinyint); -- Invalid argument
SELECT cast('1,234,567' as bigint); -- Invalid argument
SELECT cast('1'234'567' as bigint); -- Invalid argument
SELECT cast('nan' as bigint); -- Invalid argument
SELECT cast('infinity' as bigint); -- Invalid argument

Invalid examples if cast_to_int_by_truncate=false

SELECT cast('12345.67' as tinyint); -- Invalid argument
SELECT cast('1.2' as tinyint); -- Invalid argument
SELECT cast('-1.8' as tinyint); -- Invalid argument
SELECT cast('1.' as tinyint); -- Invalid argument
SELECT cast('-1.' as tinyint); -- Invalid argument
SELECT cast('0.' as tinyint); -- Invalid argument
SELECT cast('.' as tinyint); -- Invalid argument
SELECT cast('-.' as tinyint); -- Invalid argument

From decimal

By default, the decimal part is rounded. If cast_to_int_by_truncate is enabled, the decimal part will be truncated for casting to an integer.

Valid examples

SELECT cast(2.56 decimal(6, 2) as integer); -- 2 /* cast_to_int_by_truncate enabled */
SELECT cast(2.56 decimal(6, 2) as integer); -- 3 /* cast_to_int_by_truncate disabled */
SELECT cast(3.46 decimal(6, 2) as integer); -- 3

Invalid examples

SELECT cast(214748364890 decimal(12, 2) as integer); -- Out of range

Cast to Boolean

From integral and floating-point types

Casting from integral or floating-point numbers to boolean is allowed. Non-zero numbers are converted to true while zero is converted to false.

Valid examples

SELECT cast(1 as boolean); -- true
SELECT cast(0 as boolean); -- false
SELECT cast(12 as boolean); -- true
SELECT cast(-1 as boolean); -- true
SELECT cast(1.0 as boolean); -- true
SELECT cast(1.1 as boolean); -- true
SELECT cast(-1.1 as boolean); -- true
SELECT cast(nan() as boolean); -- true
SELECT cast(infinity() as boolean); -- true
SELECT cast(0.0000000000001 as boolean); -- true

From strings

There is a set of strings allowed to be casted to boolean. Casting from other strings to boolean throws.

Valid examples

SELECT cast('1' as boolean); -- true
SELECT cast('0' as boolean); -- false
SELECT cast('t' as boolean); -- true (case insensitive)
SELECT cast('true' as boolean); -- true (case insensitive)
SELECT cast('f' as boolean); -- false (case insensitive)
SELECT cast('false' as boolean); -- false (case insensitive)

Invalid examples

SELECT cast('1.7E308' as boolean); -- Invalid argument
SELECT cast('nan' as boolean); -- Invalid argument
SELECT cast('infinity' as boolean); -- Invalid argument
SELECT cast('12' as boolean); -- Invalid argument
SELECT cast('-1' as boolean); -- Invalid argument
SELECT cast('tr' as boolean); -- Invalid argument
SELECT cast('tru' as boolean); -- Invalid argument

Cast to Floating-Point Types

From integral or floating-point types

Casting from an integral or floating-point number is allowed.

Valid examples

SELECT cast(1 as real); -- 1.0
SELECT cast(123.45 as real); -- 123.45

There are two cases where Velox behaves differently from Presto (#5934) when casting to real from a value beyond real’s limit. We will fix them to follow Presto’s behavior.

SELECT cast(1.7E308 as real); -- Presto returns Infinity but Velox throws
SELECT cast(-1.7E308 as real); -- Presto returns -Infinity but Velox throws

From strings

Casting a string to real is allowed if the string represents an integral or floating-point number. Casting from invalid input values throws.

Valid examples

SELECT cast('1.' as real); -- 1.0
SELECT cast('1' as real); -- 1.0
SELECT cast('1.7E308' as real); -- Infinity
SELECT cast('infinity' as real); -- Infinity (case insensitive)
SELECT cast('-infinity' as real); -- -Infinity (case insensitive)
SELECT cast('nan' as real); -- NaN (case insensitive)

Invalid examples

SELECT cast('1.7E308' as real); -- Out of range
SELECT cast('1.2a' as real); -- Invalid argument
SELECT cast('1.2.3' as real); -- Invalid argument

There are a few corner cases where Velox behaves differently from Presto. Presto throws INVALID_CAST_ARGUMENT on these queries, while Velox allows these conversions. We keep the Velox behaivor by intention because it is more consistent with other supported cases of cast.

SELECT cast('InfiNiTy' as real); -- Infinity
SELECT cast('nAn' as real); -- NaN

From decimal

Casting from decimal to double, float or any integral type is allowed. During decimal to an integral type conversion, if result overflows, or underflows, an exception is thrown.

Valid example

SELECT cast(decimal '10.001' as double); -- 10.001

Invalid example

SELECT cast(decimal '300.001' as tinyint); -- Out of range

Cast to String

Valid examples

SELECT cast(123 as varchar); -- '123'
SELECT cast(123.45 as varchar); -- '123.45'
SELECT cast(123.0 as varchar); -- '123.0'
SELECT cast(nan() as varchar); -- 'NaN'
SELECT cast(infinity() as varchar); -- 'Infinity'
SELECT cast(true as varchar); -- 'true'
SELECT cast(timestamp '1970-01-01 00:00:00' as varchar); -- '1970-01-01T00:00:00.000'
SELECT cast(cast(22.51 as DECIMAL(5, 3)) as varchar); -- '22.510'
SELECT cast(cast(-22.51 as DECIMAL(4, 2)) as varchar); -- '-22.51'
SELECT cast(cast(0.123 as DECIMAL(3, 3)) as varchar); -- '0.123'
SELECT cast(cast(1 as DECIMAL(6, 2)) as varchar); -- '1.00'

Cast to TIMESTAMP

From strings

Casting from a string to timestamp is allowed if the string represents a timestamp in the format YYYY-MM-DD followed by an optional hh:mm:ssZZ. Casting from invalid input values throws.

Valid examples

SELECT cast('1970-01-01' as timestamp); -- 1970-01-01 00:00:00
SELECT cast('1970-01-01 00:00:00' as timestamp); -- 1970-01-01 00:00:00
SELECT cast('1970-01-01 02:01' as timestamp); -- 1970-01-01 02:01:00
SELECT cast('1970-01-01 00:00:00-02:00' as timestamp); -- 1970-01-01 02:00:00

Invalid example

SELECT cast('2012-Oct-23' as timestamp); -- Invalid argument

From date

Casting from date to timestamp is allowed.

Valid examples

SELECT cast(date '1970-01-01' as timestamp); -- 1970-01-01 00:00:00
SELECT cast(date '2012-03-09' as timestamp); -- 2012-03-09 00:00:00

From TIMESTAMP WITH TIME ZONE

The results depend on whether configuration property adjust_timestamp_to_session_timezone is set or not.

If set to true, input timezone is ignored and timestamp is returned as is. For example, “1970-01-01 00:00:00.000 America/Los_Angeles” becomes “1970-01-01 00:00:00.000”.

Otherwise, timestamp is shifted by the offset of the timezone. For example, “1970-01-01 00:00:00.000 America/Los_Angeles” becomes “1969-12-31 16:00:00.000”.

Valid examples

-- `adjust_timestamp_to_session_timezone` is true
SELECT cast(timestamp '1970-01-01 00:00:00 America/Los_Angeles' as timestamp); -- 1970-01-01 00:00:00.000
SELECT cast(timestamp '2012-03-09 10:00:00 Asia/Chongqing' as timestamp); -- 2012-03-09 10:00:00.000
SELECT cast(from_unixtime(0, '+06:00') as timestamp); -- 1970-01-01 00:00:00.000
SELECT cast(from_unixtime(0, '-02:00') as timestamp); -- 1970-01-01 00:00:00.000

-- `adjust_timestamp_to_session_timezone` is false
SELECT cast(timestamp '1970-01-01 00:00:00 America/Los_Angeles' as timestamp); -- 1969-12-31 16:00:00.000
SELECT cast(timestamp '2012-03-09 10:00:00 Asia/Chongqing' as timestamp); -- 2012-03-09 18:00:00.000
SELECT cast(from_unixtime(0, '+06:00') as timestamp); -- 1970-01-01 06:00:00.000
SELECT cast(from_unixtime(0, '-02:00') as timestamp); -- 1969-12-31 22:00:00.000

Cast to TIMESTAMP WITH TIME ZONE

From TIMESTAMP

The results depend on whether configuration property adjust_timestamp_to_session_timezone is set or not.

If set to true, the output is adjusted to be equivalent as the input timestamp in UTC based on the user provided session_timezone (if any). For example, when user supplies “America/Los_Angeles” “1970-01-01 00:00:00.000” becomes “1969-12-31 16:00:00.000 America/Los_Angeles”.

Otherwise, the user provided session_timezone (if any) is simply appended to the input timestamp. For example, “1970-01-01 00:00:00.000” becomes “1970-01-01 00:00:00.000 America/Los_Angeles”.

Valid examples

-- `adjust_timestamp_to_session_timezone` is true
SELECT cast(timestamp '1970-01-01 00:00:00' as timestamp with time zone); -- 1969-12-31 16:00:00.000 America/Los_Angeles
SELECT cast(timestamp '2012-03-09 10:00:00' as timestamp with time zone); -- 2012-03-09 02:00:00.000 America/Los_Angeles
SELECT cast(from_unixtime(0) as timestamp with time zone); -- 1969-12-31 16:00:00.000 America/Los_Angeles

-- `adjust_timestamp_to_session_timezone` is false
SELECT cast(timestamp '1970-01-01 00:00:00' as timestamp with time zone); -- 1970-01-01 00:00:00.000 America/Los_Angeles
SELECT cast(timestamp '2012-03-09 10:00:00' as timestamp with time zone); -- 2012-03-09 10:00:00.000 America/Los_Angeles
SELECT cast(from_unixtime(0) as timestamp with time zone); -- 1970-01-01 00:00:00.000 America/Los_Angeles

Cast to Date

From strings

By default, only ISO 8601 strings are supported: [+-]YYYY-MM-DD.

If cast_string_to_date_is_iso_8601 is set to false, all Spark supported patterns are allowed. See the documentation for cast_string_to_date_is_iso_8601 in Expression Evaluation Configuration for the full list of supported patterns.

Casting from invalid input values throws.

Valid examples

cast_string_to_date_is_iso_8601=true

SELECT cast('1970-01-01' as date); -- 1970-01-01

cast_string_to_date_is_iso_8601=false

SELECT cast('1970' as date); -- 1970-01-01
SELECT cast('1970-01' as date); -- 1970-01-01
SELECT cast('1970-01-01' as date); -- 1970-01-01
SELECT cast('1970-01-01T123' as date); -- 1970-01-01
SELECT cast('1970-01-01 ' as date); -- 1970-01-01
SELECT cast('1970-01-01 (BC)' as date); -- 1970-01-01

Invalid examples

cast_string_to_date_is_iso_8601=true

SELECT cast('2012' as date); -- Invalid argument
SELECT cast('2012-10' as date); -- Invalid argument
SELECT cast('2012-10-23T123' as date); -- Invalid argument
SELECT cast('2012-10-23 (BC)' as date); -- Invalid argument
SELECT cast('2012-Oct-23' as date); -- Invalid argument
SELECT cast('2012/10/23' as date); -- Invalid argument
SELECT cast('2012.10.23' as date); -- Invalid argument
SELECT cast('2012-10-23 ' as date); -- Invalid argument

cast_string_to_date_is_iso_8601=false

SELECT cast('2012-Oct-23' as date); -- Invalid argument
SELECT cast('2012/10/23' as date); -- Invalid argument
SELECT cast('2012.10.23' as date); -- Invalid argument

From TIMESTAMP

Casting from timestamp to date is allowed. If present, the part of hh:mm:ss in the input is ignored.

Valid examples

SELECT cast(timestamp '1970-01-01 00:00:00' as date); -- 1970-01-01
SELECT cast(timestamp '1970-01-01 23:59:59' as date); -- 1970-01-01

Cast to Decimal

From integral types

Casting an integral numberto a decimal of given precision and scale is allowed if the input value can be represented by the precision and scale. Casting from invalid input values throws.

Valid examples

SELECT cast(1 as decimal(4, 2)); -- decimal '1.00'
SELECT cast(10 as decimal(4, 2)); -- decimal '10.00'
SELECT cast(123 as decimal(5, 2)); -- decimal '123.00'

Invalid examples

SELECT cast(123 as decimal(6, 4)); -- Out of range
SELECT cast(123 as decimal(4, 2)); -- Out of range

From decimal

Casting one decimal to another is allowed if the input value can be represented by the result decimal type. When casting from a larger scale to a smaller one, the fraction part is rounded.

Valid example

SELECT cast(decimal '0.69' as decimal(4, 3)); -- decimal '0.690'
SELECT cast(decimal '0.69' as decimal(4, 1)); -- decimal '0.7'

Invalid example

SELECT cast(decimal '-1000.000' as decimal(6, 4)); -- Out of range
SELECT cast(decimal '123456789' as decimal(9, 1)); -- Out of range