Join Fetch returning cartesian product

[darrenclarke]

Apologies in advance if I'm asking something dumb here; I'm sure there's an obvious answer to this but I've just spent a day with my EJB3 book and on Google and haven't been able to find it.

Imagine I have two entities - Parent and Child. Parent has a unidirectional one-to-many (lazy) relationship with Child, e.g:

    @Entity
    public class Parent {
        @Id
        private int id;

        @OneToMany( cascade=CascadeType.ALL, fetch=FetchType.LAZY)
        private List children = new ArrayList();

        //snip
    }


    @Entity
    public class Child {
        @Id
        private int id;

        //snip
    }

[Note : my List and ArrayList in Parent are typed as containing Child objects but the syntax isn't being displayed here as it thinks it's XML.]


Now imagine I have two Parents, each with three Child objects and I want a query to return all Parent objects including nested children:

SELECT p FROM Parent p LEFT JOIN FETCH p.children

I would expect this to return two Parent objects, but it returns six - each Parent object is repeated n times where n is the number of nested Child objects. If I use a "SELECT DISTINCT ..." then the problem is fixed, but that seems a strange thing to need to do and I'm wondering if it's necessary, or whether I'm missing something?

Thanks in advance,
Darren

[holyjoe]

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.

[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

[holyjoe]

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

[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 :-)

[ge0ffrey]

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?