Introduction

When working with Apache IoTDB's tree model, wildcard queries offer a convenient way to select data across multiple devices simultaneously. However, developer confusion often arises when combining wildcards in the FROM clause with unqualified measurement names in the WHERE clause. In this article, we will explore why queries like WHERE temperature > 15 might filter out timestamps unexpectedly and how to resolve this issue using proper path qualification or ALIGN BY DEVICE.

The Scenario: Wildcard Query Dropping Expected Timestamps

Consider a schema where we monitor two pumps under a specific line (line_beta). Both pump_a and pump_b have a temperature measurement:

CREATE TIMESERIES root.inspection_lines.line_beta.pump_a.temperature WITH DATATYPE=DOUBLE, ENCODING=PLAIN;
CREATE TIMESERIES root.inspection_lines.line_beta.pump_b.temperature WITH DATATYPE=DOUBLE, ENCODING=PLAIN;

INSERT INTO root.inspection_lines.line_beta.pump_a(timestamp, temperature) VALUES (1000, 10.0);
INSERT INTO root.inspection_lines.line_beta.pump_a(timestamp, temperature) VALUES (2000, 20.0);
INSERT INTO root.inspection_lines.line_beta.pump_b(timestamp, temperature) VALUES (1000, 30.0);
INSERT INTO root.inspection_lines.line_beta.pump_b(timestamp, temperature) VALUES (2000, 40.0);

When running an unqualified wildcard filter query:

SELECT temperature
FROM root.inspection_lines.line_beta.*
WHERE temperature > 15;

You might expect timestamp 1000 to be included because pump_b has a temperature of 30.0 (> 15). However, the query returns only timestamp 2000:

+-----------------------------+--------------------------------------------------+--------------------------------------------------+
| Time                        |root.inspection_lines.line_beta.pump_a.temperature|root.inspection_lines.line_beta.pump_b.temperature|
+-----------------------------+--------------------------------------------------+--------------------------------------------------+
|1970-01-01T08:00:02.000+08:00| 20.0                                             | 40.0                                             |
+-----------------------------+--------------------------------------------------+--------------------------------------------------+

Why Does IoTDB Drop Timestamp 1000?

In IoTDB's default tabular output format (time-aligned columns), an unqualified measurement name in the WHERE clause expands across all target paths matched by the FROM wildcard pattern.

Specifically, the query predicate WHERE temperature > 15 is implicitly expanded into a conjunction (AND logic) across all matched series:

WHERE root.inspection_lines.line_beta.pump_a.temperature > 15 
  AND root.inspection_lines.line_beta.pump_b.temperature > 15

Because pump_a's temperature at timestamp 1000 is 10.0 (which is not greater than 15), the entire row is filtered out. At timestamp 2000, both pump_a (20.0) and pump_b (40.0) satisfy the condition, so the row is retained.

Solutions and Best Practices

Solution 1: Use ALIGN BY DEVICE

If you want the predicate evaluated independently for each device, append ALIGN BY DEVICE to your query. This transforms the result set into device-centric rows and applies the WHERE filter per device:

SELECT temperature
FROM root.inspection_lines.line_beta.*
WHERE temperature > 15
ALIGN BY DEVICE;

This evaluates pump_a and pump_b separately and returns pump_b at timestamp 1000 as well as both pumps at timestamp 2000.

Solution 2: Use Fully Qualified Paths in WHERE

If you specifically want to filter the entire aligned dataset based on one particular device's measurement, specify the fully qualified path in the WHERE clause:

SELECT temperature
FROM root.inspection_lines.line_beta.*
WHERE root.inspection_lines.line_beta.pump_b.temperature > 15;

Summary

When executing wildcard queries in Apache IoTDB:

  • Default Alignment: Unqualified predicates expand across all wildcard-matched devices using implicit AND conjunctions.
  • Device-Level Filtering: Use ALIGN BY DEVICE when predicates should filter device records individually.
  • Targeted Global Filtering: Use fully qualified paths in the WHERE clause to base filtering on a single, specific series.