[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-2111) HQL: Traversal of many-to-one relationship incorrectly results in inner join

[Emmanuel Bernard (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2111?page=all ]
     
Emmanuel Bernard resolved HHH-2111:
-----------------------------------

    Resolution: Rejected

this is the expected result.
Use an explicit left outer join.

> HQL: Traversal of many-to-one relationship incorrectly results in inner join
> ----------------------------------------------------------------------------
>
>          Key: HHH-2111
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2111
>      Project: Hibernate3
>         Type: Bug

>   Components: query-hql
>     Versions: 3.1.3
>     Reporter: Matt Sgarlata
>  Attachments: ScorecardNodeHDao.hbm.xml
>
>
> Here is my HQL query:
> select
>     count(*) 
> from
>     com.spider.cms.hdao.IScorecardNode sn 
> where
>     sn.nodeType in (0, 1, 2, 3) or
>     (
>         sn.nodeType = 5 and
>         sn.sourceNode.nodeType between 0 and 3     
>     )
> This is resulting in this SQL
> select
>     count(*) as col_0_0_ 
> from
>     scorecardnodes scorecardn0_,
>     scorecardnodes scorecardn1_ 
> where
>     scorecardn0_.sourcenodeid=scorecardn1_.scorecardnodeid 
>     and (
>         scorecardn0_.nodetype in (
>             0 , 1 , 2 , 3
>         ) 
>         or (scorecardn0_.nodetype=5 
>         and (
>             scorecardn1_.nodetype between 0 and 3
>         ))
>     )
> The inner join in the SQL query is causing fewer result to be returned than expected.  The first part of the where clause in the HQL query makes no mention of the sourceNode property, and so the returned results should include all scorecard nodes with the requested node type, regardless of whether or not they have an associated source node.  The inner join is causing only rows to be returned if they have a source node associated with them.  It's as if the query was written like this:
> select
>     count(*) 
> from
>     com.spider.cms.hdao.IScorecardNode sn 
> where
>     (
>         sn.sourceNode is not null and
>         sn.nodeType in (0, 1, 2, 3)
>     ) or
>     (
>         sn.nodeType = 5 and
>         sn.sourceNode.nodeType between 0 and 3     
>     )
> Attached is the relevant mapping information.

-- 
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