Snowflake表值函数传入动态DateTime参数报错排查

问题场景 #

现有表值函数cfn_GetShiftIDFromDateTime，定义参数dateTime为TIMESTAMP_NTZ(9)类型。执行查询时，传入临时表temp的DateTime字段（转换为DATETIME类型）调用该函数，触发报错：

Processing aborted due to error 300010:391167117; incident 3245754

原查询代码：

with temp  AS (
    SELECT CAST(SUM(sreg.ScrapQuantity) AS INT) AS Quantity,
        sreas.Name AS ScrapReason,
        to_date((DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')))) AS DATETIME,
        sreg.EquipmentID AS EquipmentID
    FROM ScrapRegistration sreg
    INNER JOIN ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')),
        sreg.EquipmentID,
        sreas.Name
    )
    
select temp.EquipmentID
from RAW_CPMS_AAR.equipment e, temp
Where e.ID = (select * from table(cfn_GetShiftIDFromDateTime_test(temp.DateTime::DATETIME, 0))) --this works with datetime

但将函数参数替换为硬编码的固定DateTime值（如'2021-12-02 10:03:0.00'::datetime）时，查询可正常返回结果。

函数定义：

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)

RETURNS table (shiftID int)
AS
$$
WITH T0 (ShiftCalendarID, CurDay, PrvDay)
AS (
    SELECT TOP 1
        ID AS ShiftCalendarID,
        DATEDIFF( day, BeginDate, dateTime ) % PeriodInDays + 1 AS CurDay,
        ( CurDay + PeriodInDays - 2 ) % PeriodInDays + 1 AS PrvDay
    FROM RAW_CPMS_AAR.ShiftCalendar
    WHERE ID = shiftCalendarID
        OR (    shiftCalendarID IS NULL
            AND Name = 'Factory'
            AND BeginDate <= dateTime )
    ORDER BY BeginDate DESC
),
T1 (TimeValue)
AS (
    SELECT TIME_FROM_PARTS(
        EXTRACT(HOUR   FROM dateTime),
        EXTRACT(MINUTE FROM dateTime),
        EXTRACT(SECOND FROM dateTime))
    )

SELECT ID as shiftID
    FROM RAW_CPMS_AAR.Shift, T0, T1
    WHERE Shift.ShiftCalendarID = T0.ShiftCalendarID
    AND (  ( FromDay = T0.CurDay AND FromTimeOfDay <= T1.TimeValue AND TillTimeOfDay >  T1.TimeValue )
            OR ( FromDay = T0.CurDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= T1.TimeValue )
            OR ( FromDay = T0.PrvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay >  T1.TimeValue )
        )
$$
;

报错原因分析 #

1. 临时表字段类型不匹配：原查询中用to_date()生成的DateTime字段是DATE类型，仅包含日期部分，无时分秒信息。强制转换为DATETIME后，时分秒默认补0，但函数内部需要提取dateTime的时分秒生成TimeValue，批量处理字段时，这种类型转换的隐式逻辑会触发Snowflake内部计算异常（硬编码常量会被提前优化，不会触发该问题）。
2. 函数参数类型兼容性问题：函数要求参数为TIMESTAMP_NTZ(9)，但传入的是DATE转DATETIME的值，虽然DATETIME是TIMESTAMP_NTZ(9)的别名，但批量字段转换时的类型推导逻辑与常量不同，导致函数执行时出现底层类型不兼容错误。
3. 潜在NULL值问题：临时表的DateTime字段可能存在NULL值，函数未处理NULL参数的情况，逐行调用时遇到NULL会触发计算中断。

解决方法 #

1. 修正临时表字段类型，匹配函数参数 #

将临时表中DateTime字段的生成逻辑从to_date()改为TO_TIMESTAMP_NTZ()，直接生成函数要求的TIMESTAMP_NTZ(9)类型，避免类型转换：

with temp  AS (
    SELECT CAST(SUM(sreg.ScrapQuantity) AS INT) AS Quantity,
        sreas.Name AS ScrapReason,
        -- 改为TO_TIMESTAMP_NTZ，保留时间部分并匹配函数参数类型
        TO_TIMESTAMP_NTZ(DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0'))) AS DATETIME,
        sreg.EquipmentID AS EquipmentID
    FROM ScrapRegistration sreg
    INNER JOIN ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    -- 过滤NULL值，避免函数处理异常
    WHERE sreg.ScrapTime IS NOT NULL
    GROUP BY DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')),
        sreg.EquipmentID,
        sreas.Name
    )
    
select temp.EquipmentID
from RAW_CPMS_AAR.equipment e, temp
-- 直接传入TIMESTAMP_NTZ类型字段，无需强制转换
Where e.ID = (select * from table(cfn_GetShiftIDFromDateTime(temp.DateTime, 0)))

2. 增强函数的NULL处理能力 #

在函数内部增加NULL参数判断，避免因传入NULL导致计算中断：

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)

RETURNS table (shiftID int)
AS
$$
-- 先判断dateTime是否为NULL，直接返回空结果
IF dateTime IS NULL THEN
    RETURN SELECT NULL AS shiftID WHERE 1=0;
END IF;

WITH T0 (ShiftCalendarID, CurDay, PrvDay)
AS (
    SELECT TOP 1
        ID AS ShiftCalendarID,
        DATEDIFF( day, BeginDate, dateTime ) % PeriodInDays + 1 AS CurDay,
        ( CurDay + PeriodInDays - 2 ) % PeriodInDays + 1 AS PrvDay
    FROM RAW_CPMS_AAR.ShiftCalendar
    WHERE ID = shiftCalendarID
        OR (    shiftCalendarID IS NULL
            AND Name = 'Factory'
            AND BeginDate <= dateTime )
    ORDER BY BeginDate DESC
),
T1 (TimeValue)
AS (
    SELECT TIME_FROM_PARTS(
        EXTRACT(HOUR   FROM dateTime),
        EXTRACT(MINUTE FROM dateTime),
        EXTRACT(SECOND FROM dateTime))
    )

SELECT ID as shiftID
    FROM RAW_CPMS_AAR.Shift, T0, T1
    WHERE Shift.ShiftCalendarID = T0.ShiftCalendarID
    AND (  ( FromDay = T0.CurDay AND FromTimeOfDay <= T1.TimeValue AND TillTimeOfDay >  T1.TimeValue )
            OR ( FromDay = T0.CurDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= T1.TimeValue )
            OR ( FromDay = T0.PrvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay >  T1.TimeValue )
        )
$$
;

3. 排查异常数据 #

检查ScrapRegistration表中ScrapTime字段是否存在异常值（如超范围的时间、格式错误的值），可以通过以下语句排查：

SELECT ScrapTime
FROM ScrapRegistration
WHERE ScrapTime IS NULL OR ScrapTime < '1970-01-01'::TIMESTAMP_NTZ;

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