What causes "Unable to cast %s to bigint" in prestodb/presto?

Filter out non-finite and out-of-range rows first: WHERE is_finite(x) AND x BETWEEN -9223372036854775808 AND 9223372036854775807.

prestodb/presto · error · PrestoException

INVALID_CAST_ARGUMENT

INVALID_CAST_ARGUMENT

Error message

Unable to cast %s to bigint

What it means

castToLong implements CAST(REAL AS BIGINT) using Guava's DoubleMath.roundToLong with HALF_UP rounding. roundToLong throws ArithmeticException when the float is NaN or its magnitude exceeds the BIGINT range; the operator rethrows it as INVALID_CAST_ARGUMENT including the offending float value.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/RealOperators.java:127

    }

    @ScalarOperator(CAST)
    @LiteralParameters("x")
    @SqlType("varchar(x)")
    public static Slice castToVarchar(@SqlType(StandardTypes.REAL) long value)
    {
        return utf8Slice(String.valueOf(intBitsToFloat((int) value)));
    }

    @ScalarOperator(CAST)
    @SqlType(StandardTypes.BIGINT)
    public static long castToLong(@SqlType(StandardTypes.REAL) long value)
    {
        try {
            return DoubleMath.roundToLong(intBitsToFloat((int) value), HALF_UP);
        }
        catch (ArithmeticException e) {
            throw new PrestoException(INVALID_CAST_ARGUMENT, format("Unable to cast %s to bigint", intBitsToFloat((int) value)), e);
        }
    }

    @ScalarOperator(CAST)
    @SqlType(StandardTypes.INTEGER)
    public static long castToInteger(@SqlType(StandardTypes.REAL) long value)
    {
        try {
            return DoubleMath.roundToInt(intBitsToFloat((int) value), HALF_UP);
        }
        catch (ArithmeticException e) {
            throw new PrestoException(INVALID_CAST_ARGUMENT, format("Unable to cast %s to integer", intBitsToFloat((int) value)), e);
        }
    }

    @ScalarOperator(CAST)
    @SqlType(StandardTypes.SMALLINT)
    public static long castToSmallint(@SqlType(StandardTypes.REAL) long value)

View on GitHub (pinned to 55bb57d202)

Solutions

Filter out non-finite and out-of-range rows first: WHERE is_finite(x) AND x BETWEEN -9223372036854775808 AND 9223372036854775807.
Use COALESCE/NULLIF to map NaN/Inf to NULL before casting, e.g. CAST(NULLIF(x, infinity()) AS BIGINT).
If the data is genuinely huge, keep it as REAL/DOUBLE instead of casting to BIGINT.

Example fix

// before
SELECT CAST(metric AS BIGINT) FROM readings; -- NaN rows fail
// after
SELECT CAST(NULLIF(metric, infinity()) AS BIGINT) FROM readings WHERE is_finite(metric);

Defensive patterns

Strategy: validation

Validate before calling

-- guard before casting REAL to BIGINT
SELECT CAST(x AS BIGINT)
FROM t
WHERE is_finite(x) AND x BETWEEN -9223372036854775808 AND 9223372036854775807;

Type guard

boolean isCastableToBigint(double v) {
    return !Double.isNaN(v) && !Double.isInfinite(v) && v >= -9.223372036854776E18 && v < 9.223372036854776E18;
}

Try / catch

try {
    return castToLong(value);
} catch (PrestoException e) {
    if (INVALID_CAST_ARGUMENT.equals(e.getErrorCode()) && e.getMessage().startsWith("Unable to cast")) {
        return null; // or a configured sentinel
    }
    throw e;
}

Prevention

Always filter or NULLIF NaN/Infinity before casting REAL to an integer type.
Range-check against the target integer type's bounds; prefer BIGINT for large magnitudes.
Sanitize upstream data pipelines so NaN/Inf never reach cast expressions.

When it happens

Trigger: CAST(real_col AS BIGINT) where the value is NaN, +Infinity/-Infinity, or outside [-2^63, 2^63) after rounding (e.g., 3.5e38 or 9.3e18-ish overflow).

Common situations: Aggregations (SUM of large reals) overflowing bigint range; imported sensor/measurement data containing NaN/Infinity; scientific data that was always out of bigint range.

Related errors

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