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

JPA Myth: EclipseLink and Hibernate are compatible. Not true: Hibernate does NOT follow the JPA Spec

Please take a look at the Hibernate JIRA issue EJB-441, seen it?

Now, please tell me if this is correct:

According to Gavin King (author of Hibernate) the JPA spec the @Column(nullable=false) is just a schema generation hint, and therefore JPA implementations should NOT use it for object level validation.

I think that means that if an entity object with a null property marked with the @Column(nullable=false) is persisted using entityManager.persist() it should crash with a java.sql.SQLException as the root exception, thrown when the "INSERT" statement failed after it was sent to the database by the JDBC driver (as it happens in EclipseLink), and not because some implementation specific mechanism "pre-validated" it and prevented it from reaching the database (as it happens in Hibernate thanks to org.hibernate.engine.Nullability.checkNullability).

Is that correct? Because I don’t know anymore!

Lets say that is correct, does that means that the following is correct too?:

Validation should be entirely the responsibility of the JSR-303, and the properties of an entity should only be checked for nullability before sending to the database if a JSR-303 implementation is there to indicate that.

Is that true? And if it is, then:

What is exactly the difference between @Basic(optional=true) and @Column(nullable=true)? What happens when this 2 are applied to the same property, but they contradict each other? (As in : @Basic(optional=false) and @Column(nullable=true) or in @Basic(optional=true) and @Column(nullable=false)

I could not find a statisfactory answer in the spec documentation, would anyone be so kind as to resolve this issue? Because it Hibernate Forums they have decided that the way to deal with this inconsistencies and contradictions in Hibernate and in the JPA standard is to plain ignore me since May of 2009.

As you can see I tried to raise the issue with 2 JIRAS, forum post and easy to reproduce unit-tests, but nothing seems to work… what should I do then?

Refactoring Bug in VS.NET 2005

Every time you modify the name of an element in a webform, it triggers refactoring
and checks all the project... and that is just plain not necessary... it is a
really really annoying bug! Perhaps they will fix it in a future release.... I just
hope I could turn off all VS.NET refactorings... (and let Reshaper do the job)