ODPS-0123091

Sample

ODPS-0123091:Illegal type cast - in function cast, value 'xx' cannot be casted from YY to ZZ

Description

This error occurs when a CAST type conversion fails. The root cause is invalid input data: the value exists but cannot be converted to the target type. For example, the string '-' cannot be cast to BIGINT. For more information about supported type conversions, see Type conversions.

Solution

Depending on the root cause, use one of the following approaches:

• Clean the input data: Identify and remove or correct values that cannot be converted. This resolves errors caused by invalid input.

• Switch to non-strict mode: To allow conversion failures without returning an error, change the processing mode to non-strict mode.

Examples

The following examples use a table mc_test with a string column a that contains a valid value ('100') and an invalid value ('-').

Prepare the data

CREATE TABLE mc_test
(
  a string
);
INSERT overwrite TABLE mc_test
VALUES ('100'), ('-');

Reproduce the error (strict mode)

In strict mode, a CAST failure on any row causes the entire query to fail.

-- Set the processing mode to strict mode.
-- For Alibaba Group internal environments, use: odps.sql.udf.strict.mode=true
-- For the Chinese mainland public cloud, use: odps.function.strictmode
SET odps.sql.udf.strict.mode=true;

-- This query fails because '-' cannot be cast to BIGINT in strict mode.
SELECT cast(a AS bigint)
FROM mc_test;

Expected output:

FAILED: ODPS-0123091:Illegal type cast - in function cast, value '-' cannot be casted from String to Bigint

Resolve by switching to non-strict mode

In non-strict mode, conversion failures do not return an error, and the query completes.

SET odps.sql.udf.strict.mode=false;

SELECT cast(a AS bigint)
FROM mc_test;