SQL Server CTE Implicit Column Datatype issue

Question

I'm running into a really odd error using a SQL Server Common Table Expression (CTE).

I think it is easiest to explain my issue by giving a code example.

CREATE TABLE #Test1 (TestCol varchar(3));

INSERT INTO #Test1 VALUES ('012')
INSERT INTO #Test1 VALUES ('ABC')

--This simple shows the Cast works as expected, and only on rows where the TestCol value is numeric
SELECT TestCol, CAST(TestCol as int) as IntCast 
FROM #Test1 
WHERE ISNUMERIC(TestCol) = 1;

--Here I create a cte using the same SQL as show above.
with cte as (
    SELECT 
       TestCol, CAST(TestCol as int) as IntCast 
    FROM #Test1 
    WHERE ISNUMERIC(TestCol) = 1
)

/*
I have two examples below. The first can be executed to check for the existence of our 'ABC' value.  It doesn't show up, which is expected. 
The Second example, simple checks to see if our IntCast column is greater than 10.  This throws an exception
*/

--SELECT * FROM cte
SELECT * FROM cte WHERE IntCast > 10

DROP TABLE #Test1

The exception here is

Conversion failed when converting the varchar value 'ABC' to data type int

I'm curious as to where this is occurring?

My guess would be `CAST(TestCol as int)` ? Try it without the `CAST`? — Kermit, Nov 19 '12 at 15:43
@Jim - Exactly. It looks like the order in which things are done are getting changed by the CTE and so the cast is attempted before the filter is applied. However, done without the cte, it will work. Strange behavior. — Jeff Reddy, Nov 19 '12 at 15:49
Subqueries appear to have the same problem: SELECT * FROM ( SELECT CAST(TestCol AS INT) AS IntCol FROM #Test1 WHERE ISNUMERIC(TestCol) = 1 ) AS sub WHERE IntCol > 10 — Jim, Nov 19 '12 at 15:51
[Explained here](http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors) — Martin Smith, Nov 19 '12 at 16:06
possible duplicate of [error converting varchar to float](http://stackoverflow.com/questions/8085015/error-converting-varchar-to-float) — Martin Smith, Nov 19 '12 at 16:08
looks like the cast is happening before the WHERE, it's getting the whole resultset first and then it filters out the results by using the WHERE; — fnurglewitz, Nov 19 '12 at 16:49

score 3 · Accepted Answer · answered Nov 19 '12 at 16:04

3

with cte as (
    SELECT TestCol, Case when ISNUMERIC(TestCol) = 1 then CAST(TestCol as int) else NULL end as IntCast FROM #Test1  
)

answered Nov 19 '12 at 16:04

bummi

27,123
14
62
101

This fixes my issue. I appreciate the work around, although I was more interested in the 'why it happened' than the 'how to work around it'. I have accepted your answer all the same. – Jeff Reddy Nov 19 '12 at 17:18
Sorry, didn't know, inmy opinion the optimizer seems to take a mysterious path of full evaluation ... – bummi Nov 19 '12 at 17:26

SQL Server CTE Implicit Column Datatype issue

1 Answers1