如何实现Snowflake与IICS的MD5摘要一致并落地生产？

核心疑问 #

为何相同数值输入下，Snowflake与IICS的MD5函数生成的摘要不同？仅当MD5输入为手动硬编码的数值字符串时两者匹配，从表中读取数值哈希则无法匹配。

现状复现 #

以定义为NUMERIC(38,6)的5.000000为例：

• IICS生成的MD5：E4DA3B7FBBCE2345D7772B0674A318D5
• Snowflake硬编码输入MD5(5.000000)生成：E4DA3B7FBBCE2345D7772B0674A318D5（预期匹配结果）
• Snowflake从表中读取BAR列（存储值为5，类型NUMERIC(38,6)）生成的MD5(BAR)：463DAAEAA59E931EC54B0D6375A05A0F（实际不匹配）

问题根源 #

本质是数值转字符串的格式规则不一致：

• IICS处理NUMERIC(38,6)类型时，会自动保留6位小数，将数值5转换为字符串"5.000000"后再计算MD5。
• Snowflake中，硬编码的5.000000会被解析为字符串"5.000000"作为MD5输入；但从表中读取NUMERIC(38,6)类型的5时，默认转字符串（如TO_VARCHAR(BAR)）会输出"5"（无末尾补零），输入字符串不同导致MD5结果不一致。

解决方案 #

让Snowflake将NUMERIC(38,6)类型数值转换为带6位小数的固定格式字符串，与IICS的转换规则对齐：
使用TO_CHAR函数指定格式模板，确保输出字符串包含6位小数，同时去掉前导空格（避免额外字符影响哈希）：

TO_CHAR(col, 'FM99999999999999999999999999999999.000000')

说明：

• FM：去除格式转换后的前导空格
• 999...：对应整数部分的最大位数（32位，匹配NUMERIC(38,6)的32位整数部分）
• .000000：强制保留6位小数，不足补零

验证代码 #

修改原排查SQL，加入正确格式转换的验证：

CREATE TABLE TMP (
    FOO NUMBER(38,6),
    BAR NUMBER(38,6)
);
INSERT INTO TMP VALUES(0, 5);

SELECT *
    ,MD5(0.000000) AS MD5_ZERO
    ,MD5(5.000000) AS MD5_FIVE
    -- 正确格式转换后的MD5，与硬编码结果匹配
    ,MD5(TO_CHAR(FOO, 'FM99999999999999999999999999999999.000000')) AS MD5_FOO_CORRECT
    ,MD5(TO_CHAR(BAR, 'FM99999999999999999999999999999999.000000')) AS MD5_BAR_CORRECT
    -- 原错误转换方式
    ,MD5(TO_VARCHAR(BAR::VARCHAR(38))) AS MD5_BAR_ORIGINAL
FROM TMP;

执行后，MD5_FOO_CORRECT会等于MD5_ZERO，MD5_BAR_CORRECT会等于MD5_FIVE，与IICS生成的结果一致。

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