Snowflake混合数据类型拼接字段join查询无返回结果问题

问题原因 #

核心问题出在三个字段拼接时的隐式转换、空值处理和逻辑歧义上，常见触发原因如下：

• 数值类型的RECOGNITION_MONTH、RECOGNITION_YEAR隐式转字符串时，部分数据库会为个位数数值预留符号位生成带前导空格的结果，比如数值3转字符串得到' 3'而非'3'，两边拼接结果实际不匹配
• 无分隔符拼接会出现逻辑歧义：比如PARTICIPANT='USR1'+月份12+年份2024的拼接结果，和PARTICIPANT='USR11'+月份2+年份2024的结果完全一致，会出现预期匹配的记录实际无法命中的情况
• 任意参与拼接的字段为NULL时，整个拼接结果都会返回NULL，而NULL和任何值的相等判断都为false，直接导致关联失败

修复方案 #

统一显式处理字段转换、增加分隔符、处理空值即可解决，修改后的SQL如下：

SELECT 
    CA.PARTICIPANT || CA.RECOGNITION_MONTH || CA.RECOGNITION_YEAR,
    CA.PLAN_NAME,
    PL.PLAN_NAME
FROM
    CSBI_DB.CS_SALES_OPS.CS_COMP_ANLTCS CA
LEFT JOIN
    (
        SELECT DISTINCT 
            CONCAT(
                IFNULL(TO_VARCHAR(PARTICIPANT), ''),
                '|', -- 用不会出现在业务字段中的特殊字符做分隔符
                LPAD(TO_VARCHAR(RECOGNITION_MONTH), 2, '0'), -- 月份统一补为2位，避免位数差异
                '|',
                TO_VARCHAR(RECOGNITION_YEAR)
            ) AS MATCH_STRING,
            PLAN_NAME 
        FROM 
            CSBI_DB.CS_SALES_OPS.CS_COMP_ANLTCS 
        WHERE 
            PLAN_NAME IS NOT NULL
    ) PL 
ON
    PL.MATCH_STRING = CONCAT(
        IFNULL(TO_VARCHAR(CA.PARTICIPANT), ''),
        '|',
        LPAD(TO_VARCHAR(CA.RECOGNITION_MONTH), 2, '0'),
        '|',
        TO_VARCHAR(CA.RECOGNITION_YEAR)
    )
WHERE 
    CA.PLAN_NAME IS NULL

你也可以先执行下面的语句验证拼接结果的差异，确认问题根源：

SELECT DISTINCT
    PARTICIPANT || RECOGNITION_MONTH || RECOGNITION_YEAR AS 原始拼接结果,
    CONCAT(IFNULL(TO_VARCHAR(PARTICIPANT), ''),'|',LPAD(TO_VARCHAR(RECOGNITION_MONTH),2,'0'),'|',TO_VARCHAR(RECOGNITION_YEAR)) AS 格式化后拼接结果
FROM CSBI_DB.CS_SALES_OPS.CS_COMP_ANLTCS

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