[
https://issues.apache.org/jira/browse/OPENJPA-2841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17240837#comment-17240837
]
Petr Kadlec commented on OPENJPA-2841:
--------------------------------------
Unfortunately, I am really a stranger to OpenJPA implementation, so I
definitely don’t dare to attempt to fix such a complex piece of code.
> Broken superfluous joins with subqueries
> ----------------------------------------
>
> Key: OPENJPA-2841
> URL: https://issues.apache.org/jira/browse/OPENJPA-2841
> Project: OpenJPA
> Issue Type: Bug
> Components: query, sql
> Affects Versions: 2.4.2, 3.1.2
> Reporter: Petr Kadlec
> Priority: Major
>
> OpenJPA generates wrong SQL for a query with nested subqueries, causing the
> result to be wrong. My attempts to rewrite the query helped to get the
> correct results, even though the generated alternate queries seem not to be
> perfect. (There might a way to write them better.)
> The following query:
> {{select u from User u where u.email=:firstEmail and u<>:firstUser and exists
> (select o1 from Order o1 where o1.user=u and exists(select o2 from Order o2
> where o2.user=:firstUser and o1.productCode=o2.productCode))}}
> generates the following SQL:
> {{SELECT t0.id, t0.email, t0.name FROM users t0 WHERE (t0.email = ? AND t0.id
> <> ? AND EXISTS (SELECT t1.id FROM orders t1, orders t3 WHERE (t1.user_id =
> t0.id AND EXISTS (SELECT t2.id FROM orders t2 WHERE (t2.user_id = ? AND
> t3.product_code = t2.product_code)))))}}
> Notice the superfluous join to “orders t3” which causes the whole query
> result to be wrong.
> When the query is modified to replace the second nested subquery with a join
> to
> {{select u from User u where u.email=:firstEmail and u<>:firstUser and exists
> (select o1 from Order o1, Order o2 where o1.user=u and o2.user=:firstUser and
> o1.productCode=o2.productCode)}}
> the generated SQL is:
> {{SELECT t0.id, t0.email, t0.name FROM users t0 WHERE (t0.email = ? AND t0.id
> <> ? AND EXISTS (SELECT t3.id FROM users t1 CROSS JOIN orders t2, orders t3
> WHERE (t3.user_id = t0.id AND t2.user_id = ? AND t3.product_code =
> t2.product_code)))}}
> which contains a strange construction of doing a cross join to „users t1“
> (which is completely unused later). Still, this seems to return the correct
> result, at least. (In my short testing; to be honest, I do not understand
> what exactly is this construction doing, and if its semantics is better,
> equal, or worse than not doing the cross join.) However, the unnecessary join
> does seem to affect performance.
> Finally, when I rewrote the query from the other side, to:
> {{select o from Order o join o.user u where u.email=:firstEmail and
> u<>:firstUser and o.productCode in (select o1.productCode from Order o1 where
> o1.user=:firstUser)}}
> The resulting SQL is:
> {{SELECT t0.id, t0.product_code, t3.id, t3.email, t3.name FROM orders t0
> INNER JOIN users t1 ON t0.user_id = t1.id LEFT OUTER JOIN users t3 ON
> t0.user_id = t3.id WHERE (t1.email = ? AND t0.user_id <> ? AND
> t0.product_code IN (SELECT t2.product_code FROM orders t2 WHERE (t2.user_id =
> ?)))}}
> Which generates correct results, even though it _still_ contains a
> superfluous outer join to „users t3“ (used only in the result projection)
> which is identical to the previous inner join, which again affects
> performance.
> A complete reproducible project is available [at my
> Github|https://github.com/mormegil-cz/openjpa-bug-repro].
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
