DECIMAL data type - MaxCompute - Alibaba Cloud Documentation Center

Overview

DECIMAL is a data type designed for high-precision decimal arithmetic, widely used in financial and scientific computing scenarios that require strict precision. In MaxCompute, the behavior of the DECIMAL type varies depending on the data type edition of the project:

DECIMAL 2.0 data type disabled

The DECIMAL type uses a fixed-precision format with a default of DECIMAL(54,18). The precision (maximum number of digits) and scale (number of decimal places) cannot be specified. Integer part range: -10³⁶+1 to 10³⁶-1. Decimal part: accurate to 10^-18. To use this data type, set the following flag:
```
SET odps.sql.decimal.odps2=false;
```
DECIMAL 2.0 data type enabled

Defaults to DECIMAL(38,18). You can specify the precision (maximum number of digits) and scale (number of decimal places). Based on precision requirements, the following two categories are available:
- Standard DECIMAL: precision range is [1,38], scale range is [0, precision]. To use this data type, set the following flags:
```
SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;
```
  Scale limits:
  - If odps.sql.decimal2.extended.scale.enable=false (default):
    
    The supported scale range is [0, 18]. All computation results are truncated to 18 decimal places.
  - If odps.sql.decimal2.extended.scale.enable=true:
    
    High-precision computation with a scale range of [0, 38] is supported.
  - If odps.sql.decimal256.auto.promotion=true; is set to enable automatic type promotion:
    
    The system automatically promotes computation results to the higher-precision DECIMAL256 type as needed, supporting a wider range of high-precision calculations.
- DECIMAL256: precision range is [39,76], scale range is [0, precision]. To use this data type, set the following flags:
```
SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;
SET odps.sql.decimal256.enable=true;
```

Note

When odps.sql.decimal256.enable=true is enabled, odps.sql.type.system.odps2 and odps.sql.decimal.odps2 must also be set to true. Otherwise, queries return an error.

Considerations

A single table cannot contain both old and new DECIMAL data types.
With the DECIMAL 2.0 data type enabled, when Hive-compatible mode is enabled (SET odps.sql.hive.compatible=true;) or BigQuery-compatible mode is enabled (SET odps.sql.bigquery.compatible=true;), DECIMAL(precision, scale) values are rounded during data import (Tunnel Upload) and SQL computations if the number of decimal places exceeds the scale. If the integer part exceeds the limit, no error is returned, but the input data becomes NULL.
When odps.sql.decimal.tostring.trimzero is set to true, trailing zeros after the decimal point are removed. When set to false, trailing zeros are preserved. This applies only to values read from tables, not to static values. Default: true.

Use cases

Data type	Configuration	Description
Legacy DECIMAL	`SET odps.sql.decimal.odps2=false;`	DECIMAL 2.0 data type is disabled. Precision and scale cannot be specified. Default: `DECIMAL(54,18)`.
Standard DECIMAL	`SET odps.sql.decimal.odps2=true;` `SET odps.sql.decimal2.extended.scale.enable=false;`	Standard DECIMAL type with precision up to 38. During computation, the scale is limited to 18.
Standard DECIMAL	`SET odps.sql.decimal.odps2=true;` `SET odps.sql.decimal2.extended.scale.enable=true;`	Standard DECIMAL type with precision up to 38. Supports high-precision computation with a scale range of `[0, 38]`.
DECIMAL256	`SET odps.sql.type.system.odps2=true;` `SET odps.sql.decimal.odps2=true;` `SET odps.sql.decimal256.enable=true;` `SET odps.sql.decimal2.extended.scale.enable=false;` `SET odps.sql.decimal256.auto.promotion=false;`	Enables DECIMAL256, but retains the original behavior when the precision of DECIMAL values involved in computations is 38 or less. Suitable for upgrading from standard DECIMAL (scale range `[0,18]`) to DECIMAL256 without changing existing job behavior.
	`SET odps.sql.type.system.odps2=true;` `SET odps.sql.decimal.odps2=true;` `SET odps.sql.decimal256.enable=true;` `SET odps.sql.decimal2.extended.scale.enable=true;` `SET odps.sql.decimal256.auto.promotion=false;`	Enables DECIMAL256, but retains the original behavior when the precision of DECIMAL values involved in computations is 38 or less. Suitable for upgrading from standard DECIMAL (scale range `[0, 38]`) to DECIMAL256 without changing existing job behavior.
	`SET odps.sql.type.system.odps2=true;` `SET odps.sql.decimal.odps2=true;` `SET odps.sql.decimal256.enable=true;` `SET odps.sql.decimal2.extended.scale.enable=true;` `SET odps.sql.decimal256.auto.promotion=true;`	Enables DECIMAL256 with full capabilities. Suitable for new projects with no backward-compatibility requirements.

Examples

DECIMAL 2.0 data type disabled

Example 1: The DECIMAL type defaults to DECIMAL(54,18) and cannot be adjusted.

-- Disable the DECIMAL 2.0 data type.
SET odps.sql.decimal.odps2=false;

-- Column a is of the DECIMAL type with fixed precision/scale (54,18). Adjustment is not supported.
CREATE OR REPLACE TABLE foo_t(a DECIMAL);

-- The following CREATE statement returns an error because specifying precision/scale is not supported in this mode.
CREATE OR REPLACE TABLE foo_t(a DECIMAL(5,2));
-- Error: FAILED: ODPS-0130071:[1,33] Semantic analysis exception - precision and scale is not currently supported in current mode, 'SET odps.sql.decimal.odps2=true' to enable

DECIMAL 2.0 data type enabled

Example 1: The DECIMAL type supports specifying precision and scale.

-- Enable the DECIMAL 2.0 data type.
SET odps.sql.decimal.odps2=true;

CREATE OR REPLACE TABLE foo_t(a DECIMAL(4,2));

Example 2: When precision and scale are not specified for the DECIMAL type, the default value depends on whether Hive-compatible mode is enabled. With Hive-compatible mode disabled, the default is DECIMAL(38,18). With Hive-compatible mode enabled, the default is DECIMAL(10,0).

-- Enable the DECIMAL 2.0 data type with Hive-compatible mode disabled.
SET odps.sql.decimal.odps2=true;
SET odps.sql.hive.compatible=false;

CREATE OR REPLACE TABLE foo_t(a DECIMAL);

-- The data type of column a is DECIMAL(38,18).
DESC foo_t;
-- Result:
+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$odps_pd_testcloud_com                             |
| Project:                  xxxx                                             |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2026-04-21 13:36:21                                      |
| LastDDLTime:              2026-04-21 13:36:21                                      |
| LastModifiedTime:         2026-04-21 13:36:21                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| a               | decimal(38,18) |       |                                             |
+------------------------------------------------------------------------------------+

-- Enable the DECIMAL 2.0 data type with Hive-compatible mode enabled.
SET odps.sql.decimal.odps2=true;
SET odps.sql.hive.compatible=true;

CREATE OR REPLACE TABLE foo_t(a DECIMAL);

-- The data type of column a is DECIMAL(10,0).
DESC foo_t;
-- Result:
+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$odps_pd_testcloud_com                             |
| Project:                  ***_project                                             |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2026-04-21 13:38:04                                      |
| LastDDLTime:              2026-04-21 13:38:04                                      |
| LastModifiedTime:         2026-04-21 13:38:04                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| a               | decimal(10,0) |       |                                             |
+------------------------------------------------------------------------------------+

Example 3: For standard DECIMAL, the precision range is [1,38] and the scale range is [0, precision]. To enable high-precision computation with a scale range of [0,38], set SET odps.sql.decimal2.extended.scale.enable=true;.

SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;  -- Enable the DECIMAL 2.0 data type.
SET odps.sql.decimal2.extended.scale.enable=true; -- Enable high-precision computation with a scale range of [0,38].

-- Multiply two DECIMAL values. The result data type is decimal(22,19).
EXPLAIN SELECT CAST(1bd AS DECIMAL(10,9))*CAST(1bd AS DECIMAL(11,10));
-- Result:
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    VALUES: _c0 : {1}
        Statistics: Num rows: 1.0, Data size: 16.0
        FS: output: Screen
            schema:
              _c0 (decimal(22,19))

            Statistics: Num rows: 1.0, Data size: 16.0

-- Set the flag to false. The output type scale is truncated to 18, resulting in decimal(22,18).
SET odps.sql.decimal2.extended.scale.enable=false; -- Disable extended scale. Scale range: [0,18].
EXPLAIN SELECT CAST(1bd AS DECIMAL(10,9))*CAST(1bd AS DECIMAL(11,10));
-- Result:
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    VALUES: _c0 : {1}
        Statistics: Num rows: 1.0, Data size: 16.0
        FS: output: Screen
            schema:
              _c0 (decimal(22,18))

            Statistics: Num rows: 1.0, Data size: 16.0

Example 4: For DECIMAL256, the precision range is [39,76] and the scale range is [0, precision].

SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;
SET odps.sql.decimal256.enable=true;

CREATE OR REPLACE TABLE foo_t(a decimal(60,10));

EXPLAIN SELECT * FROM foo_t;
-- Result:
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    Data source: ***_project.foo_t
    TS: muze_project.foo_t
        Statistics: Num rows: 1.0, Data size: 16.0
        FS: output: Screen
            schema:
              a (decimal(60,10))

            Statistics: Num rows: 1.0, Data size: 16.0

Example 5: When two standard DECIMAL values are multiplied, the result is a standard DECIMAL type by default. If the result exceeds the range, it is truncated.

SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;

-- The result type is decimal(38,5) because it is truncated to fit the standard DECIMAL range.
EXPLAIN SELECT CAST(1bd AS DECIMAL(30,5)) * CAST(1bd AS DECIMAL(30,0));
-- Result:
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    VALUES: _c0 : {1}
        Statistics: Num rows: 1.0, Data size: 16.0
        FS: output: Screen
            schema:
              _c0 (decimal(38,5))

            Statistics: Num rows: 1.0, Data size: 16.0

When DECIMAL256 is enabled and SET odps.sql.decimal256.auto.promotion=true; is set to enable automatic type promotion, the system automatically promotes computation results to higher-precision DECIMAL256 types, avoiding truncation and preserving full precision.

SET odps.sql.type.system.odps2=true;
SET odps.sql.decimal.odps2=true;
SET odps.sql.decimal256.enable=true;
SET odps.sql.decimal256.auto.promotion=true;

-- The result type is decimal(61,5). The product of two standard DECIMAL values is promoted to the DECIMAL256 range.
EXPLAIN SELECT CAST(1bd AS DECIMAL(30,5)) * CAST(1bd AS DECIMAL(30,0));
-- Result:
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    VALUES: _c0 : {1}
        Statistics: Num rows: 1.0, Data size: 16.0
        FS: output: Screen
            schema:
              _c0 (decimal(61,5))

            Statistics: Num rows: 1.0, Data size: 16.0