Bug when doing joint prefetching on a to-many relationship using a fetch limit?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug when doing joint prefetching on a to-many relationship using a fetch limit?

Hugi Þórðarson
Hi all.
I’m getting some unexpected behaviour when applying a fetch limit and doing joint prefetching on a to-many relationship. In short, the number of objects returned is always lower than the actual fetch limit I set. Problem goes away if I do a disjoint prefetch or no prefetching at all.

I created a tiny self-contained maven project that demonstrates the problem:

https://bitbucket.org/hugi/prefetch-dilemma

The source for the main class in that project (that can be run to show what happens):

https://bitbucket.org/hugi/prefetch-dilemma/src/master/src/main/java/prefetchdilemma/Main.java

Anyone run into this before?

Cheers,
- hugi
Reply | Threaded
Open this post in threaded view
|

Re: Bug when doing joint prefetching on a to-many relationship using a fetch limit?

Andrus Adamchik

> On Mar 13, 2017, at 4:41 PM, Hugi Þórðarson <[hidden email]> wrote:
>
> Hi all.
> I’m getting some unexpected behaviour when applying a fetch limit and doing joint prefetching on a to-many relationship. In short, the number of objects returned is always lower than the actual fetch limit I set. Problem goes away if I do a disjoint prefetch or no prefetching at all.
>
> I created a tiny self-contained maven project that demonstrates the problem:
>
> https://bitbucket.org/hugi/prefetch-dilemma
>
> The source for the main class in that project (that can be run to show what happens):
>
> https://bitbucket.org/hugi/prefetch-dilemma/src/master/src/main/java/prefetchdilemma/Main.java
>
> Anyone run into this before?
>
> Cheers,
> - hugi


Yeah, that's a limitation described here: [1]. Also mentioned in the docs, though it may be hard to spot [2]:

"Disjoint-by-ID Prefetching Semantics [..] Moreover this is the only type of prefetch that can handle SelectQueries with fetch limit. Both joint and regular disjoint prefetches may produce invalid results or generate inefficient fetch-the-entire table SQL when fetch limit is in effect."

Andrus

-------------------
Andrus Adamchik
Twitter: @andrus_a
https://objectstyle.com/apache-cayenne-training


[1] https://issues.apache.org/jira/browse/CAY-1130
[2] https://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html
Reply | Threaded
Open this post in threaded view
|

Re: Bug when doing joint prefetching on a to-many relationship using a fetch limit?

Hugi Thordarson-2
Hi Andrus, sorry about the belated reply.

>> Hi all.
>> I’m getting some unexpected behaviour when applying a fetch limit and doing joint prefetching on a to-many relationship. In short, the number of objects returned is always lower than the actual fetch limit I set. Problem goes away if I do a disjoint prefetch or no prefetching at all.
>>
>> I created a tiny self-contained maven project that demonstrates the problem:
>>
>> https://bitbucket.org/hugi/prefetch-dilemma
>>
>> The source for the main class in that project (that can be run to show what happens):
>>
>> https://bitbucket.org/hugi/prefetch-dilemma/src/master/src/main/java/prefetchdilemma/Main.java
>>
>> Anyone run into this before?
>>
>> Cheers,
>> - hugi
>
>
> Yeah, that's a limitation described here: [1]. Also mentioned in the docs, though it may be hard to spot [2]:
>
> "Disjoint-by-ID Prefetching Semantics [..] Moreover this is the only type of prefetch that can handle SelectQueries with fetch limit. Both joint and regular disjoint prefetches may produce invalid results or generate inefficient fetch-the-entire table SQL when fetch limit is in effect.”

Maybe Cayenne should throw an exception (or log a warning) if prefetch semantics other than Disjoint-by-ID is used with a fetch limit? This is something I can see users burning themselves on (I know I did).

Cheers,
- hugi
Reply | Threaded
Open this post in threaded view
|

Re: Bug when doing joint prefetching on a to-many relationship using a fetch limit?

Andrus Adamchik

> On Mar 31, 2017, at 1:03 PM, Hugi Thordarson <[hidden email]> wrote:
>
> Hi Andrus, sorry about the belated reply.
>
>>> Hi all.
>>> I’m getting some unexpected behaviour when applying a fetch limit and doing joint prefetching on a to-many relationship. In short, the number of objects returned is always lower than the actual fetch limit I set. Problem goes away if I do a disjoint prefetch or no prefetching at all.
>>>
>>> I created a tiny self-contained maven project that demonstrates the problem:
>>>
>>> https://bitbucket.org/hugi/prefetch-dilemma
>>>
>>> The source for the main class in that project (that can be run to show what happens):
>>>
>>> https://bitbucket.org/hugi/prefetch-dilemma/src/master/src/main/java/prefetchdilemma/Main.java
>>>
>>> Anyone run into this before?
>>>
>>> Cheers,
>>> - hugi
>>
>>
>> Yeah, that's a limitation described here: [1]. Also mentioned in the docs, though it may be hard to spot [2]:
>>
>> "Disjoint-by-ID Prefetching Semantics [..] Moreover this is the only type of prefetch that can handle SelectQueries with fetch limit. Both joint and regular disjoint prefetches may produce invalid results or generate inefficient fetch-the-entire table SQL when fetch limit is in effect.”
>
> Maybe Cayenne should throw an exception (or log a warning) if prefetch semantics other than Disjoint-by-ID is used with a fetch limit? This is something I can see users burning themselves on (I know I did).
>
> Cheers,
> - hugi

I'd go with a warning initially. Though we will need to identify all specific cases to print it. E.g. "fetchLimit|offset + joint + to-many".

Andrus