Snowflake SQL异常：符合条件客户未出现在SELECT结果但WHERE指定可见

问题描述 #

执行SQL查询某课程ID下状态为active的客户列表时，结果总数不正确——部分符合条件的客户未出现在结果中，但直接指定这些客户的CUSTOMER_ID时却能正常查询到。

问题复现语句 #

未返回指定客户的查询语句 #

SELECT * FROM COURSES_ACTIVE
WHERE COURSE_ID = '123aaaBBB'
AND COURSE_STATUS = 'Active'; 

返回指定客户的查询语句 #

SELECT * FROM COURSES_ACTIVE
WHERE COURSE_ID = '123aaaBBB'
AND COURSE_STATUS = 'Active'
AND CUSTOMER_ID = '555cccDDD'; 

已尝试的操作 #

• 怀疑ID大小写敏感问题，对CUSTOMER_ID使用COLLATE("CUSTOMER_ID ", 'binary')，确认不指定CUSTOMER_ID时能显示所有客户ID
• 使用CUSTOMER_ID::VARCHAR能得到正确ID，但担心大小写不敏感导致遗漏数据
• 使用CUSTOMER_ID::BINARY时报错："The following string is not a legal hex-encoded value: '888ooo000'"

完整查询代码 #

WITH COURSES_ALL AS (SELECT CUSTOMER_ID,
                            COURSE_ID,
                            MAX(COURSE_EXPIRATION_DATE) CURRENT_EXPIRATION_DATE
                     FROM DATABASE
                     GROUP BY CUSTOMER_ID,
                              COURSE_ID

  -- 课程存在新增条目，用MAX确保只处理最新记录
  -- 确认此处查询能显示所有CUSTOMER_ID

COURSES_ACTIVE AS (SELECT CUSTOMER_ID,
                          COURSE_ID,
                          CURRENT_EXPIRATION_DATE,
                          CASE
                              WHEN CURRENT_EXPIRATION_DATE < '2024-01-31' THEN 'Inactive'
                              WHEN CURRENT_EXPIRATION_DATE >= '2024-01-31' THEN 'Active'
                          END  COURSE_STATUS,
                          FROM COURSES_ALL)

 SELECT * FROM COURSES_ACTIVE
 WHERE COURSE_ID = '123aaaBBB'
 AND COURSE_STATUS = 'Active';
 --   AND CUSTOMER_ID = '555cccDDD'; // 添加此语句后，该客户会出现在结果中

排查与解决思路 #

1. 修正日期比较的隐式转换问题 #

从代码看，COURSE_STATUS通过CURRENT_EXPIRATION_DATE与字符串'2024-01-31'比较生成。若COURSE_EXPIRATION_DATE是TIMESTAMP类型，隐式转换会导致异常比较结果，建议显式转换为DATE类型：

CASE
    WHEN CURRENT_EXPIRATION_DATE < DATE('2024-01-31') THEN 'Inactive'
    WHEN CURRENT_EXPIRATION_DATE >= DATE('2024-01-31') THEN 'Active'
END COURSE_STATUS

2. 验证分组逻辑的正确性 #

虽然COURSES_ALL能显示所有客户ID，但需确认目标客户对应的最大过期日期是否符合条件：

SELECT CUSTOMER_ID, COURSE_ID, COURSE_EXPIRATION_DATE
FROM DATABASE
WHERE CUSTOMER_ID = '555cccDDD' AND COURSE_ID = '123aaaBBB'
ORDER BY COURSE_EXPIRATION_DATE DESC;

检查返回的最大日期是否确实满足>= '2024-01-31'，避免分组时取到错误的过期日期。

3. 正确处理大小写敏感问题 #

Snowflake默认字符串比较大小写不敏感，使用COLLATE binary可转为敏感模式，但CUSTOMER_ID::BINARY会报错是因为该类型要求十六进制输入。正确的大小写敏感比较方式：

WHERE COURSE_ID COLLATE BINARY = '123aaaBBB'
AND COURSE_STATUS = 'Active'

同时需确认COURSES_ALL中的COURSE_ID与查询条件的大小写完全匹配，避免因大小写差异生成重复分组。

4. 处理字符类型的尾部空格问题 #

若CUSTOMER_ID或COURSE_ID是CHAR类型，可能存在尾部空格导致匹配异常，可在分组和查询时去除空格：

-- 在COURSES_ALL中处理
SELECT TRIM(CUSTOMER_ID) AS CUSTOMER_ID,
       TRIM(COURSE_ID) AS COURSE_ID,
       MAX(COURSE_EXPIRATION_DATE) CURRENT_EXPIRATION_DATE
FROM DATABASE
GROUP BY TRIM(CUSTOMER_ID), TRIM(COURSE_ID)

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