clickhouse数据库查询异常（疑惑与解答）

Question

最近在写项目需求时，遇到一个clickhouse数据库的问题，单独查询外层SQL或者单独查询子查询均能查出数据，但是以下sql却查不出数据，下面以缩略形式展示sql语句：

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts is not null

  AND test_text GLOBAL IN (
        SELECT DISTINCT test_text
            FROM ck 库表2
            WHERE 
            start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
                           
            )

order by test_text;

经过多番尝试后，发现，单独查询

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts is not null

大概有20w条数据，并不算多。

单独子查询：

  SELECT DISTINCT test_text
            FROM ck 库表2
            WHERE 
            start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')

大概2000条数据，并且子查询 查询到 test_text 字段值在外层表格里面有对应的数值。 在经过多次尝试后，发现将sql语句改成：

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts != 0

  AND test_text GLOBAL IN (
        SELECT DISTINCT test_text
            FROM ck 库表2
            WHERE 
            start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
                           
            )

order by test_text;

就可以查询出数据，即将 is not null 改成 !=0 即可查询出数据。 或者在外层查询的where条件中也加入对时间的卡控，也能查出数据。其中两张表的test_text 字段类型一致，line_cutter_theoretical_cuts 字段是 Float32 类型。有木有大能解释一下原因。

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

你遇到的这个问题非常典型，涉及到 ClickHouse 中的 数据类型、NULL 值处理以及查询优化器的行为。下面我们逐步分析问题原因，并提供解决方案。

---

一、问题现象

• 外层 SQL：line_cutter_theoretical_cuts is not null 无法查出数据；
• 子查询可以查到数据；
• 改为 line_cutter_theoretical_cuts != 0 可以查出数据；
• 同时添加时间条件也能查出数据；
• 两张表的 test_text 字段类型一致；
• line_cutter_theoretical_cuts 是 Float32 类型。

---

二、可能的原因分析

1. NULL 值与 Float32 的关系

在 ClickHouse 中，Float32 类型字段 不能存储 NULL 值（除非使用 Nullable(Float32)）。也就是说，如果你的 line_cutter_theoretical_cuts 字段是 非空类型（即不是 Nullable），那么：

line_cutter_theoretical_cuts is not null 实际上是无效的，因为该字段不可能为 NULL。

但你在执行时却能运行，说明可能是字段被定义为 Nullable(Float32)，或者有其他隐藏逻辑。

2. 查询优化器行为

ClickHouse 的查询优化器可能会对某些条件进行重写或优化，例如：

• 如果 line_cutter_theoretical_cuts 字段是 Float32，且没有 NULL 值，那么 is not null 条件会被优化掉，导致结果集为空。
• 当你将条件改为 != 0，它会实际检查数值是否为 0，从而返回正确数据。

3. 子查询和外层查询的数据关联方式

你使用了 GLOBAL IN，这是 全局 IN 查询，意味着它会将子查询的结果全部加载到内存中，然后逐行比对。如果子查询结果很大，可能导致性能问题，甚至影响查询结果。

---

三、详细解答

为什么 line_cutter_theoretical_cuts is not null 无法查出数据？

✅ 原因一：字段类型不支持 NULL

• 如果 line_cutter_theoretical_cuts 是 Float32 类型（非 Nullable），那么它不可能为 NULL，所以 is not null 条件永远为真，但实际上这个条件不会起作用。
• 但是，如果你的字段是 Nullable(Float32)，那 is not null 是有效的，但你发现它无法返回数据，这可能是因为：
  • 该字段中存在很多 NULL 值，而你的子查询没有匹配到这些值；
  • 或者查询优化器忽略了这个条件。

✅ 原因二：查询优化器优化掉了 is not null

• 在某些情况下，ClickHouse 会优化掉 is not null 条件，尤其是当字段类型是 Float32 时，因为它不可能为 NULL。
• 所以即使你写了 is not null，实际上等价于没有这个条件。

✅ 原因三：子查询未包含所有可能的 test_text 值

• 虽然你认为子查询返回的 test_text 在外层表中存在，但可能由于某些过滤条件（如时间范围）导致子查询只返回部分数据；
• 如果外层查询的 test_text 在子查询中没有出现，就会返回空结果。

---

四、解决方案

1. 确认字段类型

-- 查看字段类型
DESCRIBE ck 库表1.line_cutter_theoretical_cuts;

• 如果返回的是 Float32，则 is not null 无效；
• 如果是 Nullable(Float32)，则 is not null 有效。

2. 使用 != 0 替代 is not null

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts != 0
  AND test_text GLOBAL IN (
        SELECT DISTINCT test_text
            FROM ck 库表2
            WHERE 
            start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
            )
ORDER BY test_text;

注意： 如果 line_cutter_theoretical_cuts 是 Float32，并且你希望排除 NULL 值，应确保字段类型为 Nullable(Float32)。

3. 使用 EXISTS 替代 GLOBAL IN

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts != 0
  AND EXISTS (
        SELECT 1
        FROM ck 库表2
        WHERE 
        ck 库表1.test_text = ck 库表2.test_text
          AND start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
        )
ORDER BY test_text;

优点： 更高效，避免加载大量数据到内存。

4. 添加时间范围到外层查询

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts != 0
  AND start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
  AND test_text GLOBAL IN (
        SELECT DISTINCT test_text
            FROM ck 库表2
            WHERE 
            start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
            )
ORDER BY test_text;

注意： 这里假设 ck 库表1 也有 start_time 字段。

---

五、总结

| 问题 | 解决方案 | |------|----------| | line_cutter_theoretical_cuts is not null 不生效 | 检查字段类型，改用 != 0 | | 子查询与外层查询不匹配 | 使用 EXISTS 替代 GLOBAL IN | | 查询优化器忽略条件 | 添加时间范围或使用更明确的条件 |

---

六、推荐修改后的 SQL 示例

SELECT test_text AS testText
FROM ck 库表1
WHERE line_cutter_theoretical_cuts != 0
  AND EXISTS (
        SELECT 1
        FROM ck 库表2
        WHERE 
        ck 库表1.test_text = ck 库表2.test_text
          AND start_time between concat('2025-07-17', ' 00:00:00') and concat('2025-07-20', ' 23:59:59')
        )
ORDER BY test_text;

---

如有更多字段结构或具体数据示例，可以进一步帮助定位问题。