The ROUND function rounds input values based on the number of decimal places that you specify. However, when rounding a numeric literal, you may see unexpected results — for example, round(0.25375, 4) returns 0.2537 instead of the expected 0.2538.
Why this happens
Before executing ROUND, MaxCompute converts numeric literals to the DOUBLE data type. DOUBLE is a floating-point type that cannot represent all decimal values exactly. During this conversion, 0.25375 becomes 0.2536999999..., which rounds down to 0.2537.
This issue only affects numeric literals passed directly to the function. When ROUND processes table column data, it reads the column's actual data type, so the precision issue does not occur.
Solution
Cast the input value to DECIMAL before passing it to ROUND. Unlike DOUBLE, DECIMAL stores exact values, so the conversion does not introduce precision loss.
The exact result depends on whether Hive-compatible mode is enabled.
With set odps.sql.hive.compatible=false;
Both decimal (no precision specified) and decimal(20,5) return the correct result:
select round(cast(0.25375 as decimal), 4);
-- Returns: 0.2538
select round(cast(0.25375 as decimal(20,5)), 4);
-- Returns: 0.2538With set odps.sql.hive.compatible=true;
Use decimal(p,s) with explicit precision and scale. Using decimal without a precision specification returns 0 in Hive-compatible mode:
select round(cast(0.25375 as decimal), 4);
-- Returns: 0
select round(cast(0.25375 as decimal(20,5)), 4);
-- Returns: 0.2538In Hive-compatible mode, always use decimal(p,s) with explicit precision and scale to get the expected result.