join fetch query returns redundant objects

chsell · **Joined:** Wed Feb 25, 2004 10:46 am **Posts:** 19

Hibernate version: 3.1.2
Name and version of the database you are using: Oracle 9

Hello,

I have 2 classes A and B related through a bidirectional on-many relationship. A contains a Set mapping with inverse="true" referencing B, B contains a many-to-one mapping referencing A.

Now, when I do a query

Code:

select a from A a join fetch a.bs where a.name = 'test'

I get the As with the bs relationship resolved as expected.

However, when I do something like

Code:

select a from A a join fetch a.bs where exists (select b2 from B b2 where b2.a = a and b2.number = 22)

I get a number of A instances that corresponds to the expected number multiplied by the number of related B instances, i.e. the rows returned by the underlying SQL are not resolved to unique A instances, making the query useless.

can somebody comment on this?
thanks,
Christian[/quote]

pepelnm · **Posted:** Wed Aug 02, 2006 4:14 am

try with:

Code:

select distinct a from A a join fetch a.bs where exists (select b2 from B b2 where b2.a = a and b2.number = 22)

chsell · **Joined:** Wed Feb 25, 2004 10:46 am **Posts:** 19

firstly, in my understanding, hibernate should deal with the redundant rows, as it does when I ussue the simple join fetch without the extra exists clause.

secondly, as it is so obvious, I have tried adding the distinct before posting here. It turns out that the way the SQL is generated, the distinct clause is applied to the whole row, which contains the columns from A and B, and therefore is always different. The distinct clause does not help

damnhandy · **Posted:** Wed Aug 02, 2006 10:37 am

Actually, what you need to do when using a a fetch join is pass the result List into a Set to normalize the results.

Code:

Query query = session.createQuery("select a from A a join fetch a.bs where exists (select b2 from B b2 where b2.a = a and b2.number = 22");

List results = query.list();
return new ArrayList(new HashSet(results)));

Criteria queries handle this better as you can use the DistinctRootEntityTransformer to achive the same thing. Hibernate 3.2 adds the use of ResultTransformers on HQL queries. Hope this helps.

Ryan-

chsell · **Joined:** Wed Feb 25, 2004 10:46 am **Posts:** 19

Quote:

Actually, what you need to do when using a a fetch join is pass the result List into a Set to normalize the results

whoa, that leaves me a little shocked. I was firmly assuming that hibernate was doing this for me (and I stated that in the initial post).

I guess this is not the place for that kind of discussion, but I would really like to know what reasoning lies behind instantiating scores (or more!) of objects only to thereafter "normalize" them away except for one. It seems to me there is quite an overhead involved here.

Christian

chsell · **Joined:** Wed Feb 25, 2004 10:46 am **Posts:** 19

I have to correct myself - the object is not instantiated redundantly - it is the identical object that is included in the result multiple times. Quite strange behavior still, IMO.

VampBoy · **Posted:** Wed Aug 02, 2006 1:44 pm

chsell wrote:

I have to correct myself - the object is not instantiated redundantly - it is the identical object that is included in the result multiple times. Quite strange behavior still, IMO.

Hibernate is basically returning what you would get if you executed the raw sql.

You can then transform it as you see fit. However, it is a pain when wanting to do things like results paging.

damnhandy · **Posted:** Thu Aug 03, 2006 7:11 am

Quote:

whoa, that leaves me a little shocked. I was firmly assuming that hibernate was doing this for me (and I stated that in the initial post).

I thought the same thing initially but in many respects this makes sense. When you use FetchMode.JOIN, Hibernate joins the results of two entites in the same result set. With a many-to-one or one-to-one assocaition, this doesn't pose any issues. However to a one-to-many, things change: the result set will include both the parent and the child data in every row returned. This is why you were dupes in your result set. I think the resononing is that Hibernate can't automatically determine if a FetchMode.JOIN is for a single-ended or multiple association. It is not inconcievable that you may want to join both a one-to-many and a many-to-one association in the same query.

On the plus side, Cirteria queries, and HQL queries in Hibernate 3.2 handles this much better with the DistinctRootEntityResultTransformer: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/criterion/CriteriaSpecification.html#DISTINCT_ROOT_ENTITY.

Ryan-