[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-dev] t0 not in FROM clause in generated SQL for JUnitJPQLExamplesTestSuite's testExistsExpression
|
Hi Tom, Andrei,
I think this is the issue we have discussed before related to Symfoware
being unable to use a reference from the outer query in a subquery. The
only other database I know of with this issue is MySQL - we generally
use temp tables to get around this for Updates and Deletes - if this is
not an update or delete, I am not sure how MySQL is behaving, but could
check if you can let me know what test is causing the issue.
It's in the Subject: JUnitJPQLExamplesTestSuite's testExistsExpression.
It also occurs in JUnitJPQLComplexAggregateTestSuite's
complexCountOnJoinedVariableCompositePK.
I thought I was seeing a different issue here and I wasn't sure if and
how they were related:
1. The issue described in this e-mail (outer reference not allowed).
2. Same table used in FROM clauses of main and subquery. This is the
issue I brought up back in September where Andrei suggested I try the
global temporary tables. E.g. 'CMP3_ORDER' below:
DELETE FROM CMP3_ORDER WHERE EXISTS(SELECT t1.ORDER_ID FROM
CMP3_CUSTOMER t0, CMP3_ORDER t1 WHERE ((t0.NAME = 'Karen McDonald') AND
(t0.CUST_ID = t1.CUSTOMER_CUST_ID)) AND t1.ORDER_ID = CMP3_ORDER.ORDER_ID)
Issue 1. only happens in these two tests, not in any of the other JPA
and Core tests. I suppose it would work if it did an 'EXISTS (SELECT 1
FROM ...)' instead?
Issue 2. happens in many tests in the JPA test set and six of the Core
tests for both update and delete queries. At the time I came up with an
alternative statement, but that only works well when linking tables with
single-column primary keys.
Cheers,
Dies
Dies Koper wrote:
Hi,
Another issue I found while running the JPA test set on Symfoware:
The test simply does a WHERE EXISTS with a subquery. The expected result
should be the same as the following query:
SELECT DISTINCT emp.id FROM Employee emp WHERE EXISTS ( SELECT
managedEmp.id FROM Employee managedEmp WHERE managedEmp = emp.manager)
However, the generated SQL statement (see below), has "t0.EMP_ID" in the
select list of the subquery, while t0 is not in its FROM clause.
SELECT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE (EXISTS
(SELECT t0.EMP_ID FROM CMP3_SALARY t5, CMP3_EMPLOYEE t4, CMP3_SALARY t3,
CMP3_EMPLOYEE t2 WHERE (((t2.EMP_ID = t4.EMP_ID) AND (t1.EMP_ID =
t0.EMP_ID)) AND (((t2.EMP_ID = t0.MANAGER_EMP_ID) AND (t3.EMP_ID =
t2.EMP_ID)) AND (t5.EMP_ID = t4.EMP_ID)))) AND (t1.EMP_ID = t0.EMP_ID))
Is this intended?
This query fails on Symfoware because of that reason.
Maybe other databases are more forgiving?
Thanks,
Dies
