Inaccurate Results from Apache IoTDB count() with WHERE Condition on Boolean Series

by

Summary

This incident analyzes incorrect count() results in Apache IoTDB when applying a WHERE status = false filter on a Boolean time series. Although the raw data contains 9 false values, IoTDB returns 6, leading to confusion about whether this is a defect or a query‑logic issue. The root cause lies in how IoTDB handles aligned devices, boolean filtering, and null‑row elimination during aggregation.

Root Cause

The incorrect count originates from IoTDB’s filtering semantics on aligned devices:

  • Boolean filters are applied per timeseries, not per device row.
  • Rows where a device’s measurement is NULL are dropped before aggregation.
  • When querying root.datacenter.*, IoTDB merges results across nodes, and:
    • Missing timestamps
    • Sparse boolean series
    • Misaligned rows
      cause valid false values to be excluded.
  • count(status) counts non-null values, not matching values.

Key takeaway:
count(status) does not count rows where status = false; it counts rows where status is present after filtering.

Why This Happens in Real Systems

Real telemetry systems often exhibit:

  • Misaligned timestamps between sensors
  • Sparse boolean fields (e.g., status only updates on change)
  • Aligned devices with partial-row writes
  • Implicit null filtering during query planning

These conditions cause unexpected row elimination when boolean filters are applied.

Real-World Impact

Incorrect boolean filtering can lead to:

  • Under-counting or over-counting events
  • Misleading operational dashboards
  • Incorrect anomaly detection
  • Faulty SLA or uptime calculations
  • Bad automation decisions (e.g., triggering alerts too late)

Example or Code (if necessary and relevant)

A correct query must convert boolean to numeric before aggregation:

select sum(case when status = false then 1 else 0 end) as false_count
from root.datacenter.*

Or explicitly reference each node:

select sum(case when root.datacenter.node1.status = false then 1 else 0 end),
       sum(case when root.datacenter.node2.status = false then 1 else 0 end)

These avoid IoTDB’s null-row elimination behavior.

How Senior Engineers Fix It

Experienced engineers avoid boolean-count pitfalls by:

  • Never using count() to count boolean values
  • Using CASE → numeric conversion before aggregation
  • Querying each timeseries explicitly instead of wildcarding
  • Checking alignment and timestamp consistency
  • Inspecting raw rows with select time, status before aggregating

They treat boolean fields as categorical, not numeric.

Why Juniors Miss It

Less experienced engineers often assume:

  • count(status) counts matching values
    (it does not)
  • Boolean filtering behaves like SQL on relational tables
    (IoTDB is columnar and time-series oriented)
  • Wildcard queries behave identically across devices
    (alignment changes semantics)
  • Missing timestamps are harmless
    (they cause row drops)

The mismatch between relational SQL intuition and time-series database semantics is the core reason juniors misinterpret the results.

Leave a Comment