当将varchar值转换为int时，转换失败。

Question

微软SQL Server 2008（SP1）出现意外的“转换失败”错误。

不太确定如何描述这个问题，因此以下是一个简单的例子。使用搜索条件从某些ID中提取数字部分的CTE，以确保实际存在数字部分。然后使用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
                  );

- onedaywhen

---

核心的CTE运行正常 - 请尝试使用 SELECT * FROM ValidIDs 替换您的查询进行测试。错误一定在其他地方... - marc_s

@marc_s：我同意CTE不是问题的根源。我不确定CTE在底层是如何工作的，但我预计CTE的查询仅在解析时扩展为查询。事实上，使用两个派生表（每个具有完全相同的语法，当然）重新编写查询仍然会出现问题。 - onedaywhen

Answer 1

这是一个错误，已经被 Erland Sommarskog 报告为 SQL Server 不应该出现不合逻辑的错误（就像我说的，很难描述！）。

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

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

- onedaywhen

---

这个问题看起来也可能发生在Oracle中。https://stackoverflow.com/q/46287236/73226 - Martin Smith

Answer 2

如果你替换这个部分会怎样呢？

SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER)
FROM IDs 
WHERE ID LIKE 'A[0-9][0-9]'

使用

SELECT ID, CAST(RIGHT(ID, 2) AS INTEGER)
FROM 
(
    select ID from IDs 
    WHERE ID LIKE 'A[0-9][0-9]'
)

- sgmoore

---

非常奇怪，我不理解它。错误似乎出现在 WHERE V2.seq = V1.seq + 1 这一行。将其更改为WHERE isnumeric(v2.seq) = 1 and isnumeric(v1.seq) =1 and V2.seq = V1.seq + 1可以解决问题（对我而言）。 - sgmoore

Answer 3

这种情况发生在我身上，是因为我进行了联合操作，但没有仔细确认两个查询的字段顺序是否相同。一旦我解决了这个问题，一切就好了。

- Richard Y