I am experiencing some problems when using "ORDER BY" clauses (in Hibernate queries)
in combination with (optional) many-to-one associations.


Example:
========

If have a many-to-one between "SellOffer" and "Structure"
This assocation is optional (-> "id_structure" may be null)



I have 18 records in SellOffer, only 6 of them are linked to a structure


CASE ONE
========

When running the HQL:


I do receive my 18 records (hopefully ;-)


CASE TWO:
=========

I want the same data, but this time sorted by the structure name:


=> I only receive 6 records !!!
(actually the ones having a non-null structure)

I have examined the SQL statement generated by Hibernate:


And I do not understand why Hibernate is not using an outer-join (+)
(BTW the mapping attribute is named "outer-join") such as:

which returns the expected results.


Question:
=========

Is it a bug, or (more probable ;-) do I miss something ?

What should I change (in my mapping, in my query, ....) in order to
get my 18 records sorted by structure name ?

Thx in advance,

where on earth did you read in the documentation that an implicit join is translated to an outer join?

If you want an outer join, write down an outer join.

Thanks a lot Gavin.
You are right, it is written nowhere :-$

But this leads me to the following suggestion:
I was wondering if, with the help of the not-null attribute in my <many-to-one> mapping, Hibernate could not smartly utilize such information in order to decide how to translate the implicit join:
- if not-null="true" => translated into a normal join
- if not-null="false" => translated into an outer join

What do you think about it ?

And break existing code?

No way.

I actually think that inner join is a very reasonable and defensible semantic here.

AFAIK, the documentation (reference manual and HiA) are both silent on this. That is, they don't say how an HQL implicit join gets translated into a SQL join. Since it seems that HQL implicit joins always get translated into SQL inner joins, it would be helpful if the docs actually said "HQL implicit joins always get translated into SQL inner joins".

There is in fact a discussion of this in the docs in CVS (which made it into 3.1rc2 and maybe even 3.1rc1)...

Cool, yes, I see it there. Thanks!