Prefetching can be dangerous

The Scene

Consider this scenario: There is an entity Dogs, which has an attribute name. We want to fetch all dogs with the given name. As we like to output the owner also, we hope to save some faulting round trips by prefetching the owner relationship (Dogs::idOwner->Owners::idR):

   q     = [EOQualifier qualifierWithQualifierFormat:@"name = %@", name];
   fetch = [EOFetchSpecification fetchSpecificationWithEntityName:@"Dogs"
                                         qualifier:q
                                     sortOrderings:nil];
   [fetch setPrefetchingRelationshipKeyPaths:[NSArray arrayWithObjects:@"owner"]];
   dogs = [context objectsWithFetchSpecifications:fetch];

The produced SQL would look somewhat like this, ID_R being the primary key column of each table:

   select ID_R, NAME from OWNERS, DOGS where DOGS.ID_OWNER = OWNERS.ID_R and DOGS.NAME = 'Fido'
   select ID_R, ID_OWNER, NAME from DOGS where NAME = 'Fido'

Then all owners are known to have been fetched, before the dogs are actually are fetched. Or aren't they ?

The problem

The problem arises with the introduction of the fetchlimit. Lets assume the database looks like this:

DOGS ID_R ID_OWNER NAME 11 22 'Fido' 12 21 'Fido'

OWNERS ID_R NAME 21 'Helga' 22 'Kurt'

With a fetchlimit of 1

   select ID_R, ID_OWNER, NAME from DOGS where NAME = 'Fido' 

will return 11,22,'Fido'

and

   select ID_R, NAME from OWNERS, DOGS where DOGS.ID_OWNER = OWNERS.ID_R and DOGS.NAME = 'Fido'

could return either 21,'Helga' or '22', Kurt depending on the way the database optimizer chooses to either read table OWNERS or table DOGS first.

But only 22,'Kurt' is right. Therefore there can be superflous fetches. So prefetching can slow things down!