CnosDB查询返回错误结果：UNION查询无预期返回数据

问题现象 #

在CnosDB中执行包含UNION ALL的复合查询，结合含NULL的条件判断时，预期应返回至少一条记录（已知表中存在t1为NULL的记录），但实际无结果返回。同时，直接使用IN (NULL)或IN (NULL, ...)条件查询也无结果，仅IS NULL能正常返回目标记录。

复现步骤与SQL #

-- 创建表
public ❯ CREATE TABLE m0(f0 DOUBLE , TAGS(t0, t1, t2));
Query took 0.009 seconds.

-- 插入测试数据（t1字段未赋值，为NULL）
public ❯ INSERT m0(TIME, t0, f0) VALUES(CAST (2332186730629652317 AS TIMESTAMP), 'm>', -1321451580);
+------+
| rows |
+------+
| 1    |
+------+
Query took 0.042 seconds.

-- 复合UNION查询，预期返回记录但无结果
public ❯ SELECT *
FROM m0
WHERE NOT (NOT (m0.t1 NOT IN (NULL, '''!ୋx85dc4')))
UNION ALL
SELECT ALL *
FROM m0
WHERE NOT (NOT (NOT (m0.t1 NOT IN (NULL, '''!ୋx85dc4'))))
UNION ALL
SELECT *
FROM m0
WHERE (NOT (NOT (m0.t1 NOT IN (NULL, '''!ୋx85dc4')))) IS NULL;
Query took 0.044 seconds.

-- IN包含NULL的查询无结果
public ❯ SELECT *
FROM m0
WHERE m0.t1
IN (NULL, '''!ୋx85dc4');
Query took 0.018 seconds.

public ❯ SELECT *
FROM m0
WHERE m0.t1
IN (NULL);
Query took 0.018 seconds.

-- IS NULL查询正常返回记录
public ❯ SELECT *
FROM m0
WHERE m0.t1
IS NULL;
+-------------------------------+----+----+----+---------------+
| time                          | t0 | t1 | t2 | f0            |
+-------------------------------+----+----+----+---------------+
| 2043-11-26T21:38:50.629652317 | m> |    |    | -1321451580.0 |
+-------------------------------+----+----+----+---------------+
Query took 0.018 seconds.

问题原因 #

该异常是由于CnosDB在处理包含NULL的二元操作符（如IN/NOT IN包含NULL的场景）时，将条件下推执行，导致三值逻辑（TRUE/FALSE/UNKNOWN）计算异常，无法正确匹配NULL值的记录。

解决方案 #

避免将包含NULL的二元操作符进行下推处理，确保SQL的三值逻辑判断能正确执行，从而正确匹配NULL相关的条件。

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