When using a null value in a (named) parameter query, the generated SQL uses the equals (=) operator instead of the "is" operator.

E.g.
Query q = ... "from entity where column = :possibleNullValue";
q.setParameter("possibleNullValue", null);

will generate this SQL:
"select * from entity where column = null"

On most databases, this will work. However, this is a violation of the ANSI92 SQL specification, which says that a comparison with a null value using the equals operator always gives "false". You should always use the "is" operator when comparing with a null value.

As said, most database are more relaxed, and have similar behaviour for both operators, or allow you to configure the database to be more relaxed.

Unfortunately, the HSQLDB database is strict about this, and does not allow the equals operator. Hence, the above JPAQL will not give any results, even if there are records with a null value.

Does anybody know if there is a fix or workaround for this issue?


Regards,
Tom

My first version of this post just suggested an OR clause, but I see you have a problem with generated code. Have you thought of having a named query to search for NULL, and then using an in the client code.

Yes, I have thought of this, and it works fine.

But using a different query in case of null values is only a workaround for this problem.

Hibernate should simply do this for me, and generate the correct SQL.

By the way, when you *don't* use parameter binding, Hibernate actually generates correct SQL:


is converted to SQL

So it seems that without parameter binding, hibernate correctly generates ANSI92 SQL, but with parameter binding, it doesn't.

Is this Hibernate translating an HQL query of yours, or is this SQL from auto-generated UPDATE, etc.? If you are writing the query, Hibernate would have no way of knowing which behavior you prefer, NULL matching or not matching. In your own query, you can use WHERE COALESCE(:param, value)=value to get TRUEon NULLs.

I would recommend using the Criteria API. It's much cleaner when it comes to creating dynamic queries. To add null criteria all you have to do is call criteria.add(Restrictions.isNull("propertyName")).

@drlaz

It is a HQL query I wrote myself. I don't think I understand what you mean by "Hibernate would have no way of knowing which behavior you prefer, NULL matching or not matching".

If anyone writes a piece of HQL containing "where column = :value", where value is null, I'm pretty sure that you always want to match the rows where column has value null (or has no value, to be precise). I don't think anyone would ever want this to evaluate to false; that doesn't make sense. That would be the same as including e.g. "where 1=2".

Hibernate should never convert HQL to SQL containing "column = null".

I don't thing the coalesce example you give is appropriate for this situation: I'm not comparing to a value, but to the parameter. But writing it as e.g. "where column = coalesce(:param, null)" also doesn't work for ANSI-92 compliant databases, since this also evaluates to "where column = null" when param is null.

@sowelie

Using Restrictions.isNull("propertyName") will probably work fine. However the point is that I want to add a restriction on the property, with a variable value. This value *can* be null.

I really don't want to check if the value is null or not, and add different restrictions for each scenario. I just want to pass in my variable, and let Hibernate create the proper restriction.

Thanks for thinking with me anyway.

I'm not sure I agree here. Sure, sometimes you want to pull in all the NULLs, e.g., unrealized stock transactions (in my app, they have a null close date). But I'm sure I could think of another domain where it makes the most sense to disregard all rows with that field NULL.

Anyway, since you are writing the code, you can just add an OR clause In some native SQL dialects there is an IS [NOT] DISTINCT FROM comparator but I don't see any mention of that in HQL. I thought this might be auto-generated CRUD statements and then I am not quite sure what to do.

I would love to see an example that makes sens to disregard all rows where the queried property is null, and being explicitly asked to be null:

"from entity where property = :parameter"

where the value of the parameter is set to null by the caller of the query. If your statement is true (and there are some entities in the database with property = null), it means that the caller doesn't want to see any rows. In this case, he should not have submitted the query at all.

On top of that, in reality the behaviour of using = for null comparisons is depending on the database being used. So even if it would make sense not to expect results for "= null" queries, it wouldn't work for databases that treat "= null" similar to "is null". For instance, Sybase will happily return all rows with property value null for the query above, and HSQLDB won't.

I'm surprised that is Sybase's default behavior. The Postgres documentation implies that MS Access is the only major DBMS to do so, and they provide a runtime switch to emulate that behavior for porting purposes.

I can't think of an example where I want value = :param to return rows where the param is a user-supplied NULL, but I do have queries of the form WHERE a1.field = a2.field where I don't want any rows with NULLs. Sure, I can add the clause a1.field IS NOT NULL; on the other hand, you can add the OR clause.

hth

I'm getting the impression that we're talking about slightly different scenarios ;)

You're saying


But earlier, you gave a fine example:


To me, this is quite a common scenario. In your app, I can imagine your users can query for stock transactions, and you let them specify a close date. They can fill one in, or leave it blank. I would love to have one query that handles both use cases.

Maybe you're talking about a different scenario, because I don't exactly understand what you mean in the first quote above with "value = :param": what is "value" in your case? In the problem I describe, "value" is actally a property of an entity (or column in a table). I'm a bit confused by your usage of the word value. Maybe you mean something else than I do?

I'm sorry, I totally mis-wrote that. I meant that I could see a case where I wanted value=:param to return true on NULL=NULL. The problem, I suspect, is the general case where one side isn't a user-supplied parameter, but a similar field in another table. In that case, it's more likely that you want all the NULLs excluded.

You can use the OR clause to get everything in one query. It's just writing out the meaning of IS NOT DISTINCT FROM the long way.

tomvandenberge:

You're totally right. When I first ran into this issue I thought the same thing. It would be very easy for Hibernate devs to make this work. And I cannot think of any reason why you would pass in the parameter as null other than to explicitly ask for rows where the column is null.

Have you submitted an issue for this in JIRA?