Hibernate HQL strange behavior with IS NULL

Question

I have a problem with a HQL query. I want to get all PID that has an administrative sex set to 'M' or no administrative sex (in Java the value is set to null).

PID.class

@Entity
@Table(name = "PatientIdentification")
public class PID {    

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "administrativeSex", referencedColumnName = "is_id")
    private IS administrativeSex;
    ...
}

IS.class

@Entity
@Table(name = "CodedValueForUserDefinedTables")
public class IS {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "is_id")
    private Integer id;

    private String value;
    ...
}

The HQL query

from PID where administrativeSex is null or administrativeSex.value = 'M'

The produced SQL

select
  pid0_.pid_id as pid1_84_,
  pid0_.administrativeSex as adminis11_84_,
  pid0_.birthOrder as birthOrder84_,
  pid0_.birthPlace as birthPlace84_,
  pid0_.citizenship as citizen12_84_,
  pid0_.countyCode as countyCode84_,
  pid0_.dateTimeOfBirth as dateTim19_84_,
  pid0_.driverLicenseNumber as driverL22_84_,
  pid0_.maritalStatus as maritalS8_84_,
  pid0_.multipleBirthIndicator as multiple4_84_,
  pid0_.nationality as nationa17_84_,
  pid0_.owner_id as owner9_84_,
  pid0_.patientAccountNumber as patientA6_84_,
  pid0_.patientDeathDateAndTime as patient16_84_,
  pid0_.patientDeathIndicator as patient18_84_,
  pid0_.patientId as patientId84_,
  pid0_.primaryLanguage as primaryL7_84_,
  pid0_.race as race84_,
  pid0_.religion as religion84_,
  pid0_.setId as setId84_,
  pid0_.ssnNumber as ssnNumber84_,
  pid0_.veteransMilitaryStatus as veterans2_84_
 from
  PatientIdentification pid0_,
  CodedValueForUserDefinedTables is1_
 where
  pid0_.administrativeSex=is1_.is_id
  and (
   pid0_.administrativeSex is null
   or is1_.value='M'
  )

The query only returns the PID that has the administrative sex set to 'M'. It's missing the one who does not have an administrative sex. It's normal if you look at the SQL query. How do I correct my HQL query?

Answer 1

It looks like you're doing the equivalent of an inner join.

from
 PatientIdentification pid0_,
 CodedValueForUserDefinedTables is1_
where
 pid0_.administrativeSex=is1_.is_id

isn't going to return any values where the administrativeSex is null unless you have an entry in CodedValueForUserDefinedTables where is_id is also null.

I find the OneToOne mapping to be tricky to work with. Have you considered trying to map the string value as a field in the top level class by using a @SecondaryTable annotation?

Answer 2

The hibernate reference manual writes:

HQL supports two forms of association joining: implicit and explicit.

The queries shown in the previous section all use the explicit form, that is, where the join keyword is explicitly used in the from clause. This is the recommended form.

The implicit form does not use the join keyword. Instead, the associations are "dereferenced" using dot-notation. implicit joins can appear in any of the HQL clauses. implicit join result in inner joins in the resulting SQL statement.

Since an inner join filters out those rows where the join condition doesn't match, you are missing the people of unknown gender. You will want a left outer join, which you have to request explicity:

from PID pid
left outer join pid.administrativeSex as sex 
where pid.administrativeSex is null or sex.value = 'M'

Answer 3

Have you considered using an outer join directly ?

from PID p left join p.administrativeSex with value = 'M'

Hope that helps.