Today I arrived late at my house because my team had a big problem migrating a stored procedure from SQL Server 2000 to SQL Server 2012. Countless hours lost trying to find out what we could be have been doing wrong...
On the end, in turned out, all the trouble was because of a bug in SQL Server 2012.
Here is what we found, lets say you have a table "T_1":
CREATE TABLE [dbo].[T_1](
[C] [nvarchar](50) NULL
)
Now, lets say you add some rows to it.
INSERT INTO [dbo].[T_1] ([C]) VALUES ('P')
INSERT INTO [dbo].[T_1] ([C]) VALUES ('Q')
INSERT INTO [dbo].[T_1] ([C]) VALUES ('R')
Now write this query:
select * from T_1 where ISNUMERIC(C)=1 and CONVERT(float,C)=0.0
And you will get a nice error message:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.
¿What is the bug? You shouldn't be getting an error message! the "Error converting data type nvarchar to float." is generated by the CONVERT(float,C)=0, but SQL should never run that code because ISNUMERIC(C)=1 evaluates to false, and if the first part of an "and" is false, there is no point in executing the second part, the result is going to be false anyway.
Well you might say, maybe SQLServer has always done things this way... well, no, it has not. In SQL 2000, that query executes correctly!
And in SQL 2012, there is a workaround, just change from nvarchar to varchar:
CREATE TABLE [dbo].[T_2](
[C] [varchar](50) NULL
)
INSERT INTO [dbo].[T_2] ([C]) VALUES ('P')
INSERT INTO [dbo].[T_2] ([C]) VALUES ('Q')
INSERT INTO [dbo].[T_2] ([C]) VALUES ('R')
Now if we write (note we are now working with the table T_2 that uses varchar) :
select * from T_1 where ISNUMERIC(C)=1 and CONVERT(float,C)=0.0
We will get no error.
Why is this happening then?
It seems to be a bug in SQL Server 2012 execution plan:
As you can see in the image, SQL 2012 inverts the predicate when working with nvarchar!
On the other hand, for T_2 the table with a varchar column:
As you can see, here the the order of evaluation is preserved, and things work like they should.
Now what can we do if we are not allowed to change the type of the column in the table?
I tried using a CTE, but it does not work, the execution plan is the same faulty one:
with V_1 as (select * from T_1 where ISNUMERIC(C)=1)
select * from V_1 where CONVERT(float,C)=0.0
Using a subquery also fails:
select * from (select C from T_1 where ISNUMERIC(C)=1) V_1 where CONVERT(float,C)=0.0
We can change the type explicitly to varchar in the query, that fixes the problem:
select * from T_1 where ISNUMERIC(C)=1 and CONVERT(float,convert(varchar(100),C))=0.0
But, what if we actually have an Unicode string with chars that will get damaged by a conversion to varchar?
I shouldn’t be a problem…. What do you think? any other workaround?
UPDATE: I have submitted this bug to Microsoft Connect, click here to see my bug report
