-
1. Re: Join Fetch returning cartesian product
holyjoe Aug 22, 2006 5:09 PM (in response to darrenclarke)From the EJB 3 Java Persistence API doc:
SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1
A fetch join has the same join semantics as the corresponding inner or outer join, except that the related
objects specified on the right-hand side of the join operation are not returned in the query result or otherwise
referenced in the query. Hence, for example, if department 1 has five employees, the above query
returns five references to the department 1 entity.
Which seems to imply that what you see is the defined behavior, although I can't imagine why. -
2. Re: Join Fetch returning cartesian product
darrenclarke Aug 22, 2006 5:25 PM (in response to darrenclarke)Ah - I missed that - thanks for digging that out for me.
I think you're right - it would seem that it's the defined behaviour, though it seems a strange way to go about things.
At least I know I'm not going mad now! Thanks
Darren -
3. Re: Join Fetch returning cartesian product
holyjoe Aug 22, 2006 5:49 PM (in response to darrenclarke)It does seem strange. In SQL, if there are no NULL results for the right relation, a LEFT OUTER JOIN produces the same rows as the INNER JOIN. You'd think that the two should produce the same Objects in EJB QL.
Is there anyone in the upper echelons of those defining the specs who can explain the reasoning for this to us?
Thanks,
Joe -
4. Re: Join Fetch returning cartesian product
darrenclarke Aug 23, 2006 4:48 AM (in response to darrenclarke)It seems especially strange as Hibernate does know that there's a OneToMany relationship going on.
So if there's a single row in the table corresponding to the One-end and five rows that are related at the Many-end, I don't see why this isn't reflected in the returned result set, i.e. a single object that references five others, rather than exactly that plus four duplicates.
However, I could be missing something entirely fundamental here - hopefully someone will point it out if so :-) -
5. Re: Join Fetch returning cartesian product
ge0ffrey Aug 30, 2006 9:23 AM (in response to darrenclarke)Adding DISTINCT solves this (for hsql, mysql, ...), but my database (ms sqlserver...) doesn't allow to do this because it contains ntexts.
It seems as if adding DISTINCT, does not only change the query to the database, but also triggers Hibernate to distinct them. Can I do the second without doing hte first?