Grails why join fetch is not recommended for a none single-end association?

Question

i've seen in grails doc something about join fetch :

"This works well for single-ended associations, but you need to be careful with one-to-manys. Queries will work as you'd expect right up to the moment you add a limit to the number of results you want. At that point, you will likely end up with fewer results than you were expecting. The reason for this is quite technical but ultimately the problem arises from GORM using a left outer join."

i don't see why a left outer join can raise a problem in a none single-end association like one-to-many for example.

can you give me an example ?

if i take the example given by Joshua

Class Person {
    String name
    static hasMany = [numbers:PhoneNumber]
    static mapping = {
        nubmers fetch  : 'join'
    }
}

Class PhoneNumber{
     static belongsTo = [owner : Person]
}

//for testing
def person = Person.get(1)
result in: 
select name,... from 
       person left outer join phone_number 
       on person.id = phone_number.owner_id 
       where person.id=1;

Can you give me a query(in gorm) which can show me the problem ?

thanks

Answer 1

If I understand your question correctly then it's easiest to explain with a simple example. Let's say we have a domain class Person with many PhoneNumber classes associated with it. Thus a LEFT JOIN to fetch this data will result in a SQL result like this:

PersonId, Name, PhoneNumberId, TelNumber
-----------------------------------------
1, Joe, 1, 123-123-1234
1, Joe, 2, 222-222-2222
1, Joe, 3, 333-333-3333
2, Jane, 4, 000-000-000

This would be four records returned from the datasource, but only two instances of the Person domain with their associated phone numbers. However, if you limit this query to a maximum of 2 records you will in fact only get one instance of the Person domain (Joe in this example) and his first two phone numbers.

Hope that helps you understand the underlying issue.

P.S. This is an entirely theoretical example, the actual query results from GORM would have different column names and such due to Hibernate.

Update

An example GORM based query is quite simple to demonstrate:

def people = Person.list([max: 2])

You might expect the above to give you two person instances, but in reality because of the left join you will only end up with one instance. Turning on SQL logging will show you the actual query being executed and you will notice it's using a LEFT join.