Hello,

I am using Hibernate 3.2 CR2.

I have the following mapping:



I have the following query:



If any of person object's nested field is null, the query returns zero rows even though there are matching rows.

For example, if a person has father.mother assigned and has matching rows in the table but mother.father not assigned then query returns zero rows.

Just like in SQL you'll need to use left outer joins so that null associations don't cause the row to be dropped from the results, eg:-

Thank you for the answer.

If a person's mother == null the query does not fetch even though there are macthing rows from father's side.

Have you got your parentheses right? there seems to be one missing in your example, but do they match your logic properly?

Yes, it is right and works if there are no null references.

If any of the Person references is null, it does not return that row. Here is the complete method:

Can you post the generated SQL please?

Hibernate: select person0_.id as id1_0_, person1_.id as id1_1_, person3_.id as id1_2_, person4_.id as id1_3_, person2_.id as id1_4_, person5_.id as id1_5_, person6_.id as id1_6_, person0_.father_id as father12_1_0_, person0_.mother_id as mother13_1_0_, person0_.name as name1_0_, person0_.surname as surname1_0_, person0_.male as male1_0_, person0_.placeOfBirth as placeOfB5_1_0_, person0_.birthMonth as birthMonth1_0_, person0_.birthDay as birthDay1_0_, person0_.birthYear as birthYear1_0_, person0_.deathMonth as deathMonth1_0_, person0_.deathDay as deathDay1_0_, person0_.deathYear as deathYear1_0_, person1_.father_id as father12_1_1_, person1_.mother_id as mother13_1_1_, person1_.name as name1_1_, person1_.surname as surname1_1_, person1_.male as male1_1_, person1_.placeOfBirth as placeOfB5_1_1_, person1_.birthMonth as birthMonth1_1_, person1_.birthDay as birthDay1_1_, person1_.birthYear as birthYear1_1_, person1_.deathMonth as deathMonth1_1_, person1_.deathDay as deathDay1_1_, person1_.deathYear as deathYear1_1_, person3_.father_id as father12_1_2_, person3_.mother_id as mother13_1_2_, person3_.name as name1_2_, person3_.surname as surname1_2_, person3_.male as male1_2_, person3_.placeOfBirth as placeOfB5_1_2_, person3_.birthMonth as birthMonth1_2_, person3_.birthDay as birthDay1_2_, person3_.birthYear as birthYear1_2_, person3_.deathMonth as deathMonth1_2_, person3_.deathDay as deathDay1_2_, person3_.deathYear as deathYear1_2_, person4_.father_id as father12_1_3_, person4_.mother_id as mother13_1_3_, person4_.name as name1_3_, person4_.surname as surname1_3_, person4_.male as male1_3_, person4_.placeOfBirth as placeOfB5_1_3_, person4_.birthMonth as birthMonth1_3_, person4_.birthDay as birthDay1_3_, person4_.birthYear as birthYear1_3_, person4_.deathMonth as deathMonth1_3_, person4_.deathDay as deathDay1_3_, person4_.deathYear as deathYear1_3_, person2_.father_id as father12_1_4_, person2_.mother_id as mother13_1_4_, person2_.name as name1_4_, person2_.surname as surname1_4_, person2_.male as male1_4_, person2_.placeOfBirth as placeOfB5_1_4_, person2_.birthMonth as birthMonth1_4_, person2_.birthDay as birthDay1_4_, person2_.birthYear as birthYear1_4_, person2_.deathMonth as deathMonth1_4_, person2_.deathDay as deathDay1_4_, person2_.deathYear as deathYear1_4_, person5_.father_id as father12_1_5_, person5_.mother_id as mother13_1_5_, person5_.name as name1_5_, person5_.surname as surname1_5_, person5_.male as male1_5_, person5_.placeOfBirth as placeOfB5_1_5_, person5_.birthMonth as birthMonth1_5_, person5_.birthDay as birthDay1_5_, person5_.birthYear as birthYear1_5_, person5_.deathMonth as deathMonth1_5_, person5_.deathDay as deathDay1_5_, person5_.deathYear as deathYear1_5_, person6_.father_id as father12_1_6_, person6_.mother_id as mother13_1_6_, person6_.name as name1_6_, person6_.surname as surname1_6_, person6_.male as male1_6_, person6_.placeOfBirth as placeOfB5_1_6_, person6_.birthMonth as birthMonth1_6_, person6_.birthDay as birthDay1_6_, person6_.birthYear as birthYear1_6_, person6_.deathMonth as deathMonth1_6_, person6_.deathDay as deathDay1_6_, person6_.deathYear as deathYear1_6_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person3_ on person1_.father_id=person3_.id left outer join Person person4_ on person1_.mother_id=person4_.id left outer join Person person2_ on person0_.mother_id=person2_.id left outer join Person person5_ on person2_.father_id=person5_.id left outer join Person person6_ on person2_.mother_id=person6_.id where person1_.id<>9 and person2_.id<>3 and (person3_.id in (34 , 1) or person4_.id in (35 , 2) or person5_.id in (34 , 1) or person6_.id in (35 , 2)) order by person0_.birthYear, person0_.birthMonth, person0_.birthDay

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

Hibernate: select person0_.id as id1_2_, person0_.father_id as father12_1_2_, person0_.mother_id as mother13_1_2_, person0_.name as name1_2_, person0_.surname as surname1_2_, person0_.male as male1_2_, person0_.placeOfBirth as placeOfB5_1_2_, person0_.birthMonth as birthMonth1_2_, person0_.birthDay as birthDay1_2_, person0_.birthYear as birthYear1_2_, person0_.deathMonth as deathMonth1_2_, person0_.deathDay as deathDay1_2_, person0_.deathYear as deathYear1_2_, person1_.id as id1_0_, person1_.father_id as father12_1_0_, person1_.mother_id as mother13_1_0_, person1_.name as name1_0_, person1_.surname as surname1_0_, person1_.male as male1_0_, person1_.placeOfBirth as placeOfB5_1_0_, person1_.birthMonth as birthMonth1_0_, person1_.birthDay as birthDay1_0_, person1_.birthYear as birthYear1_0_, person1_.deathMonth as deathMonth1_0_, person1_.deathDay as deathDay1_0_, person1_.deathYear as deathYear1_0_, person2_.id as id1_1_, person2_.father_id as father12_1_1_, person2_.mother_id as mother13_1_1_, person2_.name as name1_1_, person2_.surname as surname1_1_, person2_.male as male1_1_, person2_.placeOfBirth as placeOfB5_1_1_, person2_.birthMonth as birthMonth1_1_, person2_.birthDay as birthDay1_1_, person2_.birthYear as birthYear1_1_, person2_.deathMonth as deathMonth1_1_, person2_.deathDay as deathDay1_1_, person2_.deathYear as deathYear1_1_ from Person person0_ left outer join Person person1_ on person0_.father_id=person1_.id left outer join Person person2_ on person1_.mother_id=person2_.id where person0_.id=?

FYI:

If no left joins are stated, hibernate automatically generates them.

Sorry - that SQL looks correct as far as I can see. I don't know what the problem is.

In the query above, person2_ == null

Ok I got it:

If I check for a null value for person.father it works:

(father is null or father.id != {0})

For person.father.father there is no need to check for a null value.

Thanks a lot.