Apache IoTDB 2.0.6中ALL子查询含NULL值结果不符的解决问询

最小可复现数据 #

CREATE TABLE main_data (
  `time` TIMESTAMP `TIME`,
  device_id STRING TAG,
  value INT32 FIELD
);

CREATE TABLE detail_data (
  device_id STRING TAG,
  ref_value INT32 FIELD
);

INSERT INTO main_data VALUES
(2024-01-01T00:00:00, 'D1', 30),
(2024-01-01T01:00:00, 'D1', 40),
(2024-01-01T02:00:00, 'D1', 50);

INSERT INTO detail_data VALUES
(2024-01-01T00:00:00, 'D1', 30),
(2024-01-01T01:00:00, 'D1', 40),
(2024-01-02T00:00:00, 'D1', `NULL`);

查询语句 #

SELECT * FROM main_data
WHERE device_id = 'D1'
AND value > ALL (SELECT ref_value FROM detail_data WHERE device_id = 'D1');

当前返回结果 #

+----+---------+-----+
|`time`|device_id|value|
+----+---------+-----+
+----+---------+-----+
Empty set.

问题说明 #

执行50 > ALL(30, 40, NULL)比较时，查询返回空集，但预期是忽略NULL值，返回value为50的那条数据：

+-----------------------------+----------+-------+
| `time`| device_id| value|
+-----------------------------+----------+-------+
|2024-01-01T02:00:00.000+08:00| D1| 50|
+-----------------------------+----------+-------+

解决方案 #

问题根源在于SQL的比较逻辑：任何与NULL的比较结果都是UNKNOWN，而ALL要求所有比较结果都为TRUE才会满足条件。只要子查询返回的集合里包含NULL，整个value > ALL(...)的条件就会判定为不成立。

要忽略NULL值，只需在子查询中过滤掉ref_value为NULL的记录：

SELECT * FROM main_data
WHERE device_id = 'D1'
AND value > ALL (SELECT ref_value FROM detail_data WHERE device_id = 'D1' AND ref_value IS NOT NULL);

这样子查询返回的集合是(30,40)，50 > ALL(30,40)结果为TRUE，就能得到预期的查询结果。

内容的提问来源于stack exchange，提问作者Yu Long