5 Replies Latest reply on Aug 30, 2006 9:23 AM by Geoffrey De Smet

    Join Fetch returning cartesian product

    darrenclarke Newbie

      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

        • 1. Re: Join Fetch returning cartesian product
          Joe DeStefano Newbie

          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 Newbie

            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
              Joe DeStefano Newbie

              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 Newbie

                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
                  Geoffrey De Smet Newbie

                  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?