Apache IoTDB 2.0.6中IN子查询NULL值处理不符合预期的问题咨询

原始数据 #

CREATE TABLE products (product_id STRING Tag, category STRING Tag, price INT32 Field);
INSERT INTO products(`time`, product_id, category, price) VALUES (2024-09-24T14:00:00.000+08:00, 'p01', 'A', 100);
INSERT INTO products(`time`, product_id, category, price) VALUES (2024-09-24T14:01:00.000+08:00, 'p01', 'A', 200);
INSERT INTO products(`time`, product_id, category, price) VALUES (2024-09-24T14:02:00.000+08:00, 'p01', 'A', NULL);
INSERT INTO products(`time`, product_id, category, price) VALUES (2024-09-24T14:03:00.000+08:00, 'p02', 'B', 150);

原查询SQL #

SELECT product_id, price, 
       price IN (
        SELECT price 
        FROM products 
        WHERE category = 'A') AS is_in_category 
FROM products;

实际返回结果 #

遇到的问题 #

• 当p01的price为NULL时，返回null，但子查询包含NULL值，预期返回true
• p02的price=150不在子查询结果集（100,200,NULL）中，却返回null而非false

预期结果 #

问题原因：并非IoTDB Bug，符合SQL标准NULL处理逻辑 #

SQL中NULL代表未知值，所有涉及NULL的比较操作结果都是NULL（未知）：

1. 当主查询的price为NULL时，NULL IN (...)的结果是NULL，因为无法判断未知值是否在集合中
2. 当主查询的price是150时，子查询集合包含NULL，150 IN (100,200,NULL)的结果是NULL——因为集合中存在未知值，无法完全确认150不在集合内

调整后的SQL方案 #

方案1：用CASE分支明确处理NULL场景 #

SELECT 
  product_id, 
  price, 
  CASE
    -- 主查询price为NULL时，检查A类是否存在NULL价格
    WHEN price IS NULL THEN EXISTS(SELECT 1 FROM products WHERE category = 'A' AND price IS NULL)
    -- 主查询price非NULL时，只匹配A类的非NULL价格
    ELSE price IN (SELECT price FROM products WHERE category = 'A' AND price IS NOT NULL)
  END AS is_in_category
FROM products;

方案2：用EXISTS+显式NULL相等判断 #

SELECT 
  product_id, 
  price, 
  EXISTS(
    SELECT 1 
    FROM products a 
    WHERE a.category = 'A' 
    -- 匹配非NULL相等，或两者都是NULL的情况
    AND (a.price = price OR (a.price IS NULL AND price IS NULL))
  ) AS is_in_category
FROM products;

两种方案都能得到预期的结果，核心是主动处理NULL的相等判断，绕过SQL默认的NULL比较逻辑。

内容的提问来源于stack exchange，提问作者Pizza代码救援队