移除WHERE AccountReturn <> -1后SQL查询报错：无效浮点运算

问题场景 #

当SQL代码保留WHERE AccountReturn <> -1条件时可正常运行，移除该条件后抛出以下错误：

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

已确认cte_clean中不存在<= -1的数据，但错误依然触发。

相关代码 #

WITH cte_exclude AS ( --List of "bad data quarters" for an insurance
SELECT DISTINCT 
    AR.InsuranceNumber
    ,AR.AccountNumber
    ,CONVERT(varchar(4), YEAR(AR.ValueDate)) + '-' + RIGHT('00' + CONVERT(varchar(2), DATEPART(QUARTER, AR.ValueDate)), 2) AS [QuarterNumber]
FROM
    tableAccountReturns AR
WHERE
    AccountReturn >=1
    or AccountReturn <=-1)

,cte_clean AS ( --Clear all data corresponding to quarters in the exclude cte
SELECT
    AR.*
    ,X.QuarterNumber
FROM
    tableAccountReturns AR
    OUTER APPLY (
        SELECT
            (CONVERT(varchar(4), YEAR(AR.ValueDate)) + '-' + RIGHT('00' + CONVERT(varchar(2), DATEPART(QUARTER, AR.ValueDate)), 2)) AS [QuarterNumber]
    ) X
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            cte_exclude EX
        WHERE
            EX.InsuranceNumber = AR.InsuranceNumber
            AND EX.QuarterNumber = X.QuarterNumber
    ))

SELECT 
InsuranceNumber
,AccountNumber
,QuarterNumber
,(
    exp(SUM(log(CASE
        WHEN (1+AccountReturn) < 0 THEN --Should not happen since we are excluding those data points
            (1+AccountReturn)*(-1)
        ELSE
            (1+AccountReturn)
        END)
        ))
    ) * (
    CASE (SUM(CASE WHEN (1+AccountReturn) < 0 THEN 1 ELSE 0 END) % 2)
        WHEN 1 THEN -1
        WHEN 0 THEN 1
    END
    ) - 1 AS [PortfolioReturnQuarter]
FROM
cte_clean C
-- WHERE AccountReturn <> -1 移除该条件后报错
GROUP BY
InsuranceNumber, AccountNumber, QuarterNumber

错误成因分析 #

1. 直接触发原因 #

SQL Server中，对0取对数（LOG(0)）是未定义的浮点运算，会直接抛出Msg 3623错误。当AccountReturn = -1时，1 + AccountReturn = 0，此时代码中的LOG(0)会触发该错误。

2. 根本原因 #

你的数据过滤逻辑存在疏漏，导致AccountReturn = -1的数据未被完全排除在cte_clean之外，或你对cte_clean的数据检查存在遗漏：

• cte_clean的NOT EXISTS关联条件缺失了EX.AccountNumber = AR.AccountNumber，原本逻辑应该是排除单个账号（AccountNumber）自身的坏季度数据，但当前逻辑会错误地排除同一保险公司（InsuranceNumber）同一季度下所有账号的数据。这种逻辑偏差可能导致部分AccountReturn = -1的数据未被过滤（比如当某账号的坏季度数据未被cte_exclude正确关联时）。
• 即使你认为cte_clean中没有<= -1的数据，也可能存在浮点精度问题：比如AccountReturn的值因存储精度误差，实际为-1.0000000000000002或-0.9999999999999999，导致1+AccountReturn计算后出现0或极小负数，触发LOG运算错误。

验证与修复建议 #

1. 验证数据：执行SELECT * FROM cte_clean WHERE AccountReturn = -1，确认是否存在这类记录。
2. 修复过滤逻辑：在cte_clean的NOT EXISTS条件中添加EX.AccountNumber = AR.AccountNumber，确保过滤逻辑符合预期：

   WHERE
       NOT EXISTS (
           SELECT
               1
           FROM
               cte_exclude EX
           WHERE
               EX.InsuranceNumber = AR.InsuranceNumber
               AND EX.AccountNumber = AR.AccountNumber
               AND EX.QuarterNumber = X.QuarterNumber
       )
   

3. 添加异常值处理：在计算LOG前，增加对1+AccountReturn <= 0的判断，避免因数据异常触发错误：

   CASE
       WHEN (1+AccountReturn) <= 0 THEN 
           1 -- 根据业务逻辑设置合理默认值
       ELSE
           (1+AccountReturn)
   END
   

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