HQL Selection through <many-to-one> causes wrong join

ftudone · **Joined:** Tue Jul 12, 2005 5:41 am **Posts:** 7

Hello,

I'm querying with HQL and selecting one field from a Source class and another from an instance of a Target class belonging to a <many-to-one> in Source called "t". Since the target could be null (and I'd like the Source rows come back anyway), I would expect an SQL outer join when this query gets translated, but I get an inner one, so I don't get back the rows that have the foreign key == null. I'm using a non-lazy "join" fetch strategy.

Hibernate version: 3.1.3

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="hibtest.Source">
      <id name="key">
         <generator class="uuid"/>
      </id>
      <property name="i"/>
      <many-to-one name="t" class="hibtest.Target" fetch="join" lazy="false"/>
   </class>
   <class name="hibtest.Target">
      <id name="key">
         <generator class="uuid"/>
      </id>
      <property name="s"/>
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

            Session s = sf.getCurrentSession();
            Transaction t = s.beginTransaction();
            Query q = s.createQuery("select s.i, s.t.s from hibtest.Source s");
            List l = q.list();
            t.commit();
            System.out.println(l.toString());

Name and version of the database you are using: MySQL 5

The generated SQL (show_sql=true):

Code:

select source0_.i as col_0_0_, target1_.s as col_1_0_ from Source source0_, Target target1_ where source0_.t=target1_.key

Could you help me here please? If you need the java classes or other info please just ask.

TIA

kgignatyev · **Posted:** Sun Apr 02, 2006 8:49 pm

Could you try the HQL

select s.i, targ.s
from hibtest.Source s left jojn s.t targ

ftudone · **Joined:** Tue Jul 12, 2005 5:41 am **Posts:** 7

Thank you very much fo your reply.

Yes it works, but it is not clear to me why it shouldn't work the other way around... Pls consider that, since I am _generating_ code starting from a model to use Hibernate, it is very difficult for me to use this kind of query (it would be another particular case in the generator).

Do you have any clue about why it is not working without the explicit outer join? Reading the manual/docs/FAQs/forums didn't help me so far.

TIA.