You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Michael Simons <mi...@optitool.de> on 2009/11/17 11:27:31 UTC
How work around OpenJPA-1365
Hello,
OpenJPA-1365: DISTINCT keyword has no effect with JOIN FETCH queries
We're porting a formerly JDO based application to JPA.
We wanted to heavily use JOIN FETCHs to reduce the number of SQL statements generated.
We do not know how to get rid of those weird duplicates produced by a JPQL statement including a
join fetch clause.
We urgently need a solution or work-around for this problem, because we don't see another way to
get our app fast enough with OpenJPA.
How can we work-around this problem?
Does anybody know the status of the issue OpenJPA-1365? Is anybody working on it?
kind regards,
Michael
Re: How work around OpenJPA-1365
Posted by Michael Simons <mi...@optitool.de>.
Daryl Stultz schrieb:
> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> <mi...@optitool.de>wrote:
>
>
>> You state, that you're query with distinct and join fetch does work
>> properly. But this would
>> mean OpenJPA-1365 doesn't occur, does it?
>>
>
> The JIRA states:
> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>
> So 1365 does not occur unless you've patched your code such that 894 is
> fixed. What version of OpenJPA are you using and do you have any patches in
> place?
>
We are using OpenJPA 1.2.1; AFAIK we do not have 894 patch. But we do have the same behaviour as
described in 1365.
>> When we call "select a from A a join fetch B" we get n instances of A, with
>> n = numbers of
>> A-B-associations.
>>
>
> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
> distinct rows of A which sounds like what you (and I) want but is improper.
> 894 shows that a second run of the query in the same EntityManager yields
> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>
> select distinct a from A a join fetch a.bs
> ?
>
> How about these two:
> select a from A a left join fetch a.bs
> select distinct a from A a left join fetch a.bs
>
> I don't want to give the impression that I'm an expert on the matter, just
> that I've dealt with this issue and I want to be sure my understanding of
> things is accurate.
>
--
mit freundlichen Grüßen,
Michael Simons
OPTITOOL GmbH
Bruderwöhrdstr. 15b
93055 Regensburg
Tel: +49(0)941.595780
Fax: +49(0)941.595787
Re: How work around OpenJPA-1365
Posted by Michael Dick <mi...@gmail.com>.
Hi Michael,
As I understand it the JPA 2.0 spec is 'effectively' finalized. We're
certainly past the point where a new feature like FetchPlans could be added.
FetchPlans are here to stay with OpenJPA though.
We can bring them up for consideration for the next version of the JPA spec
though.
-mike
On Wed, Nov 18, 2009 at 5:06 AM, Michael Vorburger <
mvorburger@odyssey-group.com> wrote:
> >>
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/
> manual.html#ref_guide_fetch<http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/%0Amanual.html#ref_guide_fetch>
> > That sounds good, although it's not standard.
>
> But very, very, useful.. ;) I'm not sure how one can do serious ORM
> without (something like) this; the fetch = FetchType.LAZY / EAGER stuff
> is stuff way too static, IMHO.
>
> Always wondered why this never made it
> (http://www.dataservices-connection.com/2008/09/need-for-fetch-plans.htm
> l) from JDO v2 API
> (http://db.apache.org/jdo/api20/apidocs/javax/jdo/FetchPlan.html) into
> the JPA spec, not even in JPA v2 yet AFAIK.. is there any hope a JPA
> v2.x finally picks this up?
>
>
> -----Original Message-----
> From: Michael Simons [mailto:michael.simons@optitool.de]
> Sent: Wednesday, November 18, 2009 11:54 AM
> To: users@openjpa.apache.org
> Subject: Re: How work around OpenJPA-1365
>
> Hi Michael,
>
> Michael Dick schrieb:
> > Hi Michael,
> >
> > Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to
>
> > take a look at that. It probably happens if the query does not fetch
> > records from the database. If that's what you're seeing then I just
> > need to update the JIRA with that info.
> We are still investigating on this. Please stand by.
>
> >
> > I'm not terribly familiar with JDO. I was under the impression that
> > OpenJPA's FetchPlans were identical (or nearly identical) to those in
> JDO.
> >
> > To use my example (Employee and Manager) you would do something like
> this :
> > OpenJPAEntityManager em = getEntityManager();
> >
> > OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> > q.getFetchPlan().addField(Manager.class, "employees");
> >
> > List<Manager> managers = q.getResultList();
> >
> > There's a lot more in the manual about it, I'd start looking at [1].
> >
> > [1]
>
> >
> > Hope this helps,
> > -mike
> >
> >
> > On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons
> > <michael.simons@optitool.de
> >> wrote:
> >
> >> Hi Mike,
> >>
> >> Do you mean fetchplan like described in the manual in chapter 1.7.4?
> >> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so,
> >> is there a description, how to do that?
> >>
> >> kind regards,
> >> Michael
> >>
> >> Michael Dick schrieb:
> >>> Hi all,
> >>>
> >>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
> >> correct
> >>> behavior. If you don't want the duplicates to be returned you should
>
> >>> use
> >> the
> >>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
> >>>
> >>> There are several outstanding issues though (which I'm working on):
> >>>
> >>> 1.) OPENJPA-894: When results are returned from the database
> >>> OpenJPA automatically removes duplicates from the list. If the
> >>> results are
> >> fetched
> >>> from memory the duplicates reappear.
> >>>
> >>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the
> >>> distinct keyword doesn't work. This is because OpenJPA merely
> >>> prepends the
> >> DISTINCT
> >>> keyword to the SQL generated which doesn't work if you're selecting
> >> across
> >>> multiple tables. Instead we need to filter the result list after
> >> retrieving
> >>> from the database.
> >>>
> >>> There are two proposed fixes for OPENJPA-894 each of which have some
>
> >>> drawbacks.
> >>>
> >>> 3a.) Mike's fix : supports pagination but does not support multiple
> >>> JOIN FETCH statements (ie SELECT m FROM Manager m JOIN FETCH
> >>> m.employees JOIN FETCH m.projects returns the wrong number of
> results).
> >>>
> >>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does
> >>> not support pagination (ie query.setMaxResults(),
> >>> query.setFirstResult()
> >> doesn't
> >>> page forward as expected).
> >>>
> >>> So there is work being done, but it's turned out to be a very
> >>> ticklish
> >> issue
> >>> to solve.
> >>>
> >>> At the risk of muddying the waters a bit if you're migrating a JDO
> >>> application have you considered using OpenJPA's FetchPlan
> >>> implementation
> >> to
> >>> eagerly load some fields? Over medium - large datasets I've found
> >>> this to
> >> be
> >>> significantly faster than using a JOIN FETCH, but YMMV.
> >>>
> >>> Hope this helps,
> >>>
> >>> -mike
> >>>
> >>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
> >> wrote:
> >>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> >>>> <mi...@optitool.de>wrote:
> >>>>
> >>>>
> >>>>> You state, that you're query with distinct and join fetch does
> >>>>> work properly. But this would mean OpenJPA-1365 doesn't occur,
> >>>>> does it?
> >>>>>
> >>>> The JIRA states:
> >>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
> >>>>
> >>>> So 1365 does not occur unless you've patched your code such that
> >>>> 894 is fixed. What version of OpenJPA are you using and do you have
>
> >>>> any patches
> >> in
> >>>> place?
> >>>>
> >>>>
> >>>>> When we call "select a from A a join fetch B" we get n instances
> >>>>> of A,
> >>>> with
> >>>>> n = numbers of
> >>>>> A-B-associations.
> >>>>>
> >>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I
> >>>> get distinct rows of A which sounds like what you (and I) want but
> >>>> is
> >> improper.
> >>>> 894 shows that a second run of the query in the same EntityManager
> >> yields
> >>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do
> this:
> >>>>
> >>>> select distinct a from A a join fetch a.bs ?
> >>>>
> >>>> How about these two:
> >>>> select a from A a left join fetch a.bs select distinct a from A a
> >>>> left join fetch a.bs
> >>>>
> >>>> I don't want to give the impression that I'm an expert on the
> >>>> matter,
> >> just
> >>>> that I've dealt with this issue and I want to be sure my
> >>>> understanding
> >> of
> >>>> things is accurate.
> >>>>
> >>>> --
> >>>> Daryl Stultz
> >>>> _____________________________________
> >>>> 6 Degrees Software and Consulting, Inc.
> >>>> http://www.6degrees.com
> >>>> mailto:daryl@6degrees.com
> >>>>
> >>
> >>
> >
>
>
> ____________________________________________________________
>
> • This email and any files transmitted with it are CONFIDENTIAL and
> intended
> solely for the use of the individual or entity to which they are
> addressed.
> • Any unauthorized copying, disclosure, or distribution of the material
> within
> this email is strictly forbidden.
> • Any views or opinions presented within this e-mail are solely those of
> the
> author and do not necessarily represent those of Odyssey Financial
> Technologies SA unless otherwise specifically stated.
> • An electronic message is not binding on its sender. Any message referring
> to
> a binding engagement must be confirmed in writing and duly signed.
> • If you have received this email in error, please notify the sender
> immediately
> and delete the original.
>
RE: How work around OpenJPA-1365
Posted by Michael Vorburger <mv...@odyssey-group.com>.
>>
http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/
manual.html#ref_guide_fetch
> That sounds good, although it's not standard.
But very, very, useful.. ;) I'm not sure how one can do serious ORM
without (something like) this; the fetch = FetchType.LAZY / EAGER stuff
is stuff way too static, IMHO.
Always wondered why this never made it
(http://www.dataservices-connection.com/2008/09/need-for-fetch-plans.htm
l) from JDO v2 API
(http://db.apache.org/jdo/api20/apidocs/javax/jdo/FetchPlan.html) into
the JPA spec, not even in JPA v2 yet AFAIK.. is there any hope a JPA
v2.x finally picks this up?
-----Original Message-----
From: Michael Simons [mailto:michael.simons@optitool.de]
Sent: Wednesday, November 18, 2009 11:54 AM
To: users@openjpa.apache.org
Subject: Re: How work around OpenJPA-1365
Hi Michael,
Michael Dick schrieb:
> Hi Michael,
>
> Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to
> take a look at that. It probably happens if the query does not fetch
> records from the database. If that's what you're seeing then I just
> need to update the JIRA with that info.
We are still investigating on this. Please stand by.
>
> I'm not terribly familiar with JDO. I was under the impression that
> OpenJPA's FetchPlans were identical (or nearly identical) to those in
JDO.
>
> To use my example (Employee and Manager) you would do something like
this :
> OpenJPAEntityManager em = getEntityManager();
>
> OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> q.getFetchPlan().addField(Manager.class, "employees");
>
> List<Manager> managers = q.getResultList();
>
> There's a lot more in the manual about it, I'd start looking at [1].
>
> [1]
>
> Hope this helps,
> -mike
>
>
> On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons
> <michael.simons@optitool.de
>> wrote:
>
>> Hi Mike,
>>
>> Do you mean fetchplan like described in the manual in chapter 1.7.4?
>> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so,
>> is there a description, how to do that?
>>
>> kind regards,
>> Michael
>>
>> Michael Dick schrieb:
>>> Hi all,
>>>
>>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
>> correct
>>> behavior. If you don't want the duplicates to be returned you should
>>> use
>> the
>>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>>>
>>> There are several outstanding issues though (which I'm working on):
>>>
>>> 1.) OPENJPA-894: When results are returned from the database
>>> OpenJPA automatically removes duplicates from the list. If the
>>> results are
>> fetched
>>> from memory the duplicates reappear.
>>>
>>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the
>>> distinct keyword doesn't work. This is because OpenJPA merely
>>> prepends the
>> DISTINCT
>>> keyword to the SQL generated which doesn't work if you're selecting
>> across
>>> multiple tables. Instead we need to filter the result list after
>> retrieving
>>> from the database.
>>>
>>> There are two proposed fixes for OPENJPA-894 each of which have some
>>> drawbacks.
>>>
>>> 3a.) Mike's fix : supports pagination but does not support multiple
>>> JOIN FETCH statements (ie SELECT m FROM Manager m JOIN FETCH
>>> m.employees JOIN FETCH m.projects returns the wrong number of
results).
>>>
>>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does
>>> not support pagination (ie query.setMaxResults(),
>>> query.setFirstResult()
>> doesn't
>>> page forward as expected).
>>>
>>> So there is work being done, but it's turned out to be a very
>>> ticklish
>> issue
>>> to solve.
>>>
>>> At the risk of muddying the waters a bit if you're migrating a JDO
>>> application have you considered using OpenJPA's FetchPlan
>>> implementation
>> to
>>> eagerly load some fields? Over medium - large datasets I've found
>>> this to
>> be
>>> significantly faster than using a JOIN FETCH, but YMMV.
>>>
>>> Hope this helps,
>>>
>>> -mike
>>>
>>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
>> wrote:
>>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>>>> <mi...@optitool.de>wrote:
>>>>
>>>>
>>>>> You state, that you're query with distinct and join fetch does
>>>>> work properly. But this would mean OpenJPA-1365 doesn't occur,
>>>>> does it?
>>>>>
>>>> The JIRA states:
>>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>>>
>>>> So 1365 does not occur unless you've patched your code such that
>>>> 894 is fixed. What version of OpenJPA are you using and do you have
>>>> any patches
>> in
>>>> place?
>>>>
>>>>
>>>>> When we call "select a from A a join fetch B" we get n instances
>>>>> of A,
>>>> with
>>>>> n = numbers of
>>>>> A-B-associations.
>>>>>
>>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I
>>>> get distinct rows of A which sounds like what you (and I) want but
>>>> is
>> improper.
>>>> 894 shows that a second run of the query in the same EntityManager
>> yields
>>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do
this:
>>>>
>>>> select distinct a from A a join fetch a.bs ?
>>>>
>>>> How about these two:
>>>> select a from A a left join fetch a.bs select distinct a from A a
>>>> left join fetch a.bs
>>>>
>>>> I don't want to give the impression that I'm an expert on the
>>>> matter,
>> just
>>>> that I've dealt with this issue and I want to be sure my
>>>> understanding
>> of
>>>> things is accurate.
>>>>
>>>> --
>>>> Daryl Stultz
>>>> _____________________________________
>>>> 6 Degrees Software and Consulting, Inc.
>>>> http://www.6degrees.com
>>>> mailto:daryl@6degrees.com
>>>>
>>
>>
>
____________________________________________________________
� This email and any files transmitted with it are CONFIDENTIAL and intended
solely for the use of the individual or entity to which they are addressed.
� Any unauthorized copying, disclosure, or distribution of the material within
this email is strictly forbidden.
� Any views or opinions presented within this e-mail are solely those of the
author and do not necessarily represent those of Odyssey Financial
Technologies SA unless otherwise specifically stated.
� An electronic message is not binding on its sender. Any message referring to
a binding engagement must be confirmed in writing and duly signed.
� If you have received this email in error, please notify the sender immediately
and delete the original.
Re: How work around OpenJPA-1365
Posted by Michael Simons <mi...@optitool.de>.
Hi Michael,
Michael Dick schrieb:
> Hi Michael,
>
> Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to take
> a look at that. It probably happens if the query does not fetch records from
> the database. If that's what you're seeing then I just need to update the
> JIRA with that info.
We are still investigating on this. Please stand by.
>
> I'm not terribly familiar with JDO. I was under the impression that
> OpenJPA's FetchPlans were identical (or nearly identical) to those in JDO.
>
> To use my example (Employee and Manager) you would do something like this :
> OpenJPAEntityManager em = getEntityManager();
>
> OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> q.getFetchPlan().addField(Manager.class, "employees");
>
> List<Manager> managers = q.getResultList();
>
> There's a lot more in the manual about it, I'd start looking at [1].
>
> [1]
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
That sounds good, although it's not standard.
>
> Hope this helps,
> -mike
>
>
> On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <michael.simons@optitool.de
>> wrote:
>
>> Hi Mike,
>>
>> Do you mean fetchplan like described in the manual in chapter 1.7.4?
>> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
>> there a description, how
>> to do that?
>>
>> kind regards,
>> Michael
>>
>> Michael Dick schrieb:
>>> Hi all,
>>>
>>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
>> correct
>>> behavior. If you don't want the duplicates to be returned you should use
>> the
>>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>>>
>>> There are several outstanding issues though (which I'm working on):
>>>
>>> 1.) OPENJPA-894: When results are returned from the database OpenJPA
>>> automatically removes duplicates from the list. If the results are
>> fetched
>>> from memory the duplicates reappear.
>>>
>>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
>>> keyword doesn't work. This is because OpenJPA merely prepends the
>> DISTINCT
>>> keyword to the SQL generated which doesn't work if you're selecting
>> across
>>> multiple tables. Instead we need to filter the result list after
>> retrieving
>>> from the database.
>>>
>>> There are two proposed fixes for OPENJPA-894 each of which have some
>>> drawbacks.
>>>
>>> 3a.) Mike's fix : supports pagination but does not support multiple JOIN
>>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
>>> FETCH m.projects returns the wrong number of results).
>>>
>>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
>>> support pagination (ie query.setMaxResults(), query.setFirstResult()
>> doesn't
>>> page forward as expected).
>>>
>>> So there is work being done, but it's turned out to be a very ticklish
>> issue
>>> to solve.
>>>
>>> At the risk of muddying the waters a bit if you're migrating a JDO
>>> application have you considered using OpenJPA's FetchPlan implementation
>> to
>>> eagerly load some fields? Over medium - large datasets I've found this to
>> be
>>> significantly faster than using a JOIN FETCH, but YMMV.
>>>
>>> Hope this helps,
>>>
>>> -mike
>>>
>>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
>> wrote:
>>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>>>> <mi...@optitool.de>wrote:
>>>>
>>>>
>>>>> You state, that you're query with distinct and join fetch does work
>>>>> properly. But this would
>>>>> mean OpenJPA-1365 doesn't occur, does it?
>>>>>
>>>> The JIRA states:
>>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>>>
>>>> So 1365 does not occur unless you've patched your code such that 894 is
>>>> fixed. What version of OpenJPA are you using and do you have any patches
>> in
>>>> place?
>>>>
>>>>
>>>>> When we call "select a from A a join fetch B" we get n instances of A,
>>>> with
>>>>> n = numbers of
>>>>> A-B-associations.
>>>>>
>>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
>>>> distinct rows of A which sounds like what you (and I) want but is
>> improper.
>>>> 894 shows that a second run of the query in the same EntityManager
>> yields
>>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>>>>
>>>> select distinct a from A a join fetch a.bs
>>>> ?
>>>>
>>>> How about these two:
>>>> select a from A a left join fetch a.bs
>>>> select distinct a from A a left join fetch a.bs
>>>>
>>>> I don't want to give the impression that I'm an expert on the matter,
>> just
>>>> that I've dealt with this issue and I want to be sure my understanding
>> of
>>>> things is accurate.
>>>>
>>>> --
>>>> Daryl Stultz
>>>> _____________________________________
>>>> 6 Degrees Software and Consulting, Inc.
>>>> http://www.6degrees.com
>>>> mailto:daryl@6degrees.com
>>>>
>>
>>
>
Re: How work around OpenJPA-1365
Posted by Michael Dick <mi...@gmail.com>.
Hi Michael,
I think there are two issues here.
1. The FetchPlan implementation with OpenJPA doesn't generate JOIN FETCH
statements like you're expecting. As far as I know the only way to get the
behavior you're looking for is to put the JOIN FETCH clause in your JPQL
statement itself.
2. OpenJPA's query engine translates multiple JOIN FETCH clauses into
separate statements. I'm not sure why this was done or what it would take to
fix it, but at first glance that looks like the reason why your query :
"SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH p.locals"
generates so many SQL statements..
The merit of issue 1 may depend on your environment. I've seen multiple
statements perform signifcantly better than a JOIN FETCH approach when
paired with OpenJPA's Large ResultSets. Your mileage may vary of course.
I'd have to dig in to the multiple statements aspect of issue 2. If it is a
result of splitting multiple JOIN FETCHes into multiple SQL statements I
suspect it'll be very involved to 'fix'. I could be pleasantly surprised
though.
-mike
On Thu, Nov 19, 2009 at 3:46 AM, Michael Simons
<mi...@optitool.de>wrote:
> Hello Mike,
>
> My Model:
> Product(1:*)LocalizedProduct
> Product(1:*)ProductDisplayOption
> ProductDisplayOption extends DisplayOption
>
> I've setup a FetchPlan with this:
> @FetchGroups({
> @FetchGroup(name="productDetails", attributes={
> @FetchAttribute(name="locals"),
>
> @FetchAttribute(name="displayOptions")
> })
> })
> public class Product implements java.io.Serializable, SingleLocalized,
> Customizable {
> /** Localizations */
> @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY,
> mappedBy="product")
> private Set<LocalizedProduct> locals = new HashSet<LocalizedProduct> (13);
>
> @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY,
> mappedBy="product")
> private Set<ProductDisplayOption> displayOptions = new
> HashSet<ProductDisplayOption> (113);
> ...
> }
>
> When running the following code:
> private void retrieveProducts (ISentinel cache) {
> Query query = em.createQuery ("SELECT p from Product p");
> OpenJPAQuery oq = OpenJPAPersistence.cast (query);
> oq.getFetchPlan ().setMaxFetchDepth (10).addFetchGroup
> ("productDetails");
> for (Object obj : query.getResultList ()) {
> ...
> }
> }
>
>
> Unfortunately the produced SQL looks like this:
> SELECT t0.product_id, t0.jdo_version, t0.alt_ex_id, t0.density, t0.ext_id,
> t0.indefinite FROM
> product t0
>
> SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version,
> t1.display_color, ...
> FROM product_display_option t0 INNER JOIN display_option t1 ON
> t0.display_option_id =
> t1.display_option_id
> WHERE t0.product_id = ?
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0 WHERE t0.product_id = ?
>
> SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version,
> t1.display_color, ...
> FROM product_display_option t0 INNER JOIN display_option t1 ON
> t0.display_option_id =
> t1.display_option_id
> WHERE t0.product_id = ?
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0 WHERE t0.product_id = ?
>
> ... // the second and third statement are repeated for every record in the
> product table.
>
> What I would expect (and got with the formerly used JDO Impl) is like this:
> SELECT ...
> FROM product t0
> LEFT JOIN product_display_option t1 on t1.product_id = t0.product_id
> JOIN display_option t2 on t2.display_option_id = t1.display_option_id;
> SELECT ...
> FROM product t0
> LEFT JOIN localized_product t1 on t1.product_id = t0.product_id;
>
> ---
> So how do I convince OpenJPA to join the tables when using fetchplan?
>
> When I run the following code instead without a fetchplan:
> private void retrieveProducts (ISentinel cache) {
> Query query = em.createQuery (
> "SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH
> p.locals");
> for (Object obj : query.getResultList ()) {
> ...
> }
> }
>
> The produced SQL looks like (what's more like what I would have expected,
> but still produces too
> much "single selects"):
> SELECT ...
> FROM product t0
> INNER JOIN product_display_option t1 ON t0.product_id = t1.product_id
> INNER JOIN display_option t2 ON t1.display_option_id = t2.display_option_id
>
> SELECT t0.localized_product_id, t0.jdo_version, t0.description,
> t0.lnguage_id, t0.product_id
> FROM localized_product t0
> WHERE t0.product_id = ?
> ... // The last statement again is executed for every record in the product
> table.
>
> How can I reduce the number of statements generated?
>
> kind regards,
> Michael
>
> Michael Dick schrieb:
> > Hi Michael,
> >
> > Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to
> take
> > a look at that. It probably happens if the query does not fetch records
> from
> > the database. If that's what you're seeing then I just need to update the
> > JIRA with that info.
> >
> > I'm not terribly familiar with JDO. I was under the impression that
> > OpenJPA's FetchPlans were identical (or nearly identical) to those in
> JDO.
> >
> > To use my example (Employee and Manager) you would do something like this
> :
> > OpenJPAEntityManager em = getEntityManager();
> >
> > OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> > q.getFetchPlan().addField(Manager.class, "employees");
> >
> > List<Manager> managers = q.getResultList();
> >
> > There's a lot more in the manual about it, I'd start looking at [1].
> >
> > [1]
> >
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
> >
> > Hope this helps,
> > -mike
> >
> >
> > On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <
> michael.simons@optitool.de
> >> wrote:
> >
> >> Hi Mike,
> >>
> >> Do you mean fetchplan like described in the manual in chapter 1.7.4?
> >> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
> >> there a description, how
> >> to do that?
> >>
> >> kind regards,
> >> Michael
> >>
> >> Michael Dick schrieb:
> >>> Hi all,
> >>>
> >>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
> >> correct
> >>> behavior. If you don't want the duplicates to be returned you should
> use
> >> the
> >>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
> >>>
> >>> There are several outstanding issues though (which I'm working on):
> >>>
> >>> 1.) OPENJPA-894: When results are returned from the database OpenJPA
> >>> automatically removes duplicates from the list. If the results are
> >> fetched
> >>> from memory the duplicates reappear.
> >>>
> >>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
> >>> keyword doesn't work. This is because OpenJPA merely prepends the
> >> DISTINCT
> >>> keyword to the SQL generated which doesn't work if you're selecting
> >> across
> >>> multiple tables. Instead we need to filter the result list after
> >> retrieving
> >>> from the database.
> >>>
> >>> There are two proposed fixes for OPENJPA-894 each of which have some
> >>> drawbacks.
> >>>
> >>> 3a.) Mike's fix : supports pagination but does not support multiple
> JOIN
> >>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees
> JOIN
> >>> FETCH m.projects returns the wrong number of results).
> >>>
> >>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
> >>> support pagination (ie query.setMaxResults(), query.setFirstResult()
> >> doesn't
> >>> page forward as expected).
> >>>
> >>> So there is work being done, but it's turned out to be a very ticklish
> >> issue
> >>> to solve.
> >>>
> >>> At the risk of muddying the waters a bit if you're migrating a JDO
> >>> application have you considered using OpenJPA's FetchPlan
> implementation
> >> to
> >>> eagerly load some fields? Over medium - large datasets I've found this
> to
> >> be
> >>> significantly faster than using a JOIN FETCH, but YMMV.
> >>>
> >>> Hope this helps,
> >>>
> >>> -mike
> >>>
> >>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
> >> wrote:
> >>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> >>>> <mi...@optitool.de>wrote:
> >>>>
> >>>>
> >>>>> You state, that you're query with distinct and join fetch does work
> >>>>> properly. But this would
> >>>>> mean OpenJPA-1365 doesn't occur, does it?
> >>>>>
> >>>> The JIRA states:
> >>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
> >>>>
> >>>> So 1365 does not occur unless you've patched your code such that 894
> is
> >>>> fixed. What version of OpenJPA are you using and do you have any
> patches
> >> in
> >>>> place?
> >>>>
> >>>>
> >>>>> When we call "select a from A a join fetch B" we get n instances of
> A,
> >>>> with
> >>>>> n = numbers of
> >>>>> A-B-associations.
> >>>>>
> >>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
> >>>> distinct rows of A which sounds like what you (and I) want but is
> >> improper.
> >>>> 894 shows that a second run of the query in the same EntityManager
> >> yields
> >>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
> >>>>
> >>>> select distinct a from A a join fetch a.bs
> >>>> ?
> >>>>
> >>>> How about these two:
> >>>> select a from A a left join fetch a.bs
> >>>> select distinct a from A a left join fetch a.bs
> >>>>
> >>>> I don't want to give the impression that I'm an expert on the matter,
> >> just
> >>>> that I've dealt with this issue and I want to be sure my understanding
> >> of
> >>>> things is accurate.
> >>>>
> >>>> --
> >>>> Daryl Stultz
> >>>> _____________________________________
> >>>> 6 Degrees Software and Consulting, Inc.
> >>>> http://www.6degrees.com
> >>>> mailto:daryl@6degrees.com
>
>
Re: How work around OpenJPA-1365
Posted by Michael Simons <mi...@optitool.de>.
Hello Mike,
My Model:
Product(1:*)LocalizedProduct
Product(1:*)ProductDisplayOption
ProductDisplayOption extends DisplayOption
I've setup a FetchPlan with this:
@FetchGroups({
@FetchGroup(name="productDetails", attributes={
@FetchAttribute(name="locals"),
@FetchAttribute(name="displayOptions")
})
})
public class Product implements java.io.Serializable, SingleLocalized, Customizable {
/** Localizations */
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="product")
private Set<LocalizedProduct> locals = new HashSet<LocalizedProduct> (13);
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="product")
private Set<ProductDisplayOption> displayOptions = new HashSet<ProductDisplayOption> (113);
...
}
When running the following code:
private void retrieveProducts (ISentinel cache) {
Query query = em.createQuery ("SELECT p from Product p");
OpenJPAQuery oq = OpenJPAPersistence.cast (query);
oq.getFetchPlan ().setMaxFetchDepth (10).addFetchGroup ("productDetails");
for (Object obj : query.getResultList ()) {
...
}
}
Unfortunately the produced SQL looks like this:
SELECT t0.product_id, t0.jdo_version, t0.alt_ex_id, t0.density, t0.ext_id, t0.indefinite FROM
product t0
SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version, t1.display_color, ...
FROM product_display_option t0 INNER JOIN display_option t1 ON t0.display_option_id =
t1.display_option_id
WHERE t0.product_id = ?
SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0 WHERE t0.product_id = ?
SELECT t1.display_option_id, t0.display_option_id, t1.jdo_version, t1.display_color, ...
FROM product_display_option t0 INNER JOIN display_option t1 ON t0.display_option_id =
t1.display_option_id
WHERE t0.product_id = ?
SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0 WHERE t0.product_id = ?
... // the second and third statement are repeated for every record in the product table.
What I would expect (and got with the formerly used JDO Impl) is like this:
SELECT ...
FROM product t0
LEFT JOIN product_display_option t1 on t1.product_id = t0.product_id
JOIN display_option t2 on t2.display_option_id = t1.display_option_id;
SELECT ...
FROM product t0
LEFT JOIN localized_product t1 on t1.product_id = t0.product_id;
---
So how do I convince OpenJPA to join the tables when using fetchplan?
When I run the following code instead without a fetchplan:
private void retrieveProducts (ISentinel cache) {
Query query = em.createQuery (
"SELECT p from Product p JOIN FETCH p.displayOptions JOIN FETCH p.locals");
for (Object obj : query.getResultList ()) {
...
}
}
The produced SQL looks like (what's more like what I would have expected, but still produces too
much "single selects"):
SELECT ...
FROM product t0
INNER JOIN product_display_option t1 ON t0.product_id = t1.product_id
INNER JOIN display_option t2 ON t1.display_option_id = t2.display_option_id
SELECT t0.localized_product_id, t0.jdo_version, t0.description, t0.lnguage_id, t0.product_id
FROM localized_product t0
WHERE t0.product_id = ?
... // The last statement again is executed for every record in the product table.
How can I reduce the number of statements generated?
kind regards,
Michael
Michael Dick schrieb:
> Hi Michael,
>
> Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to take
> a look at that. It probably happens if the query does not fetch records from
> the database. If that's what you're seeing then I just need to update the
> JIRA with that info.
>
> I'm not terribly familiar with JDO. I was under the impression that
> OpenJPA's FetchPlans were identical (or nearly identical) to those in JDO.
>
> To use my example (Employee and Manager) you would do something like this :
> OpenJPAEntityManager em = getEntityManager();
>
> OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
> q.getFetchPlan().addField(Manager.class, "employees");
>
> List<Manager> managers = q.getResultList();
>
> There's a lot more in the manual about it, I'd start looking at [1].
>
> [1]
> http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
>
> Hope this helps,
> -mike
>
>
> On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <michael.simons@optitool.de
>> wrote:
>
>> Hi Mike,
>>
>> Do you mean fetchplan like described in the manual in chapter 1.7.4?
>> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
>> there a description, how
>> to do that?
>>
>> kind regards,
>> Michael
>>
>> Michael Dick schrieb:
>>> Hi all,
>>>
>>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
>> correct
>>> behavior. If you don't want the duplicates to be returned you should use
>> the
>>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>>>
>>> There are several outstanding issues though (which I'm working on):
>>>
>>> 1.) OPENJPA-894: When results are returned from the database OpenJPA
>>> automatically removes duplicates from the list. If the results are
>> fetched
>>> from memory the duplicates reappear.
>>>
>>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
>>> keyword doesn't work. This is because OpenJPA merely prepends the
>> DISTINCT
>>> keyword to the SQL generated which doesn't work if you're selecting
>> across
>>> multiple tables. Instead we need to filter the result list after
>> retrieving
>>> from the database.
>>>
>>> There are two proposed fixes for OPENJPA-894 each of which have some
>>> drawbacks.
>>>
>>> 3a.) Mike's fix : supports pagination but does not support multiple JOIN
>>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
>>> FETCH m.projects returns the wrong number of results).
>>>
>>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
>>> support pagination (ie query.setMaxResults(), query.setFirstResult()
>> doesn't
>>> page forward as expected).
>>>
>>> So there is work being done, but it's turned out to be a very ticklish
>> issue
>>> to solve.
>>>
>>> At the risk of muddying the waters a bit if you're migrating a JDO
>>> application have you considered using OpenJPA's FetchPlan implementation
>> to
>>> eagerly load some fields? Over medium - large datasets I've found this to
>> be
>>> significantly faster than using a JOIN FETCH, but YMMV.
>>>
>>> Hope this helps,
>>>
>>> -mike
>>>
>>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
>> wrote:
>>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>>>> <mi...@optitool.de>wrote:
>>>>
>>>>
>>>>> You state, that you're query with distinct and join fetch does work
>>>>> properly. But this would
>>>>> mean OpenJPA-1365 doesn't occur, does it?
>>>>>
>>>> The JIRA states:
>>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>>>
>>>> So 1365 does not occur unless you've patched your code such that 894 is
>>>> fixed. What version of OpenJPA are you using and do you have any patches
>> in
>>>> place?
>>>>
>>>>
>>>>> When we call "select a from A a join fetch B" we get n instances of A,
>>>> with
>>>>> n = numbers of
>>>>> A-B-associations.
>>>>>
>>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
>>>> distinct rows of A which sounds like what you (and I) want but is
>> improper.
>>>> 894 shows that a second run of the query in the same EntityManager
>> yields
>>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>>>>
>>>> select distinct a from A a join fetch a.bs
>>>> ?
>>>>
>>>> How about these two:
>>>> select a from A a left join fetch a.bs
>>>> select distinct a from A a left join fetch a.bs
>>>>
>>>> I don't want to give the impression that I'm an expert on the matter,
>> just
>>>> that I've dealt with this issue and I want to be sure my understanding
>> of
>>>> things is accurate.
>>>>
>>>> --
>>>> Daryl Stultz
>>>> _____________________________________
>>>> 6 Degrees Software and Consulting, Inc.
>>>> http://www.6degrees.com
>>>> mailto:daryl@6degrees.com
Re: How work around OpenJPA-1365
Posted by Michael Dick <mi...@gmail.com>.
Hi Michael,
Regarding seeing OPENJPA-1365 on 'vanilla' OpenJPA 1.2.1, I'll have to take
a look at that. It probably happens if the query does not fetch records from
the database. If that's what you're seeing then I just need to update the
JIRA with that info.
I'm not terribly familiar with JDO. I was under the impression that
OpenJPA's FetchPlans were identical (or nearly identical) to those in JDO.
To use my example (Employee and Manager) you would do something like this :
OpenJPAEntityManager em = getEntityManager();
OpenJPAQuery q = em.createQuery("SELECT m from Manager m ");
q.getFetchPlan().addField(Manager.class, "employees");
List<Manager> managers = q.getResultList();
There's a lot more in the manual about it, I'd start looking at [1].
[1]
http://openjpa.apache.org/builds/1.2.1/apache-openjpa-1.2.1/docs/manual/manual.html#ref_guide_fetch
Hope this helps,
-mike
On Tue, Nov 17, 2009 at 11:02 AM, Michael Simons <michael.simons@optitool.de
> wrote:
> Hi Mike,
>
> Do you mean fetchplan like described in the manual in chapter 1.7.4?
> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
> there a description, how
> to do that?
>
> kind regards,
> Michael
>
> Michael Dick schrieb:
> > Hi all,
> >
> > Daryl's correct, the duplicates returned by a JOIN FETCH clause are
> correct
> > behavior. If you don't want the duplicates to be returned you should use
> the
> > DISTINCT keyword and OpenJPA will remove duplicates from the list.
> >
> > There are several outstanding issues though (which I'm working on):
> >
> > 1.) OPENJPA-894: When results are returned from the database OpenJPA
> > automatically removes duplicates from the list. If the results are
> fetched
> > from memory the duplicates reappear.
> >
> > 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
> > keyword doesn't work. This is because OpenJPA merely prepends the
> DISTINCT
> > keyword to the SQL generated which doesn't work if you're selecting
> across
> > multiple tables. Instead we need to filter the result list after
> retrieving
> > from the database.
> >
> > There are two proposed fixes for OPENJPA-894 each of which have some
> > drawbacks.
> >
> > 3a.) Mike's fix : supports pagination but does not support multiple JOIN
> > FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
> > FETCH m.projects returns the wrong number of results).
> >
> > 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
> > support pagination (ie query.setMaxResults(), query.setFirstResult()
> doesn't
> > page forward as expected).
> >
> > So there is work being done, but it's turned out to be a very ticklish
> issue
> > to solve.
> >
> > At the risk of muddying the waters a bit if you're migrating a JDO
> > application have you considered using OpenJPA's FetchPlan implementation
> to
> > eagerly load some fields? Over medium - large datasets I've found this to
> be
> > significantly faster than using a JOIN FETCH, but YMMV.
> >
> > Hope this helps,
> >
> > -mike
> >
> > On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com>
> wrote:
> >
> >> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> >> <mi...@optitool.de>wrote:
> >>
> >>
> >>> You state, that you're query with distinct and join fetch does work
> >>> properly. But this would
> >>> mean OpenJPA-1365 doesn't occur, does it?
> >>>
> >> The JIRA states:
> >> This issue occurs if the proposed fix for OPENJPA-894 is in place.
> >>
> >> So 1365 does not occur unless you've patched your code such that 894 is
> >> fixed. What version of OpenJPA are you using and do you have any patches
> in
> >> place?
> >>
> >>
> >>> When we call "select a from A a join fetch B" we get n instances of A,
> >> with
> >>> n = numbers of
> >>> A-B-associations.
> >>>
> >> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
> >> distinct rows of A which sounds like what you (and I) want but is
> improper.
> >> 894 shows that a second run of the query in the same EntityManager
> yields
> >> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
> >>
> >> select distinct a from A a join fetch a.bs
> >> ?
> >>
> >> How about these two:
> >> select a from A a left join fetch a.bs
> >> select distinct a from A a left join fetch a.bs
> >>
> >> I don't want to give the impression that I'm an expert on the matter,
> just
> >> that I've dealt with this issue and I want to be sure my understanding
> of
> >> things is accurate.
> >>
> >> --
> >> Daryl Stultz
> >> _____________________________________
> >> 6 Degrees Software and Consulting, Inc.
> >> http://www.6degrees.com
> >> mailto:daryl@6degrees.com
> >>
> >
>
>
>
Re: How work around OpenJPA-1365
Posted by Pinaki Poddar <pp...@apache.org>.
Hi,
OpenJPA FetchPlan is a more flexible and clear solution than JPA FETCH
JOIN, imo. As you are moving from JDO, you will find FetchPlan familiar as
it was originally part of JDO specification. The quick way to
control complex fetch is to
EntityManager em = ...;
OpenJPAEntityManager oem = OpenJPAPersistence.cast(em);
FetchPlan plan = oem.getFetchPlan();
// now on the plan interface you can add/reove named groups of attributes
(called Fetch Group), add/remove specific fields, set maximum depth of
traversal or recursion, or locking mode etc.
Michael Simons wrote:
>
> Hi Mike,
>
> Do you mean fetchplan like described in the manual in chapter 1.7.4?
> Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is
> there a description, how
> to do that?
>
> kind regards,
> Michael
>
> Michael Dick schrieb:
>> Hi all,
>>
>> Daryl's correct, the duplicates returned by a JOIN FETCH clause are
>> correct
>> behavior. If you don't want the duplicates to be returned you should use
>> the
>> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>>
>> There are several outstanding issues though (which I'm working on):
>>
>> 1.) OPENJPA-894: When results are returned from the database OpenJPA
>> automatically removes duplicates from the list. If the results are
>> fetched
>> from memory the duplicates reappear.
>>
>> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
>> keyword doesn't work. This is because OpenJPA merely prepends the
>> DISTINCT
>> keyword to the SQL generated which doesn't work if you're selecting
>> across
>> multiple tables. Instead we need to filter the result list after
>> retrieving
>> from the database.
>>
>> There are two proposed fixes for OPENJPA-894 each of which have some
>> drawbacks.
>>
>> 3a.) Mike's fix : supports pagination but does not support multiple JOIN
>> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
>> FETCH m.projects returns the wrong number of results).
>>
>> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
>> support pagination (ie query.setMaxResults(), query.setFirstResult()
>> doesn't
>> page forward as expected).
>>
>> So there is work being done, but it's turned out to be a very ticklish
>> issue
>> to solve.
>>
>> At the risk of muddying the waters a bit if you're migrating a JDO
>> application have you considered using OpenJPA's FetchPlan implementation
>> to
>> eagerly load some fields? Over medium - large datasets I've found this to
>> be
>> significantly faster than using a JOIN FETCH, but YMMV.
>>
>> Hope this helps,
>>
>> -mike
>>
>> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com> wrote:
>>
>>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>>> <mi...@optitool.de>wrote:
>>>
>>>
>>>> You state, that you're query with distinct and join fetch does work
>>>> properly. But this would
>>>> mean OpenJPA-1365 doesn't occur, does it?
>>>>
>>> The JIRA states:
>>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>>
>>> So 1365 does not occur unless you've patched your code such that 894 is
>>> fixed. What version of OpenJPA are you using and do you have any patches
>>> in
>>> place?
>>>
>>>
>>>> When we call "select a from A a join fetch B" we get n instances of A,
>>> with
>>>> n = numbers of
>>>> A-B-associations.
>>>>
>>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
>>> distinct rows of A which sounds like what you (and I) want but is
>>> improper.
>>> 894 shows that a second run of the query in the same EntityManager
>>> yields
>>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>>>
>>> select distinct a from A a join fetch a.bs
>>> ?
>>>
>>> How about these two:
>>> select a from A a left join fetch a.bs
>>> select distinct a from A a left join fetch a.bs
>>>
>>> I don't want to give the impression that I'm an expert on the matter,
>>> just
>>> that I've dealt with this issue and I want to be sure my understanding
>>> of
>>> things is accurate.
>>>
>>> --
>>> Daryl Stultz
>>> _____________________________________
>>> 6 Degrees Software and Consulting, Inc.
>>> http://www.6degrees.com
>>> mailto:daryl@6degrees.com
>>>
>>
>
>
>
>
-----
Pinaki
--
View this message in context: http://n2.nabble.com/How-work-around-OpenJPA-1365-tp4017737p4023270.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: How work around OpenJPA-1365
Posted by Michael Simons <mi...@optitool.de>.
Hi Mike,
Do you mean fetchplan like described in the manual in chapter 1.7.4?
Or is there a possibility to use JDO-FetchPlan with OpenJPA? If so, is there a description, how
to do that?
kind regards,
Michael
Michael Dick schrieb:
> Hi all,
>
> Daryl's correct, the duplicates returned by a JOIN FETCH clause are correct
> behavior. If you don't want the duplicates to be returned you should use the
> DISTINCT keyword and OpenJPA will remove duplicates from the list.
>
> There are several outstanding issues though (which I'm working on):
>
> 1.) OPENJPA-894: When results are returned from the database OpenJPA
> automatically removes duplicates from the list. If the results are fetched
> from memory the duplicates reappear.
>
> 2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
> keyword doesn't work. This is because OpenJPA merely prepends the DISTINCT
> keyword to the SQL generated which doesn't work if you're selecting across
> multiple tables. Instead we need to filter the result list after retrieving
> from the database.
>
> There are two proposed fixes for OPENJPA-894 each of which have some
> drawbacks.
>
> 3a.) Mike's fix : supports pagination but does not support multiple JOIN
> FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
> FETCH m.projects returns the wrong number of results).
>
> 3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
> support pagination (ie query.setMaxResults(), query.setFirstResult() doesn't
> page forward as expected).
>
> So there is work being done, but it's turned out to be a very ticklish issue
> to solve.
>
> At the risk of muddying the waters a bit if you're migrating a JDO
> application have you considered using OpenJPA's FetchPlan implementation to
> eagerly load some fields? Over medium - large datasets I've found this to be
> significantly faster than using a JOIN FETCH, but YMMV.
>
> Hope this helps,
>
> -mike
>
> On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
>> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
>> <mi...@optitool.de>wrote:
>>
>>
>>> You state, that you're query with distinct and join fetch does work
>>> properly. But this would
>>> mean OpenJPA-1365 doesn't occur, does it?
>>>
>> The JIRA states:
>> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>>
>> So 1365 does not occur unless you've patched your code such that 894 is
>> fixed. What version of OpenJPA are you using and do you have any patches in
>> place?
>>
>>
>>> When we call "select a from A a join fetch B" we get n instances of A,
>> with
>>> n = numbers of
>>> A-B-associations.
>>>
>> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
>> distinct rows of A which sounds like what you (and I) want but is improper.
>> 894 shows that a second run of the query in the same EntityManager yields
>> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>>
>> select distinct a from A a join fetch a.bs
>> ?
>>
>> How about these two:
>> select a from A a left join fetch a.bs
>> select distinct a from A a left join fetch a.bs
>>
>> I don't want to give the impression that I'm an expert on the matter, just
>> that I've dealt with this issue and I want to be sure my understanding of
>> things is accurate.
>>
>> --
>> Daryl Stultz
>> _____________________________________
>> 6 Degrees Software and Consulting, Inc.
>> http://www.6degrees.com
>> mailto:daryl@6degrees.com
>>
>
Re: How work around OpenJPA-1365
Posted by Michael Dick <mi...@gmail.com>.
Hi all,
Daryl's correct, the duplicates returned by a JOIN FETCH clause are correct
behavior. If you don't want the duplicates to be returned you should use the
DISTINCT keyword and OpenJPA will remove duplicates from the list.
There are several outstanding issues though (which I'm working on):
1.) OPENJPA-894: When results are returned from the database OpenJPA
automatically removes duplicates from the list. If the results are fetched
from memory the duplicates reappear.
2.) OPENJPA-1365: After you apply the fix for OPENJPA-894 the distinct
keyword doesn't work. This is because OpenJPA merely prepends the DISTINCT
keyword to the SQL generated which doesn't work if you're selecting across
multiple tables. Instead we need to filter the result list after retrieving
from the database.
There are two proposed fixes for OPENJPA-894 each of which have some
drawbacks.
3a.) Mike's fix : supports pagination but does not support multiple JOIN
FETCH statements (ie SELECT m FROM Manager m JOIN FETCH m.employees JOIN
FETCH m.projects returns the wrong number of results).
3b.) Fay's fix : supported multiple JOIN FETCH statements, but does not
support pagination (ie query.setMaxResults(), query.setFirstResult() doesn't
page forward as expected).
So there is work being done, but it's turned out to be a very ticklish issue
to solve.
At the risk of muddying the waters a bit if you're migrating a JDO
application have you considered using OpenJPA's FetchPlan implementation to
eagerly load some fields? Over medium - large datasets I've found this to be
significantly faster than using a JOIN FETCH, but YMMV.
Hope this helps,
-mike
On Tue, Nov 17, 2009 at 8:48 AM, Daryl Stultz <da...@6degrees.com> wrote:
> On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
> <mi...@optitool.de>wrote:
>
>
> > You state, that you're query with distinct and join fetch does work
> > properly. But this would
> > mean OpenJPA-1365 doesn't occur, does it?
> >
>
> The JIRA states:
> This issue occurs if the proposed fix for OPENJPA-894 is in place.
>
> So 1365 does not occur unless you've patched your code such that 894 is
> fixed. What version of OpenJPA are you using and do you have any patches in
> place?
>
>
> > When we call "select a from A a join fetch B" we get n instances of A,
> with
> > n = numbers of
> > A-B-associations.
> >
>
> This is the correct behavior. I have found with OpenJPA 1.2.1, I get
> distinct rows of A which sounds like what you (and I) want but is improper.
> 894 shows that a second run of the query in the same EntityManager yields
> duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
>
> select distinct a from A a join fetch a.bs
> ?
>
> How about these two:
> select a from A a left join fetch a.bs
> select distinct a from A a left join fetch a.bs
>
> I don't want to give the impression that I'm an expert on the matter, just
> that I've dealt with this issue and I want to be sure my understanding of
> things is accurate.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> mailto:daryl@6degrees.com
>
Re: How work around OpenJPA-1365
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Nov 17, 2009 at 9:27 AM, Michael Simons
<mi...@optitool.de>wrote:
> You state, that you're query with distinct and join fetch does work
> properly. But this would
> mean OpenJPA-1365 doesn't occur, does it?
>
The JIRA states:
This issue occurs if the proposed fix for OPENJPA-894 is in place.
So 1365 does not occur unless you've patched your code such that 894 is
fixed. What version of OpenJPA are you using and do you have any patches in
place?
> When we call "select a from A a join fetch B" we get n instances of A, with
> n = numbers of
> A-B-associations.
>
This is the correct behavior. I have found with OpenJPA 1.2.1, I get
distinct rows of A which sounds like what you (and I) want but is improper.
894 shows that a second run of the query in the same EntityManager yields
duplicates A's (with LEFT JOIN FETCH). What happens when you do this:
select distinct a from A a join fetch a.bs
?
How about these two:
select a from A a left join fetch a.bs
select distinct a from A a left join fetch a.bs
I don't want to give the impression that I'm an expert on the matter, just
that I've dealt with this issue and I want to be sure my understanding of
things is accurate.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: How work around OpenJPA-1365
Posted by Michael Simons <mi...@optitool.de>.
Daryl Stultz schrieb:
> On Tue, Nov 17, 2009 at 5:27 AM, Michael Simons
> <mi...@optitool.de>wrote:
>
>> Hello,
>>
>> OpenJPA-1365: DISTINCT keyword has no effect with JOIN FETCH queries
>>
>> We're porting a formerly JDO based application to JPA.
>> We wanted to heavily use JOIN FETCHs to reduce the number of SQL statements
>> generated.
>> We do not know how to get rid of those weird duplicates produced by a JPQL
>> statement including a
>> join fetch clause.
>>
>
> The duplicates are "proper" by the specification, just as would happen in a
> standard SQL join. 1365 is related to 894, neither of which are fixed. I
> originally wrote my join fetch queries with no DISTINCT until I ran into
> 894. Now with DISTINCT and join fetches I don't get duplicates. Can you give
> an example of a query and what it returns (and what you'd like it to
> return)?
>
Hello Daryl,
You state, that you're query with distinct and join fetch does work properly. But this would
mean OpenJPA-1365 doesn't occur, does it?
In our case we've got the problem when selecting from an N-M-relationship like:
class A {
Set<B> bs;
...
}
class B {
Set<A> as;
...
}
When we call "select a from A a join fetch B" we get n instances of A, with n = numbers of
A-B-associations.
kind regards,
Michael
Re: How work around OpenJPA-1365
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Nov 17, 2009 at 5:27 AM, Michael Simons
<mi...@optitool.de>wrote:
> Hello,
>
> OpenJPA-1365: DISTINCT keyword has no effect with JOIN FETCH queries
>
> We're porting a formerly JDO based application to JPA.
> We wanted to heavily use JOIN FETCHs to reduce the number of SQL statements
> generated.
> We do not know how to get rid of those weird duplicates produced by a JPQL
> statement including a
> join fetch clause.
>
The duplicates are "proper" by the specification, just as would happen in a
standard SQL join. 1365 is related to 894, neither of which are fixed. I
originally wrote my join fetch queries with no DISTINCT until I ran into
894. Now with DISTINCT and join fetches I don't get duplicates. Can you give
an example of a query and what it returns (and what you'd like it to
return)?
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com