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

[Matt Sgarlata (JIRA)]

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