When ingesting raw telemetry data into time-series databases like Apache IoTDB, data pipelines often stage numeric values as strings. While standardizing these values using intermediate conversions, you might encounter a subtle bug: casting an out-of-range counter through DOUBLE doesn't fail; instead, it silently clamps the number to 9223372036854775807 (the maximum 64-bit signed integer).

The Problem: Clamping vs. Overflow Errors

Consider an IoT gateway storing large 64-bit counter values in a raw string column:

CREATE TABLE imported_counters (
    gateway_id STRING TAG,
    raw_value STRING FIELD
);

INSERT INTO imported_counters(time, gateway_id, raw_value)
VALUES (1000, 'gw-a', '9223372036854775808'); -- Note: 2^63, exactly 1 greater than INT64 max

If you perform a two-step cast through DOUBLE:

SELECT raw_value,
       CAST(CAST(raw_value AS DOUBLE) AS INT64) AS parsed_value
FROM imported_counters;

IoTDB outputs a valid-looking—yet silently corrupted—value:

+---------------------+---------------------+
| raw_value           | parsed_value        |
+---------------------+---------------------+
| 9223372036854775808 | 9223372036854775807 |
+---------------------+---------------------+

However, running a direct cast to INT64 raises a hard error:

SELECT raw_value, CAST(raw_value AS INT64) AS parsed_value FROM imported_counters;
-- Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Cannot cast 9223372036854775808 to INT64 type

Why Does This Happen?

This discrepancy stems from how different conversion pipelines are implemented internally in Java and SQL engines:

  • STRING to INT64: This pathway uses strict parsing routines equivalent to Long.parseLong(). When the string value exceeds Long.MAX_VALUE (9,223,372,036,854,775,807), an arithmetic overflow is caught immediately, throwing a 701: Cannot cast error.
  • DOUBLE to INT64: This relies on Java Language Specification (JLS §5.1.3) narrowing primitive conversions. In Java, casting any floating-point number greater than or equal to $2^{63}$ into a 64-bit signed integer saturates directly to Long.MAX_VALUE instead of raising an exception.

The Hidden Danger: Double-Precision Drift

Clamping isn't the only risk of routing integer counters through DOUBLE. IEEE-754 double-precision floats only have a 53-bit significand (mantissa), providing roughly 15 to 17 significant decimal digits. Numbers larger than $2^{53}$ (9,007,199,254,740,992) lose precision. Any large 64-bit counter converted to DOUBLE will silently drop its least significant digits before it even reaches the INT64 boundary.

How to Prevent Silent Clamping

1. Direct Casting (Best Practice for Integers)

If the field represents an integer counter or identifier, eliminate the intermediate float cast completely:

SELECT raw_value,
       CAST(raw_value AS INT64) AS parsed_value
FROM imported_counters;

This ensures that any overflow immediately fails the query or processing step rather than corrupting your metrics.

2. Pre-Validating Boundaries with CASE Expressions

If you cannot avoid floating-point inputs (for instance, if the source mixed float and integer payloads into the same string column), guard against truncation and clamping by setting limits before the final cast:

SELECT raw_value,
       CASE 
           WHEN CAST(raw_value AS DOUBLE) >= 9223372036854775807.0 THEN NULL
           WHEN CAST(raw_value AS DOUBLE) <= -9223372036854775808.0 THEN NULL
           ELSE CAST(CAST(raw_value AS DOUBLE) AS INT64)
       END AS parsed_value
FROM imported_counters;

Note: Due to float precision limits around $2^{63}$, safe thresholds should ideally be chosen within the safe range of integer precision for floats (up to $2^{53} - 1$).

3. Validate Ingress at the Ingestion / ETL Layer

When high-cardinality counters or exact sequence IDs pass through ingestion gateways, do not let generic normalization routines generalize all numbers into DOUBLE. Implement schema-aware routing in your ETL layer (e.g., Apache NiFi, Telegraf, or custom microservices) to parse unsigned 64-bit or signed 64-bit integers directly without intermediate floating-point transformations.