{"record":{"id":"63d88ac3d928c51f","repo":"prestodb/presto","slug":"numeric-value-out-of-range-63d88a","errorCode":"NUMERIC_VALUE_OUT_OF_RANGE","errorMessage":"smallint addition overflow: %s + %s","messagePattern":"smallint addition overflow: (.+?) \\+ (.+?)","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/type/SmallintOperators.java","lineNumber":72,"sourceCode":"import static io.airlift.slice.Slices.utf8Slice;\nimport static java.lang.Float.floatToRawIntBits;\nimport static java.lang.String.format;\n\npublic final class SmallintOperators\n{\n    private SmallintOperators()\n    {\n    }\n\n    @ScalarOperator(ADD)\n    @SqlType(StandardTypes.SMALLINT)\n    public static long add(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)\n    {\n        try {\n            return Shorts.checkedCast(left + right);\n        }\n        catch (IllegalArgumentException e) {\n            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, format(\"smallint addition overflow: %s + %s\", left, right), e);\n        }\n    }\n\n    @ScalarOperator(SUBTRACT)\n    @SqlType(StandardTypes.SMALLINT)\n    public static long subtract(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)\n    {\n        try {\n            return Shorts.checkedCast(left - right);\n        }\n        catch (IllegalArgumentException e) {\n            throw new PrestoException(NUMERIC_VALUE_OUT_OF_RANGE, format(\"smallint subtraction overflow: %s - %s\", left, right), e);\n        }\n    }\n\n    @ScalarOperator(MULTIPLY)\n    @SqlType(StandardTypes.SMALLINT)\n    public static long multiply(@SqlType(StandardTypes.SMALLINT) long left, @SqlType(StandardTypes.SMALLINT) long right)","sourceCodeStart":54,"sourceCodeEnd":90,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/type/SmallintOperators.java#L54-L90","documentation":"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.","triggerScenarios":"SELECT smallint_col + smallint_col WHERE the sum overflows 32767 or underflows -32768.","commonSituations":"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.","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."],"exampleFix":"// before\nSELECT quantity + adjustment AS total FROM inventory;\n// after\nSELECT CAST(CAST(quantity AS INTEGER) + adjustment AS SMALLINT) AS total FROM inventory;","handlingStrategy":"type-guard","validationCode":"-- Guard: only add when result fits smallint\nSELECT a, b FROM t WHERE a + b BETWEEN -32768 AND 32767; -- note: evaluate on widened type\nSELECT a, b FROM t WHERE CAST(a AS INTEGER) + b BETWEEN -32768 AND 32767;","typeGuard":"SELECT CASE WHEN CAST(a AS INTEGER) + b BETWEEN -32768 AND 32767\n       THEN CAST(a + b AS SMALLINT) END AS safe_sum FROM t;","tryCatchPattern":"// JDBC\ntry { rs = stmt.executeQuery(sumSql); }\ncatch (SQLException e) {\n  if (e.getMessage() != null && e.getMessage().contains(\"smallint addition overflow\")) {\n    // retry with widened INTEGER arithmetic\n  } else throw e;\n}","preventionTips":["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."],"tags":["presto","arithmetic","overflow","smallint"],"backgroundTag":"integer-overflow","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}