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) <[EMAIL PROTECTED]> 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.
