inner join created for not-null="false" many-to-one property

Description

Hibernate generates an inner join when a nullable many-to-one property is specified in the ORDER BY clause of an HQL statement.

If the property specified in the ORDER BY statement is nullable, then Hibernate should generate an OUTER JOIN to allow nulls in the result set. Instead, for whatever reason, Hibernate put's the property's table in the FROM clause, and JOINS the records with the parent table using a WHERE condition, which is basically an INNER JOIN. By performing the join this way, records with null values in the property being sorted upon are excluded from the query result set when they shouldn't be.

I also tried adding hibernate-mapping/class/many-to-one/@outer-join="true", and Hibernate generated the same SQL (inner join).

Example HQL: from Staff order by state.name

state is a many-to-one not-null="false" property of Staff.

Here's the SQL I expected to be generated:

select
...
from
STAFF staff0_
left outer join STATE state1_ on staff0_.STATE_ID = state1_.STATE_ID
order by
state1_.STATE_NAME asc

Here's the SQL that Hibernate generated:

select
...
from
STAFF staff0_,
State state1_
where
staff0_.STATE_ID = state1_.STATE_ID
order by
state1_.STATE_NAME asc

Environment

MS SQL Server Desktop Ed., Hibernate 3.1.2

Activity

Show:
Steve Ebersole
November 21, 2006, 11:15 PM

As clearly outlined in the documentation, use of implicit joins unequivocally resolves to an inner join.

Use the explicit syntax and specify an outer join.

Steve Ebersole
November 21, 2006, 11:15 PM

grrr

Rejected

Assignee

Unassigned

Reporter

Stephen M. Wick

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure