Outer Fetch join returns duplicate parent objects

drj · **Posted:** Tue Dec 23, 2003 6:24 am

Hi,

I am trying to eagerly fetch an association (FetchChild) whilst selecting a parent object (FetchParent) using HQL as described in the reference manual section 10.3:

Quote:

from eg.Cat as cat
join cat.mate as mate
left join cat.kittens as kitten

My mapping files are: FetchParent

Code:

<hibernate-mapping>

   <class name="FetchParent" table="fetchParent">
   
      <id name="id" column="id" type="java.lang.Integer" unsaved-value="null">
         <generator class="native"/>
      </id>
            
      <bag name="children" lazy="false" inverse="false" outer-join="true">
         <key column="fetchParentId" />
         <one-to-many class="FetchChild" />
      </bag>
   
   </class>
   
</hibernate-mapping>

and FetchChild

Code:

<hibernate-mapping>

   <class name="FetchChild" table="fetchChild">
   
      <id name="id" column="id" type="java.lang.Integer" unsaved-value="null">
         <generator class="native"/>
      </id>
            
      <many-to-one name="parent" column="fetchParentId" class="FetchParent" not-null="true" outer-join="true"/>
   
   </class>
   
</hibernate-mapping>

The database structure is as follows:

Code:

CREATE TABLE fetchparent (
  id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=InnoDB;

CREATE TABLE fetchchild (
  id int(11) NOT NULL auto_increment,
  fetchParentId int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY fetchParentId (fetchParentId)
) TYPE=InnoDB;

alter table fetchChild add index (fetchParentId), add constraint FK10AD22828705A03F foreign key (fetchParentId) references fetchParent (id);


INSERT INTO fetchchild (id, fetchParentId) VALUES("1", "1");
INSERT INTO fetchchild (id, fetchParentId) VALUES("2", "2");
INSERT INTO fetchchild (id, fetchParentId) VALUES("3", "2");


INSERT INTO fetchparent (id) VALUES("1");
INSERT INTO fetchparent (id) VALUES("2");
INSERT INTO fetchparent (id) VALUES("3");

The code that I am executing is as follows:

Code:

import java.util.Iterator;
import java.util.List;

import junit.framework.TestCase;
import net.sf.hibernate.Session;
import net.sf.hibernate.SessionFactory;
import net.sf.hibernate.Transaction;
import net.sf.hibernate.cfg.Configuration;

public class FetchTest extends TestCase {

   public FetchTest() {
      super();
   }
   
   public void testOuterFetch () 
      throws Exception
   {
      SessionFactory sf =   new Configuration().configure().buildSessionFactory();
      Session session = sf.openSession();
      Transaction tx = null;
      try {
         tx = session.beginTransaction();
         
         List list = session.createQuery(
               "FROM FetchParent AS fp " +
               "LEFT JOIN FETCH fp.children"
            )
            .list();
            
         for (Iterator iter = list.iterator(); iter.hasNext();) {
            FetchParent parent = (FetchParent)iter.next();
            System.out.println(
               "Number of children for parent with id: " + 
               parent.getId() + " is: " +
               parent.getChildren().size()
            );
         }         
         
         tx.commit();
   
         } catch (Exception e) {
            if (tx != null)
               tx.rollback();
            throw e;
         } finally {
            session.close();
         }
   }

   public static void main(String[] args) {
      try {
         
         new FetchTest().testOuterFetch();
      }
      catch (Exception ex) {
         ex.printStackTrace();
         System.exit(-1);
      }
   }
}

with the following output

Code:

2003-12-23 12:15:03,675 INFO [net.sf.hibernate.cfg.Environment] - Hibernate 2.1.1
2003-12-23 12:15:03,695 INFO [net.sf.hibernate.cfg.Environment] - hibernate.properties not found
2003-12-23 12:15:03,705 INFO [net.sf.hibernate.cfg.Environment] - using CGLIB reflection optimizer
2003-12-23 12:15:03,715 INFO [net.sf.hibernate.cfg.Configuration] - configuring from resource: /hibernate.cfg.xml
2003-12-23 12:15:03,715 INFO [net.sf.hibernate.cfg.Configuration] - Configuration resource: /hibernate.cfg.xml
2003-12-23 12:15:04,786 INFO [net.sf.hibernate.cfg.Configuration] - Mapping resource: fetchParent.hbm.xml
2003-12-23 12:15:05,077 INFO [net.sf.hibernate.cfg.Binder] - Mapping class: FetchParent -> fetchParent
2003-12-23 12:15:05,307 INFO [net.sf.hibernate.cfg.Configuration] - Mapping resource: fetchChild.hbm.xml
2003-12-23 12:15:05,377 INFO [net.sf.hibernate.cfg.Binder] - Mapping class: FetchChild -> fetchChild
2003-12-23 12:15:05,397 INFO [net.sf.hibernate.cfg.Configuration] - Configured SessionFactory: null
2003-12-23 12:15:05,407 INFO [net.sf.hibernate.cfg.Configuration] - processing one-to-many association mappings
2003-12-23 12:15:05,407 INFO [net.sf.hibernate.cfg.Binder] - Mapping collection: FetchParent.children -> fetchChild
2003-12-23 12:15:05,407 INFO [net.sf.hibernate.cfg.Configuration] - processing one-to-one association property references
2003-12-23 12:15:05,407 INFO [net.sf.hibernate.cfg.Configuration] - processing foreign key constraints
2003-12-23 12:15:05,467 INFO [net.sf.hibernate.dialect.Dialect] - Using dialect: net.sf.hibernate.dialect.MySQLDialect
2003-12-23 12:15:05,467 INFO [net.sf.hibernate.cfg.SettingsFactory] - Use outer join fetching: true
2003-12-23 12:15:05,477 INFO [net.sf.hibernate.connection.DriverManagerConnectionProvider] - Using Hibernate built-in connection pool (not for production use!)
2003-12-23 12:15:05,477 INFO [net.sf.hibernate.connection.DriverManagerConnectionProvider] - Hibernate connection pool size: 20
2003-12-23 12:15:05,517 INFO [net.sf.hibernate.connection.DriverManagerConnectionProvider] - using driver: org.gjt.mm.mysql.Driver at URL: jdbc:mysql://localhost:3306/employment?autoReconnect=true
2003-12-23 12:15:05,517 INFO [net.sf.hibernate.connection.DriverManagerConnectionProvider] - connection properties: {user=employment_user, password=employment}
2003-12-23 12:15:05,527 INFO [net.sf.hibernate.transaction.TransactionManagerLookupFactory] - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
2003-12-23 12:15:05,778 INFO [net.sf.hibernate.cfg.SettingsFactory] - Use scrollable result sets: true
2003-12-23 12:15:05,778 INFO [net.sf.hibernate.cfg.SettingsFactory] - JDBC 2 max batch size: 15
2003-12-23 12:15:05,788 INFO [net.sf.hibernate.cfg.SettingsFactory] - echoing all SQL to stdout
2003-12-23 12:15:05,788 INFO [net.sf.hibernate.cfg.SettingsFactory] - Query language substitutions: {}
2003-12-23 12:15:05,788 INFO [net.sf.hibernate.cfg.SettingsFactory] - cache provider: net.sf.ehcache.hibernate.Provider
2003-12-23 12:15:05,798 INFO [net.sf.hibernate.cfg.Configuration] - instantiating and configuring caches
2003-12-23 12:15:06,078 INFO [net.sf.hibernate.impl.SessionFactoryImpl] - building session factory
2003-12-23 12:15:06,859 INFO [net.sf.hibernate.impl.SessionFactoryObjectFactory] - no JNDI name configured
Hibernate: select fetchparen0_.id as id0_, children1_.id as id1_, children1_.fetchParentId as fetchPar2_1_, children1_.id as id__, children1_.fetchParentId as fetchPar2___ from fetchParent fetchparen0_ left outer join fetchChild children1_ on fetchparen0_.id=children1_.fetchParentId
Number of children for parent with id: 1 is: 1
Number of children for parent with id: 2 is: 2
Number of children for parent with id: 2 is: 2
Number of children for parent with id: 3 is: 0

The problem I am having is that the results contain duplicate parent objects which means I have to manually go through the list and toss them away. Is this expected behaviour?

Other options that I have tried are:
1) Iterate through the results and

Code:

Hibernate.initialise()

the collections, but I would need to do this for every parent object retrieved, which would equate to a new query per parent object

2) Marking the collection as lazy="false" and outer-join="true", with <property name="use_outer_join">true</property> in my hibernate.cg.xml file - which I assumed would result in the collection being loaded using an outer join when I referenced/loaded the parent object - which did not happen - the collection WAS loaded but not using an outer join.

Is there any way that I can eagerly initialise collections of a parent objects without getting duplicates/hitting the DB for every parent object?

emmanuel · **Posted:** Tue Dec 23, 2003 11:21 am

Quote:

s this expected behaviour?

Yes, like in your SQL.

emmanuel · **Posted:** Tue Dec 23, 2003 11:30 am

Quote:

Is there any way that I can eagerly initialise collections of a parent objects without getting duplicates/hitting the DB for every parent object?

No you have to distinct them in your java code

drj · **Posted:** Sat Dec 27, 2003 7:24 am

Thanks Emmanuel

I understand why it is happening from a SQL perspective.

But wouldn't you agree with me that it would be quite a cool feature to be able to initialise a collection of a child object(s) without incurring the cost/risk of having duplicates parent objects in the returned list.

When I think about the query:

Code:

List list = session.createQuery(
               "FROM FetchParent AS fp " +
               "LEFT JOIN FETCH fp.children"

which translates roughly to:

Give me a list of all the parent objects (even if they don't have children) and initialise their child collections.

... the fact that we are getting duplicates implies that somewhere along the line the cartesion product of SQL has bubbled up and polluted the OO paradigm.

Justin

max · **Posted:** Sat Dec 27, 2003 9:40 am

It's not pollution - it's intensional as that's how the semantics are for joins in the relational algebra (or is it just in sql ?, cant remember)

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

I found that this worked quite well, although it definitly confused me as well at first. At least the filtering was quite painless!

Code:

            List list =
                ServiceLocator.currentSession().find(
                    "select r.replicate from Reaction r left outer join fetch r.replicate.reactions where r.replicate.reworkF=false and r.workorder.id=?",
                    new Long(workorder.getId()),
                    Hibernate.LONG);

            Set uniqueSet = new HashSet();
            uniqueSet.addAll(list);
            return Arrays.asList(uniqueSet.toArray());

pdg@jcs.be · **Joined:** Wed Apr 14, 2004 4:45 am **Posts:** 1

The solution with the HashSet is works well in your case but in my case it is useless.
I want to setMaxResults() and setFirstResult() to prevent that my result populates all the fetched objects (a large list). My opinion is that there must be a way to get Unigue parentObjects ...

michael · **Posted:** Mon Aug 09, 2004 8:43 am

Probably use a subselect. Would be interesting to see how you do that in SQL, producing distinct "parent" rows when using a join.

dep4b · **Joined:** Sat Dec 06, 2003 5:38 pm **Posts:** 27

You may also want to move some of this logic into creating a VIEW. Than map a custom hibernate object to your view, or just do a a join against the view. Of course, your "milage may vary".

wdemoss · **Joined:** Tue Oct 07, 2003 2:56 pm **Posts:** 13

Couldn't you just use distinct?

Code:

List list = ServiceLocator.currentSession().find( 
                    "select distinct r.replicate from Reaction r 
                     left outer join fetch r.replicate.reactions 
                     where r.replicate.reworkF=false 
                     and r.workorder.id=?", 
                    new Long(workorder.getId()), 
                    Hibernate.LONG); 

michael · **Posted:** Wed Aug 11, 2004 2:26 am

No, the reactions have still to be returned from the SQL query, so the rows are "distinct" even though the parent objects are duplicated.