Conversion error when converting varchar value to int

Question

Microsoft SQL Server 2008 (SP1), receiving an unexpected conversion error.

Not quite sure how to describe this problem, so here is a simple example. The CTE retrieves the numerical part of specific identifiers using a search term to ensure that the numerical part actually exists. Then CTE is used to find the smallest unused sequence number (type):

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

Error: "Conversion error when converting varchar" RR "value to int data type."

I cannot understand why the value ID = 'ERR' should be considered for conversion, because the predicate ID LIKE 'A[0-9][0-9]' had to remove the invalid row from the result set.

When the base table is replaced by an equivalent CTE, the problem disappears, i.e.

 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 does the base table cause this error? Is this a known issue?

---

UPDATE @sgmoore: no, doing filtering in one CTE, and casting in another CTE still leads to the same error, for example.

 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

This is a mistake and has already been reported how SQL Server should not raise illogical errors (as I said, it is difficult to describe this!) By Erland Sommarskog.

Response of the SQL Server programmer's team: “The problem is that SQL Server is looking [too] impatiently because of predicates / expressions being pressed during query execution without taking into account the logical result of the query.”

Now I voted for the fix, everyone is doing the same :)

Answer 2

What if you replace the partition

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

FROM

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

Answer 3

This happened to me because I made the Union and was not careful that both requests had their fields in the same order. As soon as I fixed it, everything was in order.