Understanding the Apache IoTDB BETWEEN Clause Exception

When working with report builders or ORM tools, SQL queries are frequently generated dynamically. A common artifact of these query generators is appending a decimal suffix (like .0) to numeric range bounds. While most modern database planners automatically coerce floating-point literals to match the target column's data type, Apache IoTDB 2.0.8 exhibits unexpected behavior when combining BETWEEN predicates with decimal literals on an INT64 column.

Specifically, a query using BETWEEN 7.0 AND 8.0 on an integer field triggers a server-side ClassCastException during planning, whereas the logically equivalent >= 7.0 AND <= 8.0 executes seamlessly.

The Error Breakdown

Consider a standard table schema and data set in Apache IoTDB:

CREATE TABLE batch_ranges (line_id STRING TAG, batch_no INT64 FIELD);
INSERT INTO batch_ranges(time, line_id, batch_no) VALUES (1000, 'line-a', 6);
INSERT INTO batch_ranges(time, line_id, batch_no) VALUES (2000, 'line-a', 7);
INSERT INTO batch_ranges(time, line_id, batch_no) VALUES (3000, 'line-a', 8);
INSERT INTO batch_ranges(time, line_id, batch_no) VALUES (4000, 'line-a', 9);

Executing the following query with BETWEEN:

SELECT time, batch_no FROM batch_ranges WHERE batch_no BETWEEN 7.0 AND 8.0 ORDER BY time;

Results in this exception during query planning:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 301: class org.apache.iotdb.db.queryengine.plan.relational.sql.ast.DoubleLiteral cannot be cast to class org.apache.iotdb.db.queryengine.plan.relational.sql.ast.LongLiteral

However, rewriting the query using explicit range comparisons executes without issue:

SELECT time, batch_no FROM batch_ranges WHERE batch_no >= 7.0 AND batch_no <= 8.0 ORDER BY time;

Why Does This Happen? (Is it an IoTDB Bug?)

Yes, this issue stems from a bug/limitation in the Apache IoTDB relational query engine planner in version 2.0.8.

During query AST (Abstract Syntax Tree) optimization and planning:

  • Comparison Operators (>=, <=): The relational engine applies type coercion rules. It evaluates the double literal (DoubleLiteral) against the target column type (INT64) and converts or promotes types cleanly before evaluation.
  • BETWEEN Predicates: The planner's rewrite rule for BetweenPredicate attempts a direct cast of the bound AST nodes to LongLiteral when targeting integer fields, rather than applying general type coercion or transforming the AST node into an acceptable literal type first. Because DoubleLiteral does not inherit from LongLiteral, Java throws a ClassCastException.

Solutions and Workarounds

1. Rewrite USING Comparison Operators

If you control the SQL generator or report builder settings, configure it to output standard binary comparison operators instead of BETWEEN. This bypasses the uncoerced AST path in the planner:

WHERE batch_no >= 7.0 AND batch_no <= 8.0

2. Pass Integer Literals directly

Ensure that report builder parameter formatters sanitize or cast float values to pure integers when querying INT64 fields:

WHERE batch_no BETWEEN 7 AND 8

3. Explicit CAST in the Query

If you cannot modify how the report generator formats bounds, explicitly cast the floating-point bounds within the SQL query:

WHERE batch_no BETWEEN CAST(7.0 AS INT64) AND CAST(8.0 AS INT64)

Summary

The DoubleLiteral cannot be cast to LongLiteral exception when using BETWEEN on an INT64 column is an Apache IoTDB 2.0.8 query planner bug. Until a fix is patched in a subsequent release, converting BETWEEN to >= AND <= or stripping the decimal points from your query bounds will resolve the crash immediately.