Running time-series queries in Apache IoTDB is generally fast and seamless. However, some users encounter a perplexing bug when executing temporal queries: a query with a standard time filter returns rows as expected, but adding ORDER BY time DESC suddenly yields empty results.

The Problem Illustrated

Consider the following query executed in Apache IoTDB 1.3.7:

SELECT *
FROM root.iot_prod_2.p.cjzxjd.t0020.ori
WHERE time >= 1791625620000
  AND time < 1791648000000;

This query runs normally and returns rows in chronological order (the default ascending order). However, when adding reverse ordering to fetch the latest points first:

SELECT *
FROM root.iot_prod_2.p.cjzxjd.t0020.ori
WHERE time >= 1791625620000
  AND time < 1791648000000
ORDER BY time DESC;

The query produces no data (an empty result set). Why does this happen?

Root Causes

1. TsFile Chunk Boundary Pruning Bug in IoTDB 1.3.x

Apache IoTDB stores data in structured TsFile units divided into chunks, pages, and metadata indexes. When querying in ascending order, the scanner reads chunks forward from the lower boundary of the time window.

In Apache IoTDB version 1.3.x (and specific maintenance versions), an edge-case bug in the MPP (Massively Parallel Processing) Query Engine causes the reverse-scanning iterator to miscalculate chunk end boundaries when handling combined range filters (time >= X AND time < Y). Under certain chunk distributions, the reverse scanner incorrectly assumes the lower bound has already been crossed or skips candidate chunks in memory/unsequence files, causing it to terminate prematurely and return an empty set.

2. Unmerged Unsequence TsFiles

If data is written out of order or future-dated (notice that 1791625620000 corresponds to a timestamp in late 2026), IoTDB routes these writes into unsequence TsFiles. When data resides in unmerged unsequence files or active MemTables, the metadata index for reverse chunk traversal can fail to properly overlap with sequence files under strict boundary filters.

How to Fix and Work Around the Issue

Solution 1: Trigger FLUSH and MERGE

If data is trapped in memory or unsequence files with inconsistent chunk metadata, forcing a flush and compaction can resolve the chunk lookup issue immediately:

-- Flush all data from MemTable to TsFile
FLUSH;

-- Force compaction to merge unsequence files into sequence files
MERGE;

After running these commands, re-run your descending query to verify if the rows appear.

Solution 2: Upgrade to the Latest IoTDB Patch

Apache IoTDB regularly releases patch versions for the 1.3 branch to fix query planner and chunk iterator issues. Check the Apache IoTDB Releases for version 1.3.8 or the 2.x branch, where improvements have been made to descending chunk reader traversal algorithms.

Solution 3: Workaround Using Client-Side Sorting

If you cannot immediately restart or upgrade your production IoTDB cluster, you can bypass the database reverse scanner by sorting the result set on the application layer:

-- Fetch the slice in default ascending order
SELECT *
FROM root.iot_prod_2.p.cjzxjd.t0020.ori
WHERE time >= 1791625620000
  AND time < 1791648000000;

Then reverse the array or list inside your backend service (Java, Python, Go, etc.) before delivering the response to your users.

Solution 4: Adjust the Filter Format

In some versions, reformulating the query slightly alters how the execution plan constructs the chunk filter:

SELECT *
FROM root.iot_prod_2.p.cjzxjd.t0020.ori
WHERE time BETWEEN 1791625620000 AND 1791647999999
ORDER BY time DESC;

Summary

An empty result set when querying with ORDER BY time DESC in Apache IoTDB 1.3.7 is typically caused by a known chunk-index filtering bug during reverse scans on uncompacted or unsequenced TsFiles. The fastest remediation is running FLUSH and MERGE, followed by upgrading your IoTDB instance to the latest patch release.