What causes "Overflow adding interval year-month values: " in prestodb/presto?

Reduce operand sizes so the sum fits in 32-bit month space

prestodb/presto · error · PrestoException

NUMERIC_VALUE_OUT_OF_RANGE

NUMERIC_VALUE_OUT_OF_RANGE

Error message

Overflow adding interval year-month values:

What it means

Thrown by IntervalDayTimeOperators.add when adding two INTERVAL YEAR TO MONTH values overflows a 32-bit int. Year-month intervals are stored as the number of months in an int; Math.addExact on the two ints throws ArithmeticException when the sum leaves Integer range, wrapped as PrestoException NUMERIC_VALUE_OUT_OF_RANGE. Long.MAX_VALUE (Long.MIN_VALUE / 1000, Long.MIN_VALUE / 1000) boundaries.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/IntervalYearMonthOperators.java:65

import static com.facebook.presto.type.IntervalYearMonthType.INTERVAL_YEAR_MONTH;
import static io.airlift.slice.Slices.utf8Slice;
import static java.lang.Math.toIntExact;

public final class IntervalYearMonthOperators
{
    private IntervalYearMonthOperators()
    {
    }

    @ScalarOperator(ADD)
    @SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH)
    public static long add(@SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH) long left, @SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH) long right)
    {
        try {
            return Math.addExact((int) left, (int) right);
        }
        catch (ArithmeticException e) {
            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, "Overflow adding interval year-month values: " + left + " + " + right);
        }
    }

    @ScalarOperator(SUBTRACT)
    @SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH)
    public static long subtract(@SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH) long left, @SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH) long right)
    {
        try {
            return Math.subtractExact((int) left, (int) right);
        }
        catch (ArithmeticException e) {
            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, "Overflow subtracting interval year-month values: " + left + " - " + right);
        }
    }

    @ScalarOperator(MULTIPLY)
    @SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH)
    public static long multiplyByInteger(@SqlType(StandardTypes.INTERVAL_YEAR_TO_MONTH) long left, @SqlType(StandardTypes.INTEGER) long right)

View on GitHub (pinned to 55bb57d202)

Solutions

Reduce operand sizes so the sum fits in 32-bit month space
Use try(a + b) in SQL to get NULL on overflow instead of a failed query
Filter sentinel/outlier interval values before summing
Catch PrestoException NUMERIC_VALUE_OUT_OF_RANGE in the engine/plugin layer

Example fix

// before
SELECT INTERVAL '200000000' YEAR + INTERVAL '200000000' YEAR; -- > int max months
// after
SELECT try(INTERVAL '200000000' YEAR + INTERVAL '200000000' YEAR); -- NULL, or use smaller intervals

Defensive patterns

Strategy: validation

Validate before calling

-- before adding, ensure month sums fit in 32-bit int
SELECT CASE WHEN abs(cast(a AS bigint) + cast(b AS bigint)) > 2147483647
       THEN NULL ELSE a + b END
-- or simply
SELECT try(a + b)

Type guard

public static boolean safeYearMonthAdd(long monthsA, long monthsB) {
    long sum = monthsA + monthsB; // evaluate in long
    return sum >= Integer.MIN_VALUE && sum <= Integer.MAX_VALUE;
}

Try / catch

try {
    long r = IntervalYearMonthOperators.add(a, b);
} catch (PrestoException e) {
    if (e.getErrorCode().getCode() == StandardErrorCode.NUMERIC_VALUE_OUT_OF_RANGE.getCode()) {
        // return NULL / log and skip row
    } else {
        throw e;
    }
}

Prevention

Remember year-month intervals are int month counts (max ~178M years)
Promote to BIGINT for pre-checks before interval arithmetic
Use try(...) in SQL on untrusted interval data
Sanitize externally loaded month values for sentinels

When it happens

Trigger: ADD operator on two INTERVAL YEAR TO SECOND values whose combined month count exceeds Integer.MAX_VALUE (+2147483647 months, ~178,956,970 years) or is below Integer.MIN_VALUE, e.g. SELECT INTERVAL '150000000' YEAR + INTERVAL '150000000' YEAR.

Common situations: Accidentally treating year-month intervals as day-level magnitudes and summing many of them; programmatic generation of huge intervals; sentinel month values (like Integer.MIN_VALUE) from external data being added.

Related errors

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