When writing complex dynamic queries or filtering datasets based on user inputs, it is not uncommon to see lists containing NULL values. However, in Apache IoTDB (specifically versions like 2.0.x with the Table Model), you might encounter an unexpected internal server error when using an untyped NULL inside an IN (...) clause.

The Problem: ClassCastException with Untyped NULL

Consider a typical table created in IoTDB with an integer field:

CREATE DATABASE inspection_selection;
USE inspection_selection;
CREATE TABLE inspection_items(station STRING TAG, priority INT32 FIELD);

INSERT INTO inspection_items(time,station,priority) VALUES
  (1000, 'west', 1),
  (2000, 'west', 2),
  (3000, 'west', 3);

Executing a membership check that includes a literal NULL triggers an internal engine failure:

SELECT time, priority
FROM inspection_items
WHERE priority IN (1, NULL)
ORDER BY time;

Instead of completing the query using three-valued SQL logic (where x IN (1, NULL) resolves to true if x = 1 and unknown otherwise), IoTDB crashes with the following error:

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

Why Does This Happen?

This is a bug in the AST (Abstract Syntax Tree) analysis and optimization layer of IoTDB's relational query engine. During expression planning for the IN predicate, the planner assumes that all elements within an integer list can be directly cast down to numeric AST literals (such as LongLiteral) to build an optimized lookup set.

Because the untyped literal is parsed as a NullLiteral, the engine attempts an invalid Java cast rather than resolving type coercion or handling three-valued SQL semantics properly.

Workarounds and Solutions

While this issue has been flagged for resolution in newer patches of Apache IoTDB, you can prevent your queries from breaking using the following approaches.

1. Explicitly Cast the NULL Value

Explicitly assigning a type to the NULL allows the query planner to recognize the data type and bypass the problematic AST cast:

SELECT time, priority
FROM inspection_items
WHERE priority IN (1, CAST(NULL AS INT32))
ORDER BY time;

2. Sanitize Inputs at the Application Level

In standard SQL, comparing any value against NULL using = or IN yields UNKNOWN. An element with NULL in an IN list never causes a row to match that wasn't already matched by another literal value. Unless you are specifically relying on NOT IN semantics, stripping out null values before generating the SQL query is often the best practice:

# Example in Python
priorities = [1, None]
cleaned_priorities = [p for p in priorities if p is not None]

# Query becomes: WHERE priority IN (1)

3. Handle NULL Using OR IS NULL

If your goal was to match records where priority is 1 or where priority is missing, remember that SQL does not match NULL via IN (NULL). You should explicitly use the IS NULL predicate instead:

SELECT time, priority
FROM inspection_items
WHERE priority IN (1) OR priority IS NULL
ORDER BY time;

Conclusion

An untyped NULL inside an IN list leaking a Java ClassCastException is a query planner bug in Apache IoTDB. Until a formal patch resolves how NullLiteral is handled in expression simplification, either filter out nulls in your application layer or use explicit casting with CAST(NULL AS <TYPE>).