SQL 2012 Bug: NVarchar changes where evaluation order

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

Recover sa account

Today, a friend from the office locked himself out of SqlServer 2008 R2 Express, somehow he managed to remove the windows Administrator account form the list of sysadmin accounts in the SqlServer express installed in a development server.

I thought there was no way to recover from a mistake a like that, but another friend found the way and gave us a link with the solution.

Basically, we need to stop the SqlService, and restart it in "single user mode" and "minimal config mode"

sqlservr.exe -m -r -s SQLEXPRESS

And the open another command window and run this commands:

osql -E -S .\SQLEXPRESS 
exec sp_password @new='changeme', @loginame='sa' 
go 
alter login sa enable 
go 
exit 

And that was it. Remember, without the special "-m -r" options, the osql commands will fail.

NOTE: In case your SQL Server is not configured to use Mixed Mode for authentication, you may also need to make some modification to the registry settings.

Basically you need to find the registry entry for the instance:

Default instance:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

And  change the value of LoginMode to 2.

Dataphor: How to create a table with a primary key

Creating a table in Dataphor is easy:

create table Debt  
{  
    ID : Integer,

    key { ID },
};

That translates (when using MSSQL as storage) into (if we ask MSSQL to provide us with the SQL DDL):

CREATE TABLE [dbo].[Debt](   
    [ID] [int] NOT NULL 

)
 
CREATE UNIQUE CLUSTERED INDEX [UIDX_Debt_ID] ON [dbo].[Debt] 
( 
    [ID] ASC 

)

It is important to note that D4 does not have the concept of a primary key, and that the “key” keyword results in a unique index, that while for all uses and purposes can work as a primary key, it is not recognized as such by MSSQL. It is possible to have several different “keys” in a Dataphor table.