[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2688) HQL: Incorrect join when ordering by property of nullable many-to-one

[Scott Van Wart (JIRA)]

HQL: Incorrect join when ordering by property of nullable many-to-one
---------------------------------------------------------------------

                 Key: HHH-2688
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2688
             Project: Hibernate3
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.2.4.sp1
         Environment: Oracle 10gR2
            Reporter: Scott Van Wart
         Attachments: pojos_and_mappings.zip

I have two tables: Parent and Child.  Children may not have a parent.  I want to select all children, ordering by parent name:

TABLE parent_table ( parent_id NUMBER PRIMARY KEY, parent_name VARCHAR2(30) NOT NULL );
TABLE child_table ( child_id NUMBER PRIMARY KEY, parent_id REFERENCES parent ON DELETE SET NULL );

So I create the two POJOs and mapping documents (attached).  After openSession():

List children = session.createQuery( "from Child c order by c.parent.name" ).list();

I only get back the children with parents, because here's the generated SQL, with an INNER join:

select child0_.child_id as child1_17_, child0_.parent_id as parent2_17_
   from child_table child0_, parent_table parent1_
   where child0_.parent_id=parent1_.parent_id
   order by parent1_.parent_name;

Because parent_id in the child table is nullable, I would expect the generated SQL to be:

select child_id, parent_id
   from child_table c LEFT JOIN parent_table p ON c.parent_id = p.parent_id
   order by p.parent_name;

I guess a workaround might be to specify the join type explicitly in the HQL, but this default behavior kind of surprised me.  Thanks.

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