Hi.
I'm trying to write an hql query that does something like this:
"from Data as data where (data.info is null or data.info.startDate > :timestamp)"

I am working with HSQLDB.
The problem is that the result ignores the cases in which data.info IS null.

I've noticed that the HQL is translated to SQL like this:


And it seems that the "data0_.INFO_ID=info1_.INFO_ID" part causes the problem.
In contrast, when I use the query
"from Data as data where (data.info is null)"
I get the following sql:

Which does retrieve the null objects.

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.y = value" (or some workaround for the problem)?


Regards,
Oz

You need to specify an explicit (left) join. Something like the following should work:

Thanks a lot nordborg, that solved the problem.

One small change though, I added a 'fetch' so that the query returns a list of Data objects (as before) and not a list of multiple objects (Data and Info):
"from Data as data left join fetch data.info as info where (info is null or info.startDate > :timestamp)"

Regards,
Oz