What causes "decimal overflow: " in prestodb/presto?

CAST the operand to a wider decimal before the operation, e.g. CAST(x AS DECIMAL(38,s)).

prestodb/presto · error · PrestoException

NUMERIC_VALUE_OUT_OF_RANGE

NUMERIC_VALUE_OUT_OF_RANGE

Error message

decimal overflow:

What it means

A decimal rescale round-trip in MathFunctions (e.g. used by decimal arithmetic/sqrt paths) produced a value that no longer fits the declared result precision, so NUMERIC_VALUE_OUT_OF_RANGE 'decimal overflow: <num>' is thrown. Presto decimals are fixed-precision; the result would exceed decimal(rp,s) bounds.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/MathFunctions.java:1346

        @SqlType("decimal(rp, s)")
        @Constraint(variable = "rp", expression = "min(38, p + 1)")
        public static Slice roundNLong(
                @LiteralParameter("s") long numScale,
                @LiteralParameter("rp") long resultPrecision,
                @SqlType("decimal(p, s)") Slice num,
                @SqlType(StandardTypes.INTEGER) long decimals)
        {
            if (decimals >= numScale) {
                return num;
            }
            int rescaleFactor = ((int) numScale) - (int) decimals;
            try {
                Slice result = rescale(rescale(num, -rescaleFactor), rescaleFactor);
                throwIfOverflows(result, ((int) resultPrecision));
                return result;
            }
            catch (ArithmeticException e) {
                throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, "decimal overflow: " + num, e);
            }
        }

        @LiteralParameters({"p", "s", "rp"})
        @SqlType("decimal(rp, s)")
        @Constraint(variable = "rp", expression = "min(38, p + 1)")
        public static Slice roundNShortLong(
                @LiteralParameter("s") long numScale,
                @LiteralParameter("rp") long resultPrecision,
                @SqlType("decimal(p, s)") long num,
                @SqlType(StandardTypes.INTEGER) long decimals)
        {
            return roundNLong(numScale, resultPrecision, unscaledDecimal(num), decimals);
        }
    }

    @ScalarFunction("truncate")
    @Description("round to integer by dropping digits after decimal point")

View on GitHub (pinned to 55bb57d202)

Solutions

CAST the operand to a wider decimal before the operation, e.g. CAST(x AS DECIMAL(38,s)).
Reduce scale (fewer fractional digits) so precision fits within 38.
Catch the error and handle in TRY: SELECT TRY(sqrt(x)) to get NULL instead of failing.
Re-examine the query's expected magnitudes; scale down inputs if overflow is expected.

Example fix

-- before
SELECT sqrt(dec_col) FROM t; -- dec_col DECIMAL(38,10) overflows
-- after
SELECT CAST(sqrt(CAST(dec_col AS DECIMAL(38,4))) AS DECIMAL(38,10)) FROM t;

Defensive patterns

Strategy: try-catch

Validate before calling

-- ensure the operand fits the result precision before computing
SELECT * FROM t WHERE abs(CAST(x AS DECIMAL(38,s))) > <max_value_for_precision>;

Try / catch

SELECT TRY(sqrt(dec_col)) AS sqrt_x FROM t; -- NULL instead of NUMERIC_VALUE_OUT_OF_RANGE

Prevention

Declare decimal columns with headroom (precision < 38) when results feed further math.
CAST operands to DECIMAL(38,s) with reduced scale before heavy math.
Use TRY() around decimal math on unbounded user data.

When it happens

Trigger: Invoking decimal-returning math functions (e.g. sqrt/abs/inverse on DECIMAL) with values whose result needs more precision digits than the type allows (max 38), or arithmetic that overflows after rescale.

Common situations: Very large DECIMAL(p,s) inputs pushed through functions whose result precision rule (like min(38, p+1)) is exceeded; overly tight column/cast precision on computed expressions; large monetary computations.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/f859ea1c544a6159. Report an issue: GitHub.