February 12, 2009 at 10:24 pm
Now here's a fun bug in SQL Server 2005 (9.00.3175.00)
CREATE TABLE #tblTesting (TestStr VARCHAR(255))
--same result if VARCHAR(MAX)
--same result if real or temp table
INSERT INTO #tblTesting (TestStr) VALUES ('asdfjkl')
SELECT * FROM #tblTesting
--this is what we should be doing:
UPDATE #tblTesting
SET TestStr = CASE
WHEN TestStr = 'False' --miss
THEN '0'
WHEN TestStr = 'True' --miss
THEN '1'
ELSE TestStr --hit
END
--but see what happens if we leave out the quotes:
UPDATE #tblTesting
SET TestStr = CASE
WHEN TestStr = 'False' --miss
THEN 0 --SQL shouldnt even evaluate this line
WHEN TestStr = 'True' --miss
THEN 1 --SQL shouldnt even evaluate this line
ELSE TestStr --hit
END
/***********************************************************************************
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'asdfjkl' to data type int.
***********************************************************************************/
SELECT * FROM #tblTesting
DROP TABLE #tblTesting
You'd think SQL shouldn't have even attempted to evaluate the THEN statements, but it sure sounds like it did!
And even then, whats with the error message?
(I thought precompilation would have prevented this case of late binding?)
I'm trying to figure out what the SQL engine is getting up to here. Any ideas?
February 12, 2009 at 11:12 pm
UPDATE #tblTesting
SET TestStr = CASE
WHEN TestStr = 'asdfjkl' --miss
THEN 0 --SQL shouldnt even evaluate this line
WHEN TestStr = 'True' --miss
THEN 1 --SQL shouldnt even evaluate this line
ELSE TestStr --hit
END
THEN 0 and THEN 1 are valid because it is converted implicitly to string.
Suppose u give the query like below it updates TestStr with the value 0 but as string
UPDATE #tblTesting
SET TestStr = CASE
WHEN TestStr = 'asdfjkl' --miss
THEN 0 --SQL shouldnt even evaluate this line
WHEN TestStr = 'True' --miss
THEN 1 --SQL shouldnt even evaluate this line
ELSE TestStr --hit
END
/***********************************************************************************
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'asdfjkl' to data type int.
***********************************************************************************/
The error is because, in the case statement all the values that u are using to set must be of the same datatype. It then implicitly converts valid datatypes to the field's datatype
February 13, 2009 at 6:08 am
As I understand it, SQL server is first evaluating the CASE statement, without looking at what it's going to be used for. CASE
WHEN TestStr = 'False' --miss
THEN 0 --SQL shouldnt even evaluate this line
WHEN TestStr = 'True' --miss
THEN 1 --SQL shouldnt even evaluate this line
ELSE TestStr --hit
END
This fails because the engine tries to convert the result values to be all the same type, and it's selected int because that's what it saw first. It doesn't matter that it's not evaluating the INTs. Hence the conversion of the varchar(255) value 'asdgfkl' fails and gives an error. The fact that it's going to have to convert the result of the CASE to a varchar(255) to assign it to the field isn't relevant.
BTW, I would use the alternate form.CASE TestStr
WHEN 'False' THEN '0'
WHEN 'True' THEN '1'
ELSE TestStr
ENDBut that's probably just a matter of personal preference.
Derek
February 15, 2009 at 12:32 pm
[font="Verdana"]It's not a bug. SQL Server has to evaluate the expression (case statement) as a whole to decide what type to give the return value. It doesn't chop and change types mid-way. So the first type it hits is an integer, and hence it decides to try and cast your string result to an integer.
The solution is: don't rely on implicit type casts.
[/font]
February 15, 2009 at 3:17 pm
Ah! We had an inkling that this was happening, but not sure why. Well put.
Any idea why the late binding? You think this late binding introduces much of an overhead?
February 15, 2009 at 3:58 pm
Paul Harvey (2/15/2009)
Any idea why the late binding? You think this late binding introduces much of an overhead?
[font="Verdana"]Scratches head
I think it's early binding.
If it were late binding, it would be able to change types dynamically, and your statement wouldn't be an issue, as it would assign the type of varchar to the result after the first evaluation.
Instead it early binds the type, then generates an error as soon as it evaluates a result that doesn't match to the type it is expecting. Hence your error.
[/font]
February 15, 2009 at 4:07 pm
I see. So it probably does early binding on the result of the first branch of the case statement during precompilation. I can control what it binds to by placing the correct type on the first branch, or better yet, a bit of type casting. Thanks Bruce.
February 15, 2009 at 4:16 pm
[font="Verdana"]Indeed.
Actually, the place I saw this more commonly was in unions, which have the same effect. The first statement within the union defines the types for the columns.
[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply