openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei" <catalinas...@gmail.com>
Subject Re: [jira] Resolved: (OPENJPA-134) Extra unneeded SQL joins for OneToMany relationship with fetch type EAGER
Date Thu, 12 Apr 2007 01:00:59 GMT
Abe,
I have verified this fix with EagerFetchMode parallel and join. However,
there is an outstanding problem for the following query with inner join
fetch while left join fetch generates correct SQL; the inner join fetch
generates extra unneeded left join:

    select o from Order o inner join fetch o.lineitems

the generated SQL has an extra left join that causes the result set empty, I
will create a new JIRA issue for this problem:

1297 demo TRACE [main] openjpa.Query - Executing query: select o from Order
o inner join fetch o.lineitems

1297 demo TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1831234854>
executing prepstmnt 726281034 SELECT t0.oid, t0.version, t0.amount,
t0.customer_countryCode, t0.customer_id, t0.delivered, t2.order_oid, t2.lid,
t2.version, t2.cost, t2.part_partno, t2.quantity FROM TORDER t0 INNER JOIN
TORDERITEM t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN TORDERITEM t2 ON
t0.oid = t2.order_oid ORDER BY t2.order_oid ASC FOR READ ONLY
Catalina

On 4/11/07, Abe White (JIRA) <jira@apache.org> wrote:
>
>
>     [
> https://issues.apache.org/jira/browse/OPENJPA-134?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel]
>
> Abe White resolved OPENJPA-134.
> -------------------------------
>
>    Resolution: Fixed
>      Assignee:     (was: Abe White)
>
> Fixed the most egregious issue, which was the cyclic fetching of eager
> bidirectional relations.  Changed to cut off SELECTs when we're traversing
> the back-ptr to the owning side of a relation we've already fetched.  I'm
> not convinced all the other issues mentioned are bugs given the eager fetch
> settings used.  Please open new JIRAs for any individual issues that you
> believe remain.  Fixed in revision 527565.
>
> > Extra unneeded SQL joins for OneToMany relationship with fetch type
> EAGER
> >
> -------------------------------------------------------------------------
> >
> >                 Key: OPENJPA-134
> >                 URL: https://issues.apache.org/jira/browse/OPENJPA-134
> >             Project: OpenJPA
> >          Issue Type: Bug
> >          Components: sql
> >            Reporter: Catalina Wei
> >             Fix For: 0.9.8
> >
> >
> > Running JPAConfiguration default setting for EagerFetchMode (
> FetchModeValue.EAGER_PARALLEL),
> > the SQL generated is sub-optimal.
> > Consider the following entities:
> >                                      lineitems( OneToMany )
> >             Order  <===========================> OrderItem
> >                                     order ( ManyToOne )
> > Case 1:  why not combining 2 SQL to 1 SQL ?
> >
> =================================================================================================
> > Order.lineitmes(EAGER):
> > OrderItem.order(LAZY):
> > Executing query: select o from Order o
> > 2173  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> executing prepstmnt 1299336562
> > SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode,
> t0.customer_id, t0.delivered, t0.shipaddr FROM Order t0
> > 2213  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> [40 ms] spent
> > 2223  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> executing prepstmnt 1406424020
> > SELECT t0.oid, t1.lid, t1.version, t1.cost, t1.order_oid, t1.part_partno,
> t1.quantity FROM Order t0 INNER JOIN OrderItem t1 ON t0.oid = t1.order_oidORDER BY
> t0.oid ASC
> > Case 2: extra unneeded LEFT OUTER JOIN,  if eliminated, the selection
> aliase t2 should change to t1:
> >
> =============================================================================================
> > Order.lineitmes(EAGER):
> > OrderItem.order(LAZY):
> > Executing query: select o from Order o left join fetch o.lineitems
> > 2403  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1314410072> executing prepstmnt 1500797300
> > SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode,
> t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid,
> t2.version, t2.cost, t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER
> JOIN OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON
> t0.oid = t2.order_oid ORDER BY t2.order_oid ASC
> > Case  3: why not generating 1 SQL ?
> >
> ==================================================================================================
> > Order.lineitmes(EAGER):
> > OrderItem.order(EAGER):
> > Executing query: select o from Order o
> > 2343  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> executing prepstmnt 384833264 SELECT t0.oid, t0.version,
> t0.amount, t0.customer_countryCode, t0.customer_id, t0.delivered,
> t0.shipaddr FROM Order t0
> > 2383  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> [40 ms] spent
> > 2393  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> executing prepstmnt 1722705582
> > SELECT t0.oid, t1.lid, t1.version, t1.cost, t2.oid, t2.version,
> t2.amount, t2.customer_countryCode, t2.customer_id, t2.delivered,
> t2.shipaddr, t1.part_partno, t1.quantity FROM Order t0 INNER JOIN
> OrderItem t1 ON t0.oid = t1.order_oid LEFT OUTER JOIN Order t2 ON
> t1.order_oid = t2.oid ORDER BY t0.oid ASC
> > 2393  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> [0 ms] spent
> > 3134  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> executing prepstmnt 950548648
> > SELECT t0.lid, t0.version, t0.cost, t1.oid, t1.version, t1.amount,
> t1.customer_countryCode, t1.customer_id, t1.delivered, t1.shipaddr,
> t0.part_partno, t0.quantity FROM OrderItem t0 LEFT OUTER JOIN Order t1 ON
> t0.order_oid = t1.oid WHERE t0.order_oid = ? [params=(int) 88]
> > 3134  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1318342292> [0 ms] spent
> > Case 4:  duplicate selections and redundant joins
> >
> ==================================================================================================
> > Order.lineitmes(EAGER):
> > OrderItem.order(EAGER):
> > Executing query: select o from Order o left join fetch o.lineitems
> > 2273  demo  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn
> 1307463150> executing prepstmnt 1565154634
> > SELECT t0.oid, t0.version, t0.amount, t0.customer_countryCode,
> t0.customer_id, t0.delivered, t0.shipaddr, t2.order_oid, t2.lid,
> t2.version, t2.cost, t3.oid, t3.version, t3.amount,
> t3.customer_countryCode, t3.customer_id, t3.delivered, t3.shipaddr,
> t2.part_partno, t2.quantity FROM Order t0 LEFT OUTER JOIN OrderItem t1 ON
> t0.oid = t1.order_oid LEFT OUTER JOIN OrderItem t2 ON t0.oid =
> t2.order_oid LEFT OUTER JOIN Order t3 ON t2.order_oid = t3.oid ORDER BY
> t2.order_oid ASC
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message