I have a SQL Server table containing hierarchial categories, TipCategories. It has an Id field and a ParentId field which may be null. There is also an int field named DisplayRank that is used to affect the sort of categories for display. So the abridged table schema looks like:



The mapping is:



I create the following query:


And pass null in as a parameters, qualifying it as a TipCategory type. NHibernate produces the following SQL:


It binds null to @p0, and the result of this query is NULL. However, if it had been using the "is null" clause, I would get the numeric result I expected.

Is this a bug or am I doing something wrong?

-mt

Hibernate isn't going to edit your queries for you. Change your query:

Hmm... so much for abstracting away from SQL. Perhaps I am missing something fundamental. Why was the "is null" clause carried over from SQL into HQL (i.e. what about it felt different from "= null" to warrant additional syntax)?

-mt

Saga continues. I took tenwit's avice and modified my query, however, NH is doing something REALLY odd now.

The SQL it generates is:


So I'm not sure why it converted one variable used three times into three variables (don't we lose some efficiency here?!?), but the worst part is not that.

The worst part is that while NH correctly understood that it should be comparing on ParentId, in reality it tries to bind the byte array serialized representation of the entire object to each of the three sql query parameters.

Now clearly I can rewrite the HQL query to explicitly use the Id field, but having to do that would be a sign of another leaky abstraction.

So is this me or NH? Is something wrong with my mapping?

-mt

No, that's all correct. Hibernate will bind the same java variable to the three different SQL variables. That's just how SQL works, it's not an issue with hibernate.

Hibernate can't do much with the "is null" thing. I suppose that it could expand out your original short version into the longer multi-check thing, but that will fall over if the thing you're comparing to has side effects (e.g. is a subselect into a temporary table).

Ultimately, the problem boils down the fact that null isn't a "value" in SQL, it's a state, meaning "unset". Java just doesn't have that concept (thankfully: it's hopeless outmoded and really doesn't belong even in SQL, imo). In SQL, null = null is false: nothing equals null, not even null. That's why there's a separate "is null" expression.