You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2008/03/27 22:43:24 UTC
[jira] Created: (OPENJPA-547) INNER JOIN Fetch query incorrectly
generates LEFT join SQL
INNER JOIN Fetch query incorrectly generates LEFT join SQL
-----------------------------------------------------------
Key: OPENJPA-547
URL: https://issues.apache.org/jira/browse/OPENJPA-547
Project: OpenJPA
Issue Type: Bug
Components: query
Reporter: Catalina Wei
Fix For: 1.0.3, 1.1.0
INNER JOIN FETCH should not generate SQL LEFT join.
Example:
lineitems (ToMany, FetchType=LAZY)
Order <===================================> OrderItem
order (ToOne, FetchType=LAZY)
For Query: select o from Order left join fetch o.lineitems
SQL generated
for DB2:
SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity
FROM TORDER t0 LEFT OUTER JOIN TORDERITEM t1 ON t0.oid = t1.ORDER_OID ORDER BY t1.ORDER_OID ASC
for Oracle:
SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity FROM TORDER t0, TORDERITEM t1 WHERE t0.oid = t1.ORDER_OID(+) ORDER BY t1.ORDER_OID ASC
The above SQL pushdown looked good.
However, if left join fetch is changed to inner join fetch:
select o from Order o inner join fetch o.lineitems
We are generating extra redundant LEFT joins:
for DB2:
SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, 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 Oracle:
SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, t2.quantity FROM TORDER t0, TORDERITEM t1, TORDERITEM t2 WHERE t0.oid = t1.ORDER_OID AND t0.oid = t2.ORDER_OID(+) ORDER BY t2.ORDER_OID ASC
Why do we generate the extra LEFT joins even for quereis explicitly requesting INNER join fetch ?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (OPENJPA-547) INNER JOIN Fetch query incorrectly
generates LEFT join SQL
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei reassigned OPENJPA-547:
------------------------------------
Assignee: Catalina Wei
> INNER JOIN Fetch query incorrectly generates LEFT join SQL
> -----------------------------------------------------------
>
> Key: OPENJPA-547
> URL: https://issues.apache.org/jira/browse/OPENJPA-547
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: Catalina Wei
> Assignee: Catalina Wei
> Fix For: 1.0.3, 1.1.0
>
>
> INNER JOIN FETCH should not generate SQL LEFT join.
> Example:
> lineitems (ToMany, FetchType=LAZY)
> Order <===================================> OrderItem
> order (ToOne, FetchType=LAZY)
> For Query: select o from Order left join fetch o.lineitems
> SQL generated
> for DB2:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity
> FROM TORDER t0 LEFT OUTER JOIN TORDERITEM t1 ON t0.oid = t1.ORDER_OID ORDER BY t1.ORDER_OID ASC
> for Oracle:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity FROM TORDER t0, TORDERITEM t1 WHERE t0.oid = t1.ORDER_OID(+) ORDER BY t1.ORDER_OID ASC
> The above SQL pushdown looked good.
> However, if left join fetch is changed to inner join fetch:
> select o from Order o inner join fetch o.lineitems
> We are generating extra redundant LEFT joins:
> for DB2:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, 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 Oracle:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, t2.quantity FROM TORDER t0, TORDERITEM t1, TORDERITEM t2 WHERE t0.oid = t1.ORDER_OID AND t0.oid = t2.ORDER_OID(+) ORDER BY t2.ORDER_OID ASC
> Why do we generate the extra LEFT joins even for quereis explicitly requesting INNER join fetch ?
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (OPENJPA-547) INNER JOIN Fetch query incorrectly
generates LEFT join SQL
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei closed OPENJPA-547.
--------------------------------
Resolution: Fixed
> INNER JOIN Fetch query incorrectly generates LEFT join SQL
> -----------------------------------------------------------
>
> Key: OPENJPA-547
> URL: https://issues.apache.org/jira/browse/OPENJPA-547
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Reporter: Catalina Wei
> Assignee: Catalina Wei
> Fix For: 1.0.3, 1.1.0
>
>
> INNER JOIN FETCH should not generate SQL LEFT join.
> Example:
> lineitems (ToMany, FetchType=LAZY)
> Order <===================================> OrderItem
> order (ToOne, FetchType=LAZY)
> For Query: select o from Order left join fetch o.lineitems
> SQL generated
> for DB2:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity
> FROM TORDER t0 LEFT OUTER JOIN TORDERITEM t1 ON t0.oid = t1.ORDER_OID ORDER BY t1.ORDER_OID ASC
> for Oracle:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t1.ORDER_OID, t1.lid, t1.version, t1.cost, t1.quantity FROM TORDER t0, TORDERITEM t1 WHERE t0.oid = t1.ORDER_OID(+) ORDER BY t1.ORDER_OID ASC
> The above SQL pushdown looked good.
> However, if left join fetch is changed to inner join fetch:
> select o from Order o inner join fetch o.lineitems
> We are generating extra redundant LEFT joins:
> for DB2:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, 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 Oracle:
> SELECT t0.oid, t0.version, t0.amount, t0.delivered, t2.ORDER_OID, t2.lid, t2.version, t2.cost, t2.quantity FROM TORDER t0, TORDERITEM t1, TORDERITEM t2 WHERE t0.oid = t1.ORDER_OID AND t0.oid = t2.ORDER_OID(+) ORDER BY t2.ORDER_OID ASC
> Why do we generate the extra LEFT joins even for quereis explicitly requesting INNER join fetch ?
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.