Hql where is null

Boiled down to its simplest form, we build a query like this:. RadiationExposedPerson "owns" the one-to-one association to a Person this makes sense in our case - keep in mind that this is a condensed version for the problem at hand. We were however not able to verify if this impacts the issue. The expected output of the query above would be to show all persons, in which the radiationExposedPerson is not null. However, this does not work, and returns all elements.

When the same query is modified to a "isNull" check, an empty result is returned. We verified that our database contains persons where radiationExposedPerson is null, and others where it is set. Another thing. If the null check is used as an expression for ordering, we run into an AST Exception from hibernate:. If this is indeed correct, I might need to investigate the hibernate side of things.

The text was updated successfully, but these errors were encountered:. Something I forgot - the generated SQL is - ofc - invalid. It null-check the auto id of Person, and not the radiationExposedPerson field. Sorry, something went wrong. I believe the first issue is caused by Hibernate, maybe the OneToOne can't be properly handled.

I really hope this is not an hibernate issue. We are using hibernate 4. X atm. So I'm unable to verify if this might be resolved in a later hibernate version. Will keep you posted.

I found whats causing the issue. At least for the first issue with the where part. Since the radiationExposedPerson field in the Person entity is a mapped field, a simple null check is not sufficient. The following JPQL yields the correct result:. Page 1 of 1. Previous topic Next topic. Post subject: HQL query for "x is null or x. In contrast, when I use the query "from Data as data where data.

Sadly, I cannot use some patch like creating an empty "info" class for every "data" to avoid the problem. Is there any way to write a single query in which the where clause is in the form "x is null or x.

Regards, Oz. Post subject: Posted: Wed Feb 11, pm. The above query assumes that there are table rows where c. The parameter name 'type' may be a reserved word in SQL but it shouldn't matter since it is replaced before the query runs. The javadoc for setParameter String, Object is explicit, saying that the Object value must be non-null.

It's a shame that it doesn't throw an exception if a null is passed in, though. An alternative is setParameter String, Object, Type , which does allow null values, although I'm not sure what Type parameter would be most appropriate here. Python Javascript Linux Cheat sheet Contact.