Data type conversion

An explicit conversion uses the CAST function to convert a value from one data type to another. The following table shows the explicit conversions supported by MaxCompute SQL. For more information about this function, see CAST.

Usage notes and limitations

• When you convert a DOUBLE type to a BIGINT type, the fractional part is truncated. For example, CAST(1.6 AS BIGINT) = 1.

• When you convert a STRING in DOUBLE format to a BIGINT, it is first converted to DOUBLE and then to BIGINT, truncating the fractional part. For example, CAST("1.6" AS BIGINT) = 1.

• You can convert a STRING in BIGINT format to a DOUBLE. The result has one decimal place. For example, CAST("1" AS DOUBLE) = 1.0.

• Conversions to the DATETIME type use the default format yyyy-mm-dd hh:mi:ss.

• You can use built-in functions to convert some data types that do not support direct explicit conversion. For example, you can use the TO_CHAR function to convert a BOOLEAN value to a STRING. For more information, see TO_CHAR. The TO_DATE function also supports converting a STRING value to a DATETIME. For more information, see TO_DATE.

• If a DECIMAL's value range is exceeded, CAST STRING TO DECIMAL may cause issues such as an overflow error or data truncation.

• Explicitly converting a DECIMAL to a DOUBLE or FLOAT may result in a loss of precision. For high-precision scenarios, such as currency calculations, we recommend using the DECIMAL type.

• MaxCompute supports type conversions for complex data types. For both implicit and explicit conversions of a complex type, its subtypes must also be convertible. When you convert a STRUCT, the field names are not required to match, but the number of fields must be the same and the corresponding fields must be implicitly or explicitly convertible. For example:

  • ARRAY<BIGINT> can be implicitly or explicitly converted to ARRAY<STRING>.

  • ARRAY<BIGINT> can be explicitly converted to ARRAY<INT> but cannot be implicitly converted.

  • ARRAY<BIGINT> cannot be implicitly or explicitly converted to ARRAY<DATETIME>.

  • STRUCT<a:BIGINT,b:INT> can be implicitly converted to STRUCT<col1:STRING,col2:BIGINT> but cannot be implicitly or explicitly converted to STRUCT<a:STRING>.

An implicit conversion is a type conversion that MaxCompute performs automatically at runtime based on the context and conversion rules. The following tables show the implicit conversions that MaxCompute supports.

Example

SELECT user_id+age+'12345', CONCAT(user_name,user_id,age) FROM user;

The rules for implicit conversion vary depending on the operator:

• Implicit conversion with relational operators

  Relational operators include =, <>, <, <=, >, >=, IS NULL, IS NOT NULL, LIKE, RLIKE, and IN. The implicit conversion rules for LIKE, RLIKE, and IN differ from those of other relational operators and are described separately. The rules in this section do not apply to these three operators.

  When different data types are involved in a relational operation, they are implicitly converted according to the following rules.

  From/to	BIGINT	DOUBLE	STRING	DATETIME	BOOLEAN	DECIMAL
  BIGINT	N/A	DOUBLE	DOUBLE	N	N	DECIMAL
  DOUBLE	DOUBLE	N/A	DOUBLE	N	N	DECIMAL
  STRING	DOUBLE	DOUBLE	N/A	DATETIME	N	DECIMAL
  DATETIME	N	N	DATETIME	N/A	N	N
  BOOLEAN	N	N	N	N	N/A	N
  DECIMAL	DECIMAL	DECIMAL	DECIMAL	N	N	N/A

  Note

  • If the two data types being compared cannot be implicitly converted, the relational operation fails and returns an error.

  • For more information about relational operators, see Operators.

• Implicit conversion with special relational operators

  Special relational operators include LIKE, RLIKE, and IN.

  • The syntax for LIKE and RLIKE is as follows:

    source LIKE pattern;  
    source RLIKE pattern;

    Note

    • The source and pattern parameters for LIKE and RLIKE must be of the STRING type.

    • Other data types are not allowed and are not implicitly converted to STRING for this operation.

  • The syntax for IN is as follows:

    key IN (value1, value2, …)

    Note

    • In an IN expression, all values in the value list must have the same data type.

    • When the key is compared with the values, if the types include BIGINT, DOUBLE, and STRING, we recommend converting them all to DOUBLE. If the types include DATETIME and STRING, we recommend converting them all to DATETIME. No other type conversions are allowed.

• Implicit conversion with arithmetic operators

  Arithmetic operators include +, -, *, /, and %. Their implicit conversion rules are as follows:

  • Only STRING, BIGINT, DOUBLE, and DECIMAL types can be used in arithmetic operations.

  • STRING values are implicitly converted to DOUBLE before the operation.

  • BIGINT is implicitly converted to DOUBLE when used in calculations with DOUBLE.

  • DATETIME and BOOLEAN types cannot be used in arithmetic operations.

• Implicit conversion with logical operators

  Logical operators include and, or, and not. Their implicit conversion rules are as follows:

  • Only BOOLEAN values can be used in logical operations.

  • Other data types cannot be used in logical operations and cannot be implicitly converted for this purpose.

MaxCompute SQL provides a wide range of built-in functions to perform calculations on one or more columns from any row and return various data types. Their implicit conversion rules are as follows:

• When you call a function, if an input parameter's data type does not match the defined type for that parameter, the parameter is implicitly converted to the required type.

• The implicit conversion rules vary for each built-in function in MaxCompute SQL (see Built-in functions overview for more information).

For more information about CASE WHEN expressions, see CASE WHEN expression. The implicit conversion rules are as follows:

• If the return types include only BIGINT and DOUBLE, all are converted to DOUBLE.

• If any potential return value is a STRING, all return values are converted to STRING. If a type, such as BOOLEAN, cannot be converted, an error is returned.

• Other type conversions are not allowed.