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