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

Microsoft SQL Server 2008 (SP1), getting an unexpected 'Conversion failed' error.

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

Not quite sure how to describe this problem, so below is a simple example. The CTE extracts the numeric portion of certain IDs using a search condition to ensure a numeric portion actually exists. The CTE is then used to find the lowest unused sequence number (kind of):

不太清楚如何描述这个问题，所以下面是一个简单的例子。 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
                  );

The error is, 'Conversion failed when converting the varchar value 'RR' to data type int.'

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

I can't understand why the value ID = 'ERR' should be being considered for conversion because the predicate ID LIKE 'A[0-9][0-9]' should have removed the invalid row from the resultset.

我无法理解为什么值ID ='ERR'应该被考虑用于转换，因为谓词ID LIKE'A [0-9] [0-9]'应该从结果集中删除了无效行。

When the base table is substituted with an equivalent CTE the problem goes away i.e.

当基表用等效的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
                  );

Why would a base table cause this error? Is this a known issue?

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

UPDATE @sgmoore: no, doing the filtering in one CTE and the casting in another CTE still results in the same error e.g.

UPDATE @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
                  );

3 个解决方案

#1

It's a bug and has already been reported as SQL Server should not raise illogical errors (as I said, it's hard to describe this one!) by Erland Sommarskog.

这是一个错误，并且已经被报告为SQL Server不应该引起不合逻辑的错误（正如我所说，很难描述这个错误！）由Erland Sommarskog。

The response from the SQL Server Programmability Team is, "the issue is that SQL Server raises errors [too] eagerly due to pushing of prediates/expressions during query execution without considering the logical result of the query."

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

I've now voted for a fix, everyone do the same please :)

我现在投票支持修复，每个人都这样做请:)

#2

What if you replace the section

如果您更换该部分该怎么办？

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

With

同

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

#3

This happened to me because I did a Union and was not careful to make sure both queries had their fields in the same order. Once I fixed that, it was fine.

这发生在我身上，因为我做了一个联盟并且不小心确保两个查询的字段顺序相同。一旦我修好了，就可以了。

#1