Data types

• MaxCompute SQL
• MapReduce of the new version
• SDK 0.27.2-public and later versions, client 0.27.0 and later versions

• Note 1: changes in the semantics of the INT keyword. After you enable the new data types, the INT keyword in the SQL statement refers to the 32-bit integer type, for example, cast(a as INT) is to convert the a type to the 32-bit integer type. If new data types are not enabled, it is converted to the BIGINT type, which is a 64-bit value. For example, in an SQL statement, cast(a as INT) is equivalent to cast(a as BIGINT), or create table t(a INT) is equivalent to create table a (BIGINT). To be compatible with the original data types of MaxCompute, if you do not set odps.sql.type.system.odps2=true; in new data types, the preceding conversion is still performed but an error is prompted, indicating that INT is processed as BIGINT. To prevent any confusion that may result from this conversion, we recommend that you change all INT to BIGINT.
• Changes to the semantics of an integer constant
  Use the integer constant 1 in SELECT 1 + a; as an example.

  • If new data types are not enabled, the integer is processed as the BIGINT type. (If the length of the constant exceeds the value range of BIGINT, for example, 1000000000000000000000000, it is processed as the DOUBLE type.)
  • If new data types are enabled, the integer constant is first interpreted as 1 of a 32-bit integer of the INT type. If the constant value exceeds the value range of the INT type but does not exceed the value range of the BIGINT type, it is interpreted as the BIGINT type. If the value of the constant exceeds the value range of the BIGINT type, it is interpreted as the DOUBLE type.
  • Possible compatibility issues: The INT type may cause inconsistencies in function prototypes during subsequent operations. For example, the actions of peripheral tools and subsequent operations might be altered due to the new-type tables generated after data is written to a disk.
• Changes to the implicit type conversion rules
  • If new data types are enabled, some implicit type conversions may be disabled. For example, errors may occur or precision may be reduced when the data type is converted from STRING to BIGINT, from STRING to DATETIME, from DOUBLE to BIGINT, from DECIMAL to DOUBLE, or from DECIMAL to BIGINT. In this case, you can use the CAST function to convert the data type.
  • Implicit type conversion has major impacts on function invocations and INSERT statements. For example, the SQL statements can be executed when new data types are not enabled, but an error is reported when new data types are enabled.
• Changes to the supported operations, built-in functions, and UDFs
  When new data types are not enabled, some operations and built-in functions that use new data types as input parameters and return values are ignored. These operations and functions can be normally executed only after you enable the new data types. The major impact is as follows:

  • Some built-in functions can only be used after new data types are enabled. Such functions include most functions that use the INT type as parameters and subsequently bear BIGINT overload, such as YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND, DAYOFMONTH, and WEEKOFYEAR. These functions can be implemented by using the DATEPART built-in function.
  • Changes to UDF resolution Assume that a UDF contains two overloads: BIGINT and INT. The BIGINT overload is performed based on the original data types because the INT type cannot be identified. However, the INT overload may be parsed based on the new data types.
• Changes to the resolution of the BIGINT keyword

  A single integer constant, such as 123, is classified as the BIGINT type based on the original data types, whereas it is classified as the INT type based on the new data types. Possible compatibility issues: The INT type may cause inconsistencies in function prototypes during subsequent operations. For example, the actions of peripheral tools and subsequent operations might be altered due to new-type tables generated after data is written to a disk.

• Changes to partition column types
  • If new data types are not enabled, the partition column type can only be STRING.
  • If new data types are enabled, the partition column type can be STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, or BIGINT.
  • Special processing is used if new data types are not enabled: The partition fields in INSERT operations are processed as the STRING type. For example, enter: insert overwrite table t partition (pt = 045) select ... ;. Note that 045 is not enclosed in quotation marks. Therefore, it should be parsed as the integer type, that is, 045 is actually 45. However, if new data types are not enabled, 045 is still processed as 045. If new data types are enabled, the special processing is not performed, that is, 045 is 45.
• Changes to the behavior of the LIMIT statement
  Use the SELECT * FROM t1 UNION ALL SELECT * FROM t2 limit 10; statement as an example.

  • If new data types are not enabled, this statement is SELECT * FROM t1 UNION ALL SELECT * FROM ( SELECT * FROM t2 limit 10) t2;.
  • If new data types are enabled, this statement is SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t limit 10;.

  Actions of the UNION, INTERSECT, and EXCEPT, INTERSECT, EXCEPT, LIMIT, ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY statements also change if new data types are enabled.

• Changes to the resolution of the IN expression

  IN expressions, such as a in (1, 2, 3):

  • If new data types are not enabled, all the values in the parentheses () must be of the same type.
  • If new data type are enabled, all the values in the parentheses () are implicitly converted to the same type.

create table a_bigint_table(a int); -- The INT in this statement is actually processed as BIGINT.
select cast(id as int) from mytable; -- The INT in this statement is actually processed as BIGINT.

insert into test_tb(a) values (3.5BD)