What causes "smallint addition overflow: %s + %s" in prestodb/presto?

Cast operands to INTEGER or BIGINT before adding: CAST(a AS INTEGER) + CAST(b AS INTEGER).

prestodb/presto · error · PrestoException

NUMERIC_VALUE_OUT_OF_RANGE

NUMERIC_VALUE_OUT_OF_RANGE

Error message

smallint addition overflow: %s + %s

What it means

Thrown by the SMALLINT + SMALLINT operator when the mathematical sum exceeds the 16-bit range [-32768, 32767]. Guava Shorts.checkedCast throws IllegalArgumentException and Presto rethrows NUMERIC_VALUE_OUT_OF_RANGE. Presto smallint arithmetic stays in smallint type, so no silent promotion to int occurs.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/SmallintOperators.java:72

import static io.airlift.slice.Slices.utf8Slice;
import static java.lang.Float.floatToRawIntBits;
import static java.lang.String.format;

public final class SmallintOperators
{
    private SmallintOperators()
    {
    }

    @ScalarOperator(ADD)
    @SqlType(StandardTypes.SMALLINT)
    public static long add(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)
    {
        try {
            return Shorts.checkedCast(left + right);
        }
        catch (IllegalArgumentException e) {
            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, format("smallint addition overflow: %s + %s", left, right), e);
        }
    }

    @ScalarOperator(SUBTRACT)
    @SqlType(StandardTypes.SMALLINT)
    public static long subtract(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)
    {
        try {
            return Shorts.checkedCast(left - right);
        }
        catch (IllegalArgumentException e) {
            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, format("smallint subtraction overflow: %s - %s", left, right), e);
        }
    }

    @ScalarOperator(MULTIPLY)
    @SqlType(StandardTypes.SMALLINT)
    public static long multiply(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)

View on GitHub (pinned to 55bb57d202)

Solutions

Cast operands to INTEGER or BIGINT before adding: CAST(a AS INTEGER) + CAST(b AS INTEGER).
Use try_add(a, b) to return NULL on overflow instead of failing the query.
Guard with a CASE: CASE WHEN a + b BETWEEN -32768 AND 32767 THEN a + b END.

Example fix

// before
SELECT quantity + adjustment AS total FROM inventory;
// after
SELECT CAST(CAST(quantity AS INTEGER) + adjustment AS SMALLINT) AS total FROM inventory;

Defensive patterns

Strategy: type-guard

Validate before calling

-- Guard: only add when result fits smallint
SELECT a, b FROM t WHERE a + b BETWEEN -32768 AND 32767; -- note: evaluate on widened type
SELECT a, b FROM t WHERE CAST(a AS INTEGER) + b BETWEEN -32768 AND 32767;

Type guard

SELECT CASE WHEN CAST(a AS INTEGER) + b BETWEEN -32768 AND 32767
       THEN CAST(a + b AS SMALLINT) END AS safe_sum FROM t;

Try / catch

// JDBC
try { rs = stmt.executeQuery(sumSql); }
catch (SQLException e) {
  if (e.getMessage() != null && e.getMessage().contains("smallint addition overflow")) {
    // retry with widened INTEGER arithmetic
  } else throw e;
}

Prevention

Widen to INTEGER/BIGINT for intermediate arithmetic.
Use try_add for overflow-tolerant sums.
Audit smallint columns for values near ±32767.
Document that Presto smallint arithmetic does not auto-promote.

When it happens

Trigger: SELECT smallint_col + smallint_col WHERE the sum overflows 32767 or underflows -32768.

Common situations: Summing two smallint columns that hold near-max values (year offsets, counters); accumulating smallint values across joins without widening first; porting SQL from engines that promote to int implicitly.

Related errors

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