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.

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:

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

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

WITH T1 AS ( SELECT SNO AS SA , CITY
            FROM   S ) ,
    T2 AS ( SELECT SNO AS SB , CITY
            FROM   S ) ,
    T3 AS ( SELECT *
            FROM   T1 NATURAL JOIN T2 ) ,
    T4 AS ( SELECT *
            FROM   T3
            WHERE  SA < SB )
    SELECT SA , SB
    FROM   T4

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…

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

Pagination in Dataphor

First I created this operator:

create operator GetNext(var AID :Integer) : System.Integer 
begin 
AID:= AID+1; 
result:=AID; 
end ;

and then I used it:

var id := 0; 
select SomeTable add {GetNext(id) rowid}; 

It is nice, but it would be better if there were a way to do this without having to create an operator myself (it should already include one that does this)

Now If I want to do pagination, all I have to do is write:

var id := 0; 
select SomeTable add {GetNext(id) rowid} where rowid between 2 and 5;

That will return rows between 2 and 5 from the “SomeTable” table.

Now one of the most interesting feaures about Dataphor, are relvars (relational variables), that make it possible to do this:

var SomeComplexQuery := SomeTable where …...
var id := 0;
select SomeComplexQuery add {GetNext(id) rowid} where rowid between 2 and 5;

That way, pagination can be added to a query of arbitrary complexity with minimum effort.

Functionally Complete Crud Generator

In my previous post I wrote about the Irreducible Complexity Model ….now why is that model so important?

Well, for a Crud Generator to be functionally complete it has to fulfill the following requirements:

1. Be able to generated the Crud Screens for the Irreducible Complexity Model
2. It should be possible to “capture” the entire “model graph” as a single transaction starting from any node in the graph
3. It should be possible to “skip” validation until the “save” button of the screen to save the node where we started is clicked, and, it should be easy to go from there to any of the screens used to edit the other nodes to fix any validation error.
4. It should be possible to search for instances (rows?) of the entity (class, table?) by using a “query by example” node graph that can be built using one or all of the nodes of the model with any arbitrary depth.

With this rules, I realize that the only functionally complete crud generator that I have seen was Apple WebObjects Direct2Web… All others (JBoss SeamGen for example) are unable to deal with all the kinds of relationships in the Irreducible Complexity Model and, even without that limitation, are just unable to deal with requirements 2,3, and 4. because all of them force you to either violate transactionality by forcing you to save before navigation to the next node, or force you to pass all validation rules at a particular node before going to the next, or, have a really simple and powerless query by example UI.

Irreducible (Minimal?) Complexity: EntityModel (RelationalModel? DatabaseModel?)

I am not sure that the name Irreducible Complexity Entity Model actually fits what I am about to describe, but I was the best I can think of:

Lets think of an Entity Model (Database Model?, Relational Model?) that has at least one instance of each kind of entity relationship, so that it becomes representative of pretty much anything that can happen at any database:

• A One To One relationship
• A One To Many relationship

So we would have:

• TableA One To One TableB
  TableB One To Many TableC

But then, we remember that there are recursive relationships so the list now is:

• A One To One relationship to the same table
• A One To One relationship to another table
• A One To Many relationship to the same table
• A One To Many relationship to another table

So now we have:

• TableA One To One TableA
• TableA One To One TableB
• TableB One To Many TableB
• TableB One To Many TableC

But then we realize that there is si not representative of any case, because there is another kind of relationship, one that is “complex” (formed by 2 of the “simple” ones described before: Many To Many) so:

• A One To One relationship to the same table
• A One To One relationship to another table
• A One To Many relationship to the same table
• A One To Many relationship to another table
• A Many To Many relationship to the same table
• A Many To Many relationship to another table

So we have:

• TableA One To One TableA
• TableA One To One TableB
• TableB One To Many TableB
• TableB One To Many TableC
• TableC Many To Many to TableC (using TableCC as intermediate)
• TableC Many To Many to TableD (using TableCD as intermediate)

Now, with this one would think we have a representative case for each “conceptual” kind of entity relationship. Am I right? (For example Object Relational Mappers? offer a way to map relationships for their Object Oriented counterparts, and the only kinds of relationships that are supported by them (when going from the database to the object model) are: One To One, One To Many? and Many To Many?. (If going from the Object Model to the database, then "Inheritance" enters the game, so I guess the Irreducible Complexity Entity Model would have to include "Inherits From?" as a relationship type (note that Inherits From? is the only kind of relationship that can not be used in "reflective" way):

* A One To One relationship to the same class
* A One To One relationship to another class
* A One To Many? relationship to the same class
* A One To Many? relationship to another class
* A Many To Many? relationship to the same class
* A Many To Many? relationship to another class
* A Inherits From? relationship to another class

And so we get:

* Class A One To One Class A
* Class A One To One Class B
* Class B One To Many Class B
* Class B One To Many Class C
* Class C Many To Many to Class C
* Class C Many To Many to Class D
* Class D Inherits From Class E

But, then we realize there is another case, what if the relation is not to a concrete but to an abstract class?

• Composition
• A One To One relationship to the same class
• A One To One relationship to another class
• A One To Many relationship to the same class
• A One To Many relationship to another class
• A Many To Many relationship to the same class
• A Many To Many relationship to another class
• Inheritance
• A Inherits From relationship to another Concrete Class?
• A Inherits From relationship to an Abstract Class

And then (finally?) we realize that this "Concrete/Abstract" separation also applies to the Composition relationships:

• Composition
• A One To One relationship to the same abstract class
• A One To One relationship to the same concrete class
• A One To One relationship to another abstract class
• A One To One relationship to another concrete class
• A One To Many relationship to the same abstract class
• A One To Many relationship to the same concrete class
• A One To Many relationship to another abstract class
• A One To Many relationship to another concrete class
• A Many To Many relationship to the same abstract class
• A Many To Many relationship to the same concrete class
• A Many To Many relationship to another abstract class
• A Many To Many relationship to another concrete class
• Inheritance
• A Inherits From relationship to another Concrete Class?
• A Inherits From relationship to an Abstract Class

I wonder if I am missing a particular combination/ relationship kind… (or if there is a simpler way to express this…)