NULL handling with subselect in Hibernate Criteria API

Question

I'm constructing a Hibernate Criterion, using a subselect as follows

DetachedCriteria subselect =
    DetachedCriteria.forClass(NhmCode.class, "sub"); // the subselect selecting the maximum 'validFrom'
subselect.add(Restrictions.le("validFrom", new Date())); // it should be in the past (null needs handling here)
subselect.add(Property.forName("sub.lifeCycle").eqProperty("this.id")); // join to owning entity
subselect.setProjection(Projections.max("validFrom"));  // we are only interested in the maximum validFrom

Conjunction resultCriterion = Restrictions.conjunction();
resultCriterion.add(Restrictions.ilike(property, value)); // I have other Restrictions as well
resultCriterion.add(Property.forName("validFrom").eq(subselect)); // this fails when validFrom and the subselect return NULL

return resultCriterion;

It works ok so far, but the restriction on the last line before the return statement is false when validFrom and subselect result in NULL.

What I need is a version which handles this case as true. Possibly by applying a NVL or coalesce or similar.

How do I do this?

Update: ----------------------------

Péters idea with the sqlRestriction results in a where clause like this:

        ...
        and (
            nhmcode1_.valid_from = (
                select
                    max(sub_.valid_from) as y0_ 
                from
                    nhm_code sub_ 
                where
                    sub_.valid_from<=? 
                    and sub_.lc_id=this_.id
            ) 
            or (
                nhmcode1_.valid_from is null 
                and sub.validFrom is null
            )
        )
        ...

which in turn result in:

ORA-00904: "SUB_"."VALIDFROM": ungültiger Bezeichner

the error message meaning 'invalid identifier'

Answer 1

You could try something like this instead of the problematic line:

resultCriterion.add(
  Restrictions.or(
    Restrictions.and(
      Restrictions.isNull("validFrom"),
      Restrictions.sqlRestriction("sub.validFrom is null")
    ),
    Property.forName("validFrom").eq(subselect)
  )
);

This may not work straight away, but hopefully helps.

Answer 2

It really looks like this is one more limitation of the Criteria API. :-(