CTE与SUBSTRING/CHARINDEX使用疑问：WHERE子句触发函数参数错误

问题背景 #

• 原本以为CTE是预计算好的临时表，创建后可直接引用其数据，不会重复执行内部逻辑
• 处理逻辑：certifiedBy字段格式为name:date:id:，若该字段为NULL，将CERT_DATE设为'1/1/1970'；否则从字段中提取日期部分赋值给CERT_DATE
• 异常现象：单独执行SELECT * FROM Certified时，CERT_DATE结果符合预期；但添加WHERE CERT_DATE BETWEEN '2/1/2023' AND '2/22/2023'过滤条件后，触发错误：

Invalid length parameter passed to the LEFT or SUBSTRING function.

涉及SQL代码 #

WITH Certified AS (
    select
        certifiedBy,
        CASE WHEN certifiedBy IS NULL  THEN '1/1/1970' ELSE SUBSTRING(certifiedBy, CHARINDEX(':', certifiedBy, 1) + 1, CHARINDEX(':', certifiedBy, 
        CHARINDEX(':', certifiedBy, 1) + 1) - CHARINDEX(':', certifiedBy, 1) - 1)  
        END AS CERT_DATE
    From dbo.PO_Orders 
    WHERE poreference = 'shev'  
    )
SELECT * FROM Certified 
WHERE CERT_DATE BETWEEN '2/1/2023' AND '2/22/2023' 
ORDER BY CERT_DATE

排查线索 #

• CTE执行逻辑本质：CTE并非预存储结果的临时表，SQL优化器会将CTE的逻辑与外层查询合并执行，甚至会把WHERE过滤条件下推到CTE的底层查询中。这意味着外层的WHERE子句可能导致部分数据提前进入CTE的SUBSTRING计算，而这些数据可能不符合预期格式。
• 检查脏数据：查看PO_Orders表中poreference = 'shev'的记录，确认是否存在certifiedBy非NULL但格式不符合name:date:id:的情况（比如只有一个冒号、冒号缺失）。当CHARINDEX找不到第二个冒号时会返回0，此时计算出的SUBSTRING长度为负数，直接触发错误。
• 修改防御性逻辑：优化日期提取的代码，避免出现负数长度：
  可以通过CASE判断确保长度参数合法，示例：

  SUBSTRING(certifiedBy, 
            CHARINDEX(':', certifiedBy) + 1, 
            CASE 
                WHEN CHARINDEX(':', certifiedBy, CHARINDEX(':', certifiedBy) + 1) > CHARINDEX(':', certifiedBy)
                THEN CHARINDEX(':', certifiedBy, CHARINDEX(':', certifiedBy) + 1) - CHARINDEX(':', certifiedBy) - 1
                ELSE 0 -- 若找不到第二个冒号，返回空字符串或其他默认值
            END)
  

  也可以使用STRING_SPLIT（SQL Server 2016+）配合TRY_CAST来更安全地提取日期，降低格式错误的影响。
• 分析执行计划：查看查询的执行计划，确认WHERE子句是否被下推到CTE内部，导致脏数据提前参与计算。

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