[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2256) inner join created for not-null="false" many-to-one property

[Stephen M. Wick (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2256?page=all ]

Stephen M. Wick updated HHH-2256:
---------------------------------

    Attachment: hibernate_generated_sql.txt

> inner join created for not-null="false" many-to-one property
> ------------------------------------------------------------
>
>          Key: HHH-2256
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2256
>      Project: Hibernate3
>         Type: Bug

>   Components: core
>     Versions: 3.1.2
>  Environment: MS SQL Server Desktop Ed., Hibernate 3.1.2
>     Reporter: Stephen M. Wick
>  Attachments: Resource.hbm.xml, hibernate_generated_sql.txt
>
>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira