title: Conversion failed when converting the varchar value to int
body: <p>Microsoft SQL Server 2008 (SP1), getting an unexpected 'Conversion failed' error.</p>
  
  <p>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): </p>
  
  <pre><code>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
                    );
  </code></pre>
  
  <p>The error is, 'Conversion failed when converting the varchar value 'RR' to data type int.' </p>
  
  <p>I can't understand why the value <code>ID = 'ERR'</code> should be being considered for conversion because the predicate <code>ID LIKE 'A[0-9][0-9]'</code> should have removed the invalid row from the resultset.</p>
  
  <p>When the base table is substituted with an equivalent CTE the problem goes away i.e. </p>
  
  <pre><code>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
                    );
  </code></pre>
  
  <p>Why would a base table cause this error? Is this a known issue?</p>
  
  <hr>
  
  <p>UPDATE @sgmoore: no, doing the filtering in one CTE and the casting in another CTE still results in the same error e.g. </p>
  
  <pre><code>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
                    );
  </code></pre>