Grails
  1. Grails
  2. GRAILS-8915

Grails 2.0.1 only fetching first element from eager collection

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.0.5, 2.1-RC3
    • Component/s: Persistence
    • Labels:
    • Environment:
      MacOSX Lion
    • Testcase included:
      yes

      Description

      The attached application contains an n:m relation between Authors and Books.
      Authors are fetched eagerly when loading a book.
      Bootstrap connects one book ("Book1") with two Authors ("Author1" and "Author2").
      In the integration test "BookTests" the expression

      Book.findByTitle('Book1').authors

      returns only "Author1" but not "Author2".
      The reason seems to be that the finderBy leads to a SQL statement that contains the join between books and author as needed for eager fetching but also a "limit"-expression.
      This seems to restrict the results that are fetched.

        Activity

        Oliver Wahlen created issue -
        Hide
        Txomin added a comment -

        This issue seems to be related to a problem with the way Hibernate Criteria handles left outer joins:

        https://hibernate.onjira.com/browse/HHH-2049

        https://hibernate.onjira.com/browse/HHH-6877

        Show
        Txomin added a comment - This issue seems to be related to a problem with the way Hibernate Criteria handles left outer joins: https://hibernate.onjira.com/browse/HHH-2049 https://hibernate.onjira.com/browse/HHH-6877
        Hide
        Patrick Ryan added a comment -

        I am hoping someone can help us best understand how to work around this issue until a more permanent solution can be found. At the moment this issue is a blocker for us.

        I think this particular issue came about because of the fix for this issue:
        GRAILS-8762 - findBy methods not setting a limit even though they return only 1 result

        Below are two queries from a findBy method we have in our application. The first one is from our current Grails 1.3.7 system and the second is from the Grails 2.0.4. I am going to shorten the queries so the salient difference is better seen.

        Grails 1.3.7 Query:

        Hibernate:
        select
        – a bunch of columns –
        from
        node this_
        inner join
        apobj this_1_
        on this_.id=this_1_.id
        left outer join
        nodedg childnodee2_
        on this_.id=childnodee2_.parent_node_id
        left outer join
        apobj childnodee2_1_
        on childnodee2_.id=childnodee2_1_.id
        left outer join
        corner corners3_
        on childnodee2_.id=corners3_.edge_id
        left outer join
        apobj corners3_1_
        on corners3_.id=corners3_1_.id
        left outer join
        nodedg parentnode4_
        on this_.id=parentnode4_.child_node_id
        left outer join
        apobj parentnode4_1_
        on parentnode4_.id=parentnode4_1_.id
        where
        this_1_.sequence=?
        and this_1_.ap_id=?

        Grails 2.0.4 Query:

        Hibernate:
        select
        – a bunch of columns –
        from
        node this_
        inner join
        apobj this_1_
        on this_.id=this_1_.id
        left outer join
        nodedg childnodee2_
        on this_.id=childnodee2_.parent_node_id
        left outer join
        apobj childnodee2_1_
        on childnodee2_.id=childnodee2_1_.id
        left outer join
        corner corners3_
        on childnodee2_.id=corners3_.edge_id
        left outer join
        apobj corners3_1_
        on corners3_.id=corners3_1_.id
        left outer join
        nodedg parentnode4_
        on this_.id=parentnode4_.child_node_id
        left outer join
        apobj parentnode4_1_
        on parentnode4_.id=parentnode4_1_.id
        where
        this_1_.sequence=?
        and this_1_.ap_id=? limit ?

        NOTE: the limit on the query. This ultimately limits the number of eager loaded elements and we do not see all of the children in the collections.

        Any help and/or suggestions on how to proceed is greatly appreciated. Thank you

        Show
        Patrick Ryan added a comment - I am hoping someone can help us best understand how to work around this issue until a more permanent solution can be found. At the moment this issue is a blocker for us. I think this particular issue came about because of the fix for this issue: GRAILS-8762 - findBy methods not setting a limit even though they return only 1 result Below are two queries from a findBy method we have in our application. The first one is from our current Grails 1.3.7 system and the second is from the Grails 2.0.4. I am going to shorten the queries so the salient difference is better seen. Grails 1.3.7 Query: Hibernate: select – a bunch of columns – from node this_ inner join apobj this_1_ on this_.id=this_1_.id left outer join nodedg childnodee2_ on this_.id=childnodee2_.parent_node_id left outer join apobj childnodee2_1_ on childnodee2_.id=childnodee2_1_.id left outer join corner corners3_ on childnodee2_.id=corners3_.edge_id left outer join apobj corners3_1_ on corners3_.id=corners3_1_.id left outer join nodedg parentnode4_ on this_.id=parentnode4_.child_node_id left outer join apobj parentnode4_1_ on parentnode4_.id=parentnode4_1_.id where this_1_.sequence=? and this_1_.ap_id=? Grails 2.0.4 Query: Hibernate: select – a bunch of columns – from node this_ inner join apobj this_1_ on this_.id=this_1_.id left outer join nodedg childnodee2_ on this_.id=childnodee2_.parent_node_id left outer join apobj childnodee2_1_ on childnodee2_.id=childnodee2_1_.id left outer join corner corners3_ on childnodee2_.id=corners3_.edge_id left outer join apobj corners3_1_ on corners3_.id=corners3_1_.id left outer join nodedg parentnode4_ on this_.id=parentnode4_.child_node_id left outer join apobj parentnode4_1_ on parentnode4_.id=parentnode4_1_.id where this_1_.sequence=? and this_1_.ap_id=? limit ? NOTE: the limit on the query. This ultimately limits the number of eager loaded elements and we do not see all of the children in the collections. Any help and/or suggestions on how to proceed is greatly appreciated. Thank you
        Hide
        Stephen Cresswell added a comment -

        Also hitting this. Would a workaround be to use findAllBy (thus removing the limit), then to take the first result?

        Show
        Stephen Cresswell added a comment - Also hitting this. Would a workaround be to use findAllBy (thus removing the limit), then to take the first result?
        Graeme Rocher made changes -
        Field Original Value New Value
        Fix Version/s 2.0.5 [ 13129 ]
        Fix Version/s 2.1 [ 13125 ]
        Priority Major [ 3 ] Blocker [ 1 ]
        Graeme Rocher made changes -
        Assignee Graeme Rocher [ graemerocher ]
        Graeme Rocher made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Graeme Rocher made changes -
        Status In Progress [ 3 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]

          People

          • Assignee:
            Graeme Rocher
            Reporter:
            Oliver Wahlen
          • Votes:
            12 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development