CODEWITHSUNDEEP
sqlsql-serversql-server-2008
onedaywhen
onedaywhen

Reputation: 57023

Conversion failed when converting the varchar value to int

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

Upvotes: 8

Views: 17848

Answers (3)

Richard Y
Richard Y

Reputation: 1

This happened to me because I did a Union and was not careful to make sure both queries had their fields in the same order. Once I fixed that, it was fine.

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

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

Upvotes: 5

sgmoore
sgmoore

Reputation: 16067

What if you replace the section

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

With

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

Upvotes: 0

Related Questions