MaxCompute V1.0 Data Type Edition Overview: Supported Types & Limits - MaxCompute

The MaxCompute V1.0 data type edition is one of three data type editions available in MaxCompute. It supports only MaxCompute V1.0 data types and is intended for projects that depend on components incompatible with the MaxCompute V2.0 data type edition.

When to use this edition

Use the MaxCompute V1.0 data type edition when your project runs on an early MaxCompute version and depends on components that do not support the MaxCompute V2.0 data type edition.

Project configuration

A MaxCompute project uses the V1.0 data type edition when all three of the following project properties are set:

setproject odps.sql.type.system.odps2=false; -- Disable MaxCompute V2.0 data types.
setproject odps.sql.decimal.odps2=false;      -- Disable the DECIMAL type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false;    -- Disable Hive-compatible data types.

Supported data types

Basic data types

All basic data types support NULL values.

Data type	Constant example	Description
BIGINT	`100000000000L` and `-1L`	64-bit signed integer. Valid values: -2⁶³ + 1 to 2⁶³ - 1.
DOUBLE	`3.14159261E+7`	64-bit binary floating point.
DECIMAL	`3.5BD` and `99999999999.9999999BD`	Exact numeric type. Default precision: `decimal(54,18)`. Precision and scale cannot be adjusted. Integer part range: -10³⁶ + 1 to 10³⁶ - 1. Decimal part: accurate to 10⁻¹⁸.
STRING	`"abc"`, `'bcd'`, `"alibaba"`, `'inc'`	String type. Maximum length: 8 MB.
DATETIME	`DATETIME'2017-11-11 00:00:00'`	Date and time type. Does not include millisecond precision. Valid values: 0000-01-01 to 9999-12-31.
BOOLEAN	`True` and `False`	Boolean type. Valid values: True and False.

Complex data types

Complex data types can be nested within each other. For built-in functions related to complex types, see ARRAY, MAP, and STRUCT.

Data type	Definition	Constructor
ARRAY	`ARRAY<BIGINT>`, `ARRAY<STRUCT<a:BIGINT, b:STRING>>`	`ARRAY(1, 2, 3)`, `ARRAY(NAMED_STRUCT('a', 1, 'b', '2'), NAMED_STRUCT('a', 3, 'b', '4'))`
MAP	`MAP<STRING, STRING>`, `MAP<BIGINT, ARRAY<STRING>>`	`MAP("k1", "v1", "k2", "v2")`, `MAP(1L, ARRAY('a', 'b'), 2L, ARRAY('x', 'y'))`
STRUCT	`STRUCT<'x', BIGINT, 'y', BIGINT>`, `STRUCT<'field1', BIGINT, 'field2', ARRAY<BIGINT>, 'field3', MAP<BIGINT>>`	`NAMED_STRUCT('x', 1, 'y', 2)`, `NAMED_STRUCT('field1', 100L, 'field2', ARRAY(1, 2), 'field3', MAP(1, 100, 2, 200))`

Usage notes

Integer constants: Processed as BIGINT by default. If a constant exceeds the BIGINT range (for example, 1,000,000,000,000,000,000,000,000), it is processed as DOUBLE. For example, 1 in SELECT 1 + a; is BIGINT.
DECIMAL constants: When inserting a DECIMAL constant into a field, use the BD suffix format. Example:
```
INSERT INTO test_tb(a) VALUES (3.5BD);
```
DATETIME precision: DATETIME values do not include the millisecond component. To load data with millisecond precision, add -dfp to your Tunnel command. Example: tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'. For more information, see Tunnel commands.
STRING concatenation: STRING constants can be concatenated. For example, abc and xyz concatenate to abcxyz.
Partition key columns: Partition key columns of partitioned tables must be of the STRING type.
Built-in functions: Built-in functions that include parameters of MaxCompute V2.0 data types cannot be used under the V1.0 data type edition.

Differences from the MaxCompute V2.0 data type edition

LIMIT behavior with UNION ALL

The following example uses SELECT * FROM t1 UNION ALL SELECT * FROM t2 LIMIT 10;.

Edition	Equivalent statement
MaxCompute V1.0	`SELECT * FROM t1 UNION ALL SELECT * FROM (SELECT * FROM t2 LIMIT 10) t2;`
MaxCompute V2.0	`SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) t LIMIT 10;`

The LIMIT, ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY operations behave differently between the MaxCompute V1.0 data type edition and the MaxCompute V2.0 data type edition.

IN expression behavior

The following example uses a IN (1, 2, 3).

Edition	Behavior
MaxCompute V1.0	All values in the parentheses must be of the same type.
MaxCompute V2.0	Values in the parentheses can be implicitly converted to the same type.