Fetch mode JOIN returns duplicate results

laxu · **Posted:** Tue Jun 06, 2006 7:44 pm

Hibernate version:
3.1.3
Mapping documents:

Code:

    <class name="Ihtiyac" table="ihtiyac">
        <id name="no" unsaved-value="-1" ><generator class="hilo" >
            <param name="max_lo">1</param>
            <param name="table">key_ihtiyac</param>
        </generator></id>
        <property name="adet"/>
        <many-to-one class="Durak" name="durak"/>
        <set name="karsilayanlar" table="ihtiyac_karsilayanlar" lazy="true">
            <key column="ihtiyac" not-null="true"/>
            <composite-element class="AracYol" node="aracyol">
                <many-to-one name="arac" column="arac"/>
                <many-to-one name="yol" column="yol"/>
            </composite-element>
        </set>
   </class>

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

Code:

            public Object doInHibernate(Session session) throws HibernateException,SQLException {
                return session.createCriteria(Ihtiyac.class)
                .setFetchMode("karsilayanlar",FetchMode.JOIN)
                .list();
            }

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Sybase Adaptive Server Anywhere 9.0.1

The generated SQL (show_sql=true):

Code:

select this_.no as no9_0_, this_.adet as adet9_0_, this_.durak as durak9_0_, karsilayan2_.ihtiyac as ihtiyac2_, karsilayan2_.arac as arac2_, karsilayan2_.yol as yol2_ from ihtiyac this_ left outer join ihtiyac_karsilayanlar karsilayan2_ on this_.no=karsilayan2_.ihtiyac

Debug level Hibernate log excerpt:

Hi,
I have the mapping and criteria query above. The generated SQL is ok, no exceptions etc. When I eager fetch the query as above, I receive duplicate results. I expect ro receive same number of results as I issued the query with FetchMode.DEFAULT (or w/o an explicit FetchMode). However, I receive as many results as the cartesian product of the two tables. I must be missing something.

damnhandy · **Posted:** Wed Jun 07, 2006 6:51 am

With FetchMode.JOIN and a one-to-many or many-to-many, you need to filter out the dupes. With a criteria query, you can do this via a result transformer:

Code:

public Collection doInHibernate(Session session) throws HibernateException,SQLException {
                return session.createCriteria(Ihtiyac.class)
                .setFetchMode("karsilayanlar",FetchMode.JOIN)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .list();
            }

Adding that one extra line in there will do the trick. HQL Queries support result transformers as of Hibernate 3.2. An alternatitive wold be passing your results to a new HashSet:

Code:

public Collection doInHibernate(Session session) throws HibernateException,SQLException {
         List results =  session.createCriteria(Ihtiyac.class)
                .setFetchMode("karsilayanlar",FetchMode.JOIN)
                .list();
         return new HashSet(results);
            }

Ryan-