代码之家 › 专栏 › 技术社区 › onedaywhen

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

sql-server-2008 sql-server sql

8

onedaywhen · 技术社区 · 16 年前

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

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:no,在一个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 回复 | 直到 16 年前

1

4

onedaywhen 16 年前

这是一个bug,已经被报道为 SQL Server should not raise illogical errors

SQLServer可编程性团队的回答是:“问题是,SQLServer在执行查询时推送predites/表达式,而不考虑查询的逻辑结果,从而急切地引发错误。”

2

0

sgmoore 16 年前

如果你换掉那部分呢

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]'
)

3

0

Richard Y 13 年前

这种情况发生在我身上是因为我进行了联合,并且不小心确保两个查询的字段顺序相同。一旦我修好了,就没事了。