问题背景 #

在Apache IoTDB 2.0.5中有两条带空值的时间序列，插入语句如下：

INSERT INTO
  `root.db.YJEUG88.tms.ch14.m1.DDC001` (`time`, value)
VALUES
  (2025-09-16T14:15:28.000, 12.0),
  (2025-09-16T14:15:58.000, 10.0),
  (2025-09-16T14:16:28.000, 11.0),
  (2025-09-16T14:16:58.000, `NULL`),
  (2025-09-16T14:17:28.000, 12.0),
  (2025-09-16T14:17:58.000, 18.0);

INSERT INTO
  `root.db.YJEUG88.tms.ch14.m2.DAV001` (`time`, value)
VALUES
  (2025-09-16T14:15:28.000, 14.0),
  (2025-09-16T14:15:58.000, 20.0),
  (2025-09-16T14:16:28.000, `NULL`),
  (2025-09-16T14:16:58.000, 21.0),
  (2025-09-16T14:17:28.000, 22.0),
  (2025-09-16T14:17:58.000, 12.0);

想要实现按设备对齐数据并填充空值，执行了以下查询：

SELECT
  value
FROM
  `root.db.YJEUG88.tms.ch14`.*.DDC001,
  `root.db.YJEUG88.tms.ch14`.*.DAV001
WHERE
  `time` >= 2025-09-16T14:15:40.000
  AND `time` < 2025-09-16T14:17:40.000
align by device
fill(previous)

但触发语法错误：

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 700: Error occurred while parsing SQL to physical plan: line 1:179 mismatched input 'fill' expecting {, ';'}

尝试调整后发现：

• 移除FILL或调换ALIGN与FILL顺序，查询可执行但空值未填充；
• 移除ALIGN，查询可执行但结果未按设备对齐。

期望得到的结果（按设备对齐且空值被前值填充）：

+-----------------------------+----------------------------------+-----+
| `Time`| Device|value|
+-----------------------------+----------------------------------+-----+
|2025-09-16T14:15:58.000+08:00|`root.db.YJEUG88.tms.ch14.m1.DDC001`| 10.0|
|2025-09-16T14:16:28.000+08:00|`root.db.YJEUG88.tms.ch14.m1.DDC001`| 11.0|
|2025-09-16T14:16:58.000+08:00|`root.db.YJEUG88.tms.ch14.m1.DDC001`| 11.0| -- Filled `null`
|2025-09-16T14:17:28.000+08:00|`root.db.YJEUG88.tms.ch14.m1.DDC001`| 12.0|
|2025-09-16T14:15:58.000+08:00|`root.db.YJEUG88.tms.ch14.m2.DAV001`| 20.0|
|2025-09-16T14:16:28.000+08:00|`root.db.YJEUG88.tms.ch14.m2.DAV001`| 20.0| -- Filled `null`
|2025-09-16T14:16:58.000+08:00|`root.db.YJEUG88.tms.ch14.m2.DAV001`| 21.0|
|2025-09-16T14:17:28.000+08:00|`root.db.YJEUG88.tms.ch14.m2.DAV001`| 22.0|
+-----------------------------+----------------------------------+-----+

解决方案 #

在IoTDB 2.0.x版本中，FILL子句需要嵌套在ALIGN BY DEVICE的语法结构中，正确的写法是将FILL放在ALIGN BY DEVICE之后的括号内。调整后的查询语句如下：

SELECT
  value
FROM
  `root.db.YJEUG88.tms.ch14`.*.DDC001,
  `root.db.YJEUG88.tms.ch14`.*.DAV001
WHERE
  `time` >= 2025-09-16T14:15:40.000
  AND `time` < 2025-09-16T14:17:40.000
align by device (fill(previous))

说明 #

• 原语句错误是因为IoTDB 2.0.x不支持ALIGN BY DEVICE和FILL作为独立子句并列使用，必须将FILL作为ALIGN BY DEVICE的参数放在括号内；
• 该语句会先按设备对齐数据，再对每个设备的空值使用前一个非空值进行填充，完全匹配期望的输出结果。

内容的提问来源于stack exchange，提问作者Tampa Tommy