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

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

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

UPDATE @sgmoore：不,在一个CTE中进行过滤而在另一个CTE中进行过滤仍会导致相同的错误,例如

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

),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
                  );

解决方法

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!