Conversion failed when converting the varchar value to int

Question

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

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

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.'

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.

When the base table is substituted with an equivalent CTE the problem goes away 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 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.

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

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.

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."

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