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

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.

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.

Greenspuns Tenth Rule Of Programming and ADO.NET Data Services

This is a humorous observation once made by Philip Greenspun:

Any sufficiently complicated C or Fortran program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of CommonLisp.

Or the database variant (Greencodds Tenth Rule Of Programming):

Every sufficiently complex application/language/tool will either have to use a database or reinvent one the hard way

I sometimes I feel that:

Any sufficiently complicated procedural api contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of query language.

Take for example, Web Services, they started a procedural thing where people created their own custom verbs for each service, then people realized that the semantics of HTTP constitutes a coordination language which is is sufficiently for any computational communication pattern, they realized that GET / PUT / POST / were enough for any program, but they again forgot about the query part, so after a while they reinvented a query language (like SQL) by creating create a query language based on URL query strings, as in ADO.NET Data Services)

Sometimes I feel all IT industry is like a dog chasing its own tail… we invent Cobol to handle data procedurally only to invent SQL to handle data declaratively only to invent stored procedures and web services to handle it procedurally to then invent REST to handle it declaratively I wonder what will be the name of the procedural silver bullet that will replace REST…

EntityManager.persist: ¿What does/should it mean?

Lets say you are presented with the following JPA @Entity:

@Entity
public class Customer {   
  private Long id;
private String name;
  @Id
@GeneratedValue(strategy=GenerationType.AUTO)
public Long getId() {
    return id;
}   
  public void setId(Long id) {
    this.id = id;
}
  @Column(nullable=false)
public String getName() {
    return name;
}   
  public void setName(String name) {
    this.name = name;
}
}
 

And then the following test

   @Test
public void persistCustomerInTransaction() throws Exception {
    factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
      EntityManager em = factory.createEntityManager();
      em.getTransaction().begin();
      Customer customer = new Customer();
      em.persist(customer);em.getTransaction().commit();
      em.close();   
  }

And the following question: Where does it crash?:

1. At the line em.persist(customer); because the name is configured as nullable=false, and we are trying to persist the Customer instance with a null value in the name property



2. em.getTransaction().commit(); because the name is configured as nullable=false, and we are trying to commit the transaction with a null value in the name property

Turns out… that the answer depends on the JPA provider you are using!!!! If using Hibernate, it crashes at em.persist, but if using EclipseLink, it crashes at em.getTransaction().commit.

Now, this might seem irrelevant, but it is in fact very important, because EclipseLink behavior means that constraint validations are deferred until the point where the transaction is committed, and that means that the developer has a lot more freedom to manipulate its persistent objects: they can be in any (possibly invalid) state while they are being manipulated by the business logic of the application, and they only have to be “right” the moment one needs to commit them to the database (not before), this is specially useful, for example, when building a wizard like UI (or a plain simple CRUD UI with support for full object graphs) with EclipseLink, I can persist my objects as soon as I want, and if want to, for example, run some validation logic at the end, all I have to do is ask the EclipseLink to give a list of all the objects that are going to be written in to the database, with Hibernate, the entityManager does not help me manage new object instances, I have to manage them myself.

What I find really surprising is that AFAIK there should be some kind of test to ensure that all JPA are compatible (something called the TCK?), and I think that this kind of discrepancy should be detected by those tests... shouldn't it?


I think this support for deferred validation, and an API to get will be inserted, will be updated or will be deleted, will be very important to really integrate JPA with something like JSR 330, so that validation can really integrate with the lifecycle of a persistent POJO.

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…)

Data Transfer Object Injection

Data Transfer Object injection is a programming error which results in security holes., it is to a Remote Object Service based applications which use object graphs what SQL Injection is to web-based applications which use databases.

DTO injection could happen where there is a remote object service that allows a client system to send and and object graph that is automatically converted by an object relational mapper in to SQL statements.

Instead of sending a valid object graph, the attacker can send a different object graph, representing alterations to the database that go well beyond his security level. For example, a remote object service receives an object graph that represent changes in the objects that represent new users, or new permissions granted for existing users of the system.

To prevent this problem it should be possible to specify at the object relational mapping level, which entities can be saved by the current user... many object relational mappers, or xml relational mappers automatically write the changes represented by the object graph to the database, without caring if the current application user has the privileges required to persist those objects... we can not rely on RDBMS security, because most remote object services use the same user for all the calls... and I think it that connecting with a different user for each remote object service would be bad for connection pooling (decreasing performance)

I wonder if anyone else thinks this is a common security problem... Mmmm... I will add this to C2... I wonder how (or if it) will evolve.

Unit testing Relational Queries (SQL or HQL or JQL or LINQ...)

Hi!
Most applications I have built have something to do with a database (I remember that while I was on college I used to think that was not exiting stuff, I used to dream about doing neural networks stuff, logic programming in Prolog, etc) but then I met WebObjects and its Object Relational Mapper (Enterprise Objects Framework) and I got really excited about object oriented data programming... but I always had a problem... to test my object oriented queries I had to "manually" translate them into SQL and test them against the database, and only after they give me what I thought were correct result I would write them using EOF Qualifiers...
Then I met Hibernate's HQL and I realized it is much more powerful than EOF Qualifiers, but I still had to translate it to SQL to test it, I know I can get the SQL that is generated from the HQL from the debug console, and paste it in my favorite SQL editor... but even then if I found a mistake, a lot of times it was easier to tweak it in SQL and the manually translate it HQL.
Currently, there are some extensions for Eclipse (Hibernate Tools) that make this more "direct" but, what if I don't like (or don't want, or can't) use Eclipse... it would be great if someone could, for example, make a plugin for SquirrelSQL, but until then... what options do I have?

Then I learned about unit testing... and the answer came to my mind immediately: I just had to write a unit test for each of my queries. That worked fine... in the beginning... until I started having queries that returned thousands (or millions) of objects, and it wasn't such a good idea to output them to the debug console... and I had another problem... how should I write the "asserts" of query?... and how can I do it so that it doesn't make my test so slow that it becomes unusable? (I can, of course, check the results just by viewing them, but my brain is not that good to say if those 10,000 row really match with the idea I had when I wrote that HQL)

So, I started to look "what do I do" to check if an SQL query is correct, lets say for example, that I write this:

select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3

(Translated to English: How many address the Employee with Id = 3 has?)

Now... how do I test that? well I could add an assert after getting the result in java (or c#) like this:

assert(count>0)

But the, what happens if someone deletes the row with Id = 3 from the table Employee? That means my test will fail... or what what if someone deletes all the addresses from employee? and what if I want to test that if there are no addresses for an employee, the answer should be zero...

That is a lot of work just to test if that simple query is right... and I think that work could be done automatically:

Take a look at the SQL, it could be decomposed into:

select count(*) from (select * from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3) as EmployeeAddresses

And then we could say, lets automatically check for the case when the resulting set is empty, and for the case when the result is not empty, to check for a case when the result is empty, we need and Employee with Addresses, so we generate:

Select * from Employee where exists(select * from Address where Address.EmployeeId = Employee.Id)

And take the Id first employee we get... and that should give us a non empty set if used in the original sql sentence that we are trying to test... after that, we automatically generate:

Select * from Employee where not exists(select * from Address where Address.EmployeeId = Employee.Id)

And take the Id first employee we get... and that should give us an empty set if used in the original sql sentence that we are trying to test...

I call this queries "inverses" of the original one, it like when one is testing a multiplication, to see if 2 x 3 = 6, just do: 6/3 = 2 and 6/2 = 3, if 2, and 3 match the operands of the multiplication, you multiplication is right. The same thing goes for SQL, one just has to find the way to "invert" it, if I could automate this inversion, the automatically generated queries would help me by telling me things that might not be immediately obvious to me when I look at the original query, and that would help me check if my original query is right.... it would me some kind of "invariants" that would help me to better understand my querying... or maybe I could even write the invariants first, and then create a query and see if it matches my invariants...

Mmmm.... maybe using a select there is another way to "invert" a query to test if it is right, using the actual inverse operation of selecting... that is "inserting", could I derive from:

select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = 3

Something like (In pseudocode):

Insert Employee;

Store Employee.Id

Run select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = EmployeeId

Assert("The answer should be zero")

Insert Address related to Employee

Run select count(*) from Address,Employee where Address.Id= Employee.AddressId and Employee.Id = EmployeeId

Assert("The answer should be one")

This has the advantage that I don't need a database with data already on it, but it has the disadvantage that takes lot of time to write an unit test like this in java, because to insert an employee, it might be necessary to:

• Avoid breaking validation rules no related to this particular test, for example, an Employee must be related to a Department, but if the Department table is empty, then I should create a Department or I will not be able to insert an employee.
  
• Avoid conflicts with validation rules directly related to this particular test, for example, what if I have an Hibernate interceptor that won't let me insert an address without 1 or more Addresses

The main problem here, I believe, is that f I insert a row leaving a not null column empty most databases won't wait until I try to commit the transaction to say "integrity violation" and rollback my changes... therefore it is impossible to write partial data just for the test that I have in front of me, but... could I automatically generate consistent inserts using as a source just the integrity rules at the database level... and the select that I want to test?
I think it can be done... the question is..
What is the algorithm to generate the inserts needed to satisfy an SQL select statement?

Okey, things I miss about NHibernate

Lately I have been working without a formal object relational mapping (you know, using custom "hand made" business objects, and fetching data from the database using stored procedures)

Here are the things I miss from NHibernate:

• Automatic caching of objects by primary key
• Easy support for primary keys with multiple fields
• Polymorphic Queries (and all the benefits of persistent inheritance)
• Queries with "." navigation "object.Relationship.Relationship.Field = :parameter"
• Prefecthing relationships: "load Products with their to one relationship with Vendor preloaded"
• Easy pagination (instead of trying to figure out how to do it in combination with stored procedures for a particular version of a particular database (that can be a real nightmare))
• Interactive single and multicolumn sorting (try to do it with stored procedures, and of course without cheating by using the execute command, remember if you use execute all the so called "security benefits" of stored procedures vs dynamic sql dissapear)
• Concurrency support (automatical increment of a "version" field when saving changes, even if those changes are not just field changes, but relationship changes, custom exception type for concurrency errors, optimistic locking)
  
• Query by example (no need to concatenate strings by hand based on whether a field is null, no need to worry about upper case or lower case comparision when comparing each field, no need to update a query definition if a column is added or dropped from a table)

And, if somehow you have to work with a database that doesn't have support for stored procedures (and you can't use NHibernate)... things get even more "interesting":

• Alias for ugly table names and ugly field names (instead of "select * from PDT where PCKT = 1" you write "select * from Products where PackageType = 1"
• Named Parameters: Instead of "select * from PDT where PCKT = ? and RQTY = ?" you can write: "select * from Products where PackageType= :packageType and RemainingQuantity = :remainingQuantity" this is specially a nightmare when one has to use OleDb, and it gets worse with Inserts or Updates: "INSERT INTO PDT (NN,PCKT,RQTY,DRP,PRNBR) VALUES (?,?,?,?,?)" instead of the simple: "session.save(product)"

I will update this list as it increases...