HQL Bug and nullable fields.

Question

Why does this query NOT work

FROM WorkflowConfiguration
WHERE ((saReplacement IS NOT NULL) AND (:currentTime >= saReplacement.start) AND (saReplacement.end >= :currentTime)) OR
              ((hrmsAdminReplacement IS NOT NULL) AND (:currentTime >= hrmsAdminReplacement.start) AND (hrmsAdminReplacement.end >= :currentTime)) OR
              ((hrManagerReplacement IS NOT NULL) AND (:currentTime >= hrManagerReplacement.start) AND (hrManagerReplacement.end >= :currentTime)) OR
              ((payrollAdminReplacement IS NOT NULL) AND (:currentTime >= payrollAdminReplacement.start) AND (payrollAdminReplacement.end >= :currentTime))

But this one does

FROM WorkflowConfiguration
WHERE (saReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
              (hrmsAdminReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
              (hrManagerReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime))) OR
              (payrollAdminReplacement IN (from WorkflowReplacement as replacement WHERE (:currentTime >= replacement.start) AND (replacement.end >= :currentTime)))

The reason why its so long is the saReplacement, hrmsAdminReplacement, hrManagerReplacement and payrollAdminReplacement can all be null, so I put in a check to ensure they are not null so the rest of the clause does not get executed. But for some reason, when I run the first query, I don't get ANY results, but if I run the second one (MUCH LESS EFFICIENT) I get all the expected results. Is this a bug in Hibernate's HQL or am I losing my mind?

Answer 1

Have you tried something like this:

FROM WorkflowConfiguration wfc
LEFT JOIN FETCH wft.saReplacement a
LEFT JOIN FETCH wft.hrmsAdminReplacement b
LEFT JOIN FETCH wft.hrManagerReplacement c
LEFT JOIN FETCH wft.payrollAdminReplacement d
WHERE
((:currentTime >= a.start) AND (a.end >= :currentTime)) or
((:currentTime >= b.start) AND (b.end >= :currentTime)) or
((:currentTime >= c.start) AND (c.end >= :currentTime)) or
((:currentTime >= d.start) AND (d.end >= :currentTime))