sql - 将 varchar 值转换为 int 时转换失败

Question

Microsoft SQL Server 2008 (SP1)，出现意外的“转换失败”错误。

不太确定如何描述这个问题，所以下面是一个简单的例子。 CTE 使用搜索条件提取某些 ID 的数字部分，以确保数字部分确实存在。然后使用 CTE 查找最低的未使用序列号(某种):

CREATE TABLE IDs (ID CHAR(3) NOT NULL UNIQUE);

INSERT INTO IDs (ID) VALUES ('A01'), ('A02'), ('A04'), ('ERR');

WITH ValidIDs (ID, seq)
AS 
(
 SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER)
   FROM IDs 
  WHERE ID LIKE 'A[0-9][0-9]'
)
SELECT MIN(V1.seq) + 1 AS next_seq
  FROM ValidIDs AS V1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM ValidIDs AS V2
                    WHERE V2.seq = V1.seq + 1
                  );

错误是“将 varchar 值“RR”转换为数据类型 int 时转换失败。”

我不明白为什么应考虑转换值 ID = 'ERR'，因为谓词 ID LIKE 'A[0-9][0-9]' 应该从结果集中删除无效行。

当用等效的 CTE 替换基表时，问题就消失了，即

WITH IDs (ID)
AS
(
 SELECT 'A01'
 UNION ALL 
 SELECT 'A02'
 UNION ALL 
 SELECT 'A04'
 UNION ALL 
 SELECT 'ERR' 
),
ValidIDs (ID, seq)
AS 
(
 SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER)
   FROM IDs 
  WHERE ID LIKE 'A[0-9][0-9]'
)
SELECT MIN(V1.seq) + 1 AS next_seq
  FROM ValidIDs AS V1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM ValidIDs AS V2
                    WHERE V2.seq = V1.seq + 1
                  );

为什么基表会导致此错误？这是一个已知问题吗？

<小时/>

更新@sgmoore:不，在一个 CTE 中进行过滤并在另一个 CTE 中进行转换仍然会导致相同的错误，例如

WITH FilteredIDs (ID)
AS 
(
 SELECT ID
   FROM IDs 
  WHERE ID LIKE 'A[0-9][0-9]'

), 
ValidIDs (ID, seq)
AS 
(
 SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER)
   FROM FilteredIDs 
)
SELECT MIN(V1.seq) + 1 AS next_seq
  FROM ValidIDs AS V1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM ValidIDs AS V2
                    WHERE V2.seq = V1.seq + 1
                  );

Answer 1

这是一个错误，已被报告为 SQL Server should not raise illogical errors (正如我所说，很难描述这个!)作者:Erland Sommarskog。

SQL Server 可编程性团队的回应是:“问题在于，由于在查询执行期间推送谓词/表达式，而不考虑查询的逻辑结果，SQL Server 过于急切地引发错误。”

我现在已经投票支持修复，请大家也这样做:)