Friday, November 30, 2012

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

Tuesday, October 19, 2010

Web Slices: you need to use Alternative Display Source or javascript will not work

If your WebSlices use the Basic Web Slice model, javascript will not work (this file is WebSlice.html):

<html xmlns="">
<head runat="server">

<div class="hslice" id="SliceID">
     <span class="entry-title">Title of the web slice</span>
     <div class="entry-content">Preview of the <a href="#" onclick="document.getElementById('Message').innerHTML='Hello'; return false;">web</a> slice
       <div id="Message"></div>
     Hola Rebanadas Web!


you have to use Alternative Display Source to make javascript work (this file is WebSlice.html):

<html xmlns="">
<head runat="server">

<div class="hslice" id="SliceID">
      <span class="entry-title">Title of the web slice</span>
      <a rel="entry-content" href="AlternativeDisplay.html" style="display:none;">Alternative Display Source</a>
      Hola Rebanadas Web!


and in the file AlternativeDisplay.html you put the code that use to be in WebSlice.html (and that needs to use javascript):

<html xmlns="">
<head runat="server">
     <div class="entry-content">Preview of the <a href="#" onclick="document.getElementById('Message').innerHTML='Hello'; return false;">web</a> slice
        <div id="Message"></div>


Monday, August 02, 2010

How to migrate your local user profile to the domain

Apparently, there is no easy way (this or this do NOT work), there use to be a tool to do this (Moveuser.exe), but it stopped working with Windows Vista.

The way to do it now is to write a VBScript that uses WMI, happily, I have found someone that has already done it here.

It basically seems to be using the ChangeOwner Method of the Win32_UserProfile Class, one problem I have found so far is that if the profile to be “moved” in to the domain is heavy (many Gbytes of weight) the ChangeOwner method can take a really long time to do its job. A possible solution then is to move the files outside of the profile dir, run the migration process, and copy the files back (you might need to change the permission information of those files to be able to copy them back)

Friday, July 16, 2010

How to move C:\Users to D:\Users

There is no user friendly way to move your C:\Users folder to D:\ but the user unfriendly way to do it is not too hard (it was a little hard to discover, I learned how to do this when I decided that I wanted to have C:\Winnt\Profiles dir at a different location, after I lost most of my data when my main disk failed, back when Windows NT 4.0 was the latest and greatest OS from Microsoft)

Basically, after you installed Windows (this instructions are for Windows 7 but the general approach is valid since Windows NT 4.0)  you have to:

  1. Login with “WhatEverIsYourUserName” account.
  2. Run the Command Prompt (cmd.exe) as an Administrator
  3. image
  4. Copy the C:\Users\ folder to D:\Users using Robocopy
    robocopy C:\Users D:\Users /E /COPYALL /R:0 /Z /XJ

  5. Open regedit and modify the ProfileList entry so that it looks like this:


  1. Create a new (Administrator) Windows account that you will use to test the configuration change


  2. Restart

  3. Login with the “Test” Windows account

  4. Delete C:\Users

  5. See that the  Start menu still works (if you made a mistake, your Start menu is now empty)

  6. Delete the registry Key for your “WhatEverIsYourUserName” account (if you still want to use it). (The registry Key is the yellow folder with the S-1-5… name that contains the value ProfileImagePath that points your now obsolete C:\ folder, remember,  you have to delete the whole folder/key, not just the ProfileImagePath value):


  7. Log out

  8. Now log in with your “WhatEverIsYourUserName” account

  9. If you take a look at the registry now, it will look like this (The value in ProfileImagePath now starts with “D:\” instead of “C:\”):


  10. Now you can delete the test account.

And that is it, now the users accounts live in D:\Users\

Thursday, July 08, 2010

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' 
alter login sa enable 

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:

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

And  change the value of LoginMode to 2.

Monday, March 15, 2010

Formulating Expressions a Step at a Time: Lazy Evaluation

I am reading this book, and I found the section with the title “Formulating Expressions a Step at a Time” particularly interesting:

First, the book shows you this the query for the sentence "Get pairs of supplier numbers such that the suppliers concerned are collocated (i.e., are in the same city)" written in Tutorial D:

( ( ( S RENAME ( SNO AS SA ) ) { SA , CITY } JOIN
( S RENAME ( SNO AS SB ) ) { SB , CITY } )
WHERE SA < SB ) { SA , SB }

And then it proceeds to show you how to write this query in a more readable (step by step) way:

( S RENAME ( SNO AS SB ) ) { SB , CITY } AS R2 ,
R1 JOIN R2 AS R3 ,
R4 { SA, SB }

Finally, it shows you how to write this query in SQL:

FROM S ) ,
FROM S ) ,

Thanks to the “with” keyword, both in SQL and in Tutorial D, it is possible to deal with this query in a “step by step” way, instead of having to deal with it a single hard to write and hard to read expression (note that this is not a recursive query, so the with keyword is not being used for that in this examples)

Sadly, so far I have been unable to find a equivalent for this syntax in Dataphor… While it is possible to write something like (I am not 100% confident the syntax is right, but I think it should give you the general idea):

var R1 := S {SNO SA}
var R2 := S {SNO SB}
var R3 := R2 JOIN R3
var R4 := R3 WHERE SA < SB
select R4 {SA, SB}

In Dataphor, the variables (R1, R2, etc) are not lazily evaluated, and therefore the performance is not as good, as in, for example, the SQL case (I ran a similar example in SqlServer, and the expressions were evaluated lazily, at the end, instead of one by one, resulting in far better performance). Maybe I am doing something wrong?

I wonder how hard would it be to make Dataphor generate SQL using the WITH keyword with those databases that support it (so far the latest versions of SQLServer (2008) and Oracle (10 & 11) seem to support this syntax)… I guess it is time to ask the Dataphor authors…

Thursday, March 04, 2010

Null versus None

None means Nothing, Nothing is a concept that describes the absence of anything at all. Nothing is sometimes confused with Null, but they are very different concepts because Nothing means absence of anything, while Null means unknown (you do not know if there is a thing or not).

For Nothing, the normal Two valued logic applies (Nothing=Nothing : true, Nothing = Something : false), for Null, Three valued logic is necessary(Null=Null:unknown, Null=Something:unknown). Unfortunately, this 2 concepts have been used interchangeably without much thought, to point where the most common use for Null in relational databases is to mean Nothing (even when Null was designed to represent unknown by Codd). This confusion is aggravated by the fact that many mainstream application languages (Java, C#, C, etc) use the null keyword to mean uninitialized variable which easily maps to the interpretation that null means the variable is pointing to "nothing" (no object).

But for databases, Null was not invented to represent nothingness, was invented to represent that the value of something was not known (maybe be something, maybe nothing, we just do not know).

Now that Chris Date wants Null to be removed from Relational Databases, so that the incongruence and confusion brought in by Three valued logic is eliminated, the developers, accustomed to use Null to represent Nothing, resist to the idea asking: How am I going to represent the fact that a Person is not married? I use to do that by marking the Marriage Date? as a nullable Date. Now what? I need to split the table into 2 tables just to represent the fact that the Marriage Date? is not mandatory? That of course seems like the obvious, elegant (if extremely cumbersome answer). But the practical developer refuses to get into that trouble, it is just too much effort, it is simple easier to continue using Null. But... what about Nothing? why not just simply add "Nothing" as a possible value for to the Date domain? That way it is possible to say that the Person has no Marriage Date?, and still stay inside the realm of two valued logic.

Is this solution, in any way in conflict with The Third Manifesto? I really would like to know… I wonder what will be the opinion of the community in the  C2 Wiki