Understanding the "Default result type must be the same" Error in Apache IoTDB

Apache IoTDB 2.0 introduced the highly anticipated Table Model, bringing relational-like query capabilities to time-series data. However, if you are migrating from more lenient SQL dialects, you might encounter strict type-checking hurdles. One common scenario is data cleaning, where you want to cast a string to a numeric type if it's valid, and return NULL otherwise.

When executing a query like this:

SELECT time, txt, 
       CASE WHEN txt != 'bad' THEN CAST(txt AS DOUBLE) 
            ELSE null 
       END AS n 
FROM replacement_case_null;

IoTDB throws the following error:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Default result type must be the same as WHEN result types: unknown vs DOUBLE

Why Does Type Inference Fail for NULL in Apache IoTDB?

In many relational database management systems (RDBMS), the query parser automatically infers that an untyped NULL in a CASE expression should inherit the data type of the other branches (in this case, DOUBLE). However, Apache IoTDB's Table Model enforces a strict, strongly-typed system during its query planning phase.

Here is why the error occurs:

  • The "Unknown" Type: A raw, un-casted NULL literal is treated as having an UNKNOWN (or untyped) data type by the parser.
  • Strict Branch Matching: Under IoTDB's strict type-matching rules for conditional expressions, the return type of the ELSE branch must be structurally compatible with the return type of the THEN branch.
  • No Implicit Coercion: The query compiler does not automatically coerce the UNKNOWN type of the literal NULL to DOUBLE, resulting in the mismatch: unknown vs DOUBLE.

The Solution: Explicitly Cast NULL

To resolve this issue, you must explicitly declare the data type of the NULL value in your ELSE branch. This provides the query planner with the exact type signature it needs to construct the execution plan.

Modify your query to cast the NULL to a DOUBLE:

SELECT time, txt, 
       CASE WHEN txt != 'bad' THEN CAST(txt AS DOUBLE) 
            ELSE CAST(null AS DOUBLE) 
       END AS n 
FROM replacement_case_null 
ORDER BY time;

This query compiles successfully and yields the expected results:

+-----------------------------+----+----+
| time                        | txt| n  |
+-----------------------------+----+----+
|1970-01-01T08:00:01.000+08:00|12.5|12.5|
|1970-01-01T08:00:02.000+08:00| bad|null|
|1970-01-01T08:00:03.000+08:00|13.5|13.5|
+-----------------------------+----+----+

Best Practices for Data Cleaning in IoTDB Table Model

When working with the Apache IoTDB Table Model, keep these best practices in mind to avoid similar type mismatch errors:

  • Always type-cast NULLs in conditionals: Whenever you use CASE WHEN, COALESCE, or similar functions where branches can return NULL, explicitly cast the NULL to the target type (e.g., CAST(NULL AS DOUBLE)).
  • Leverage strict schema validation: Since IoTDB is designed for high-performance time-series ingestion, keeping your data types consistent at the ingestion layer is always preferable to performing heavy string-to-numeric casting at query time.