Re: [eclipselink-dev] t0 not in FROM clause in generated SQL for JUnitJPQLExamplesTestSuite's testExistsExpression
To make the JUnitJPQLExamplesTestSuite#testExistsExpression test work on Symfoware, try changing
the manager expression to the following in the subQuery.
Expression managerExpression =
What is the change you want me to try exactly? (this line is already there)
For the JUnitJPQLComplexAggregateTestSuite#complexCountOnJoinedVariableCompositePK, I'm afraid we're going
to have to call this a limitation of symfoware. The query is
specifically testing COUNT DISTINCT, so we cannot do a SELECT 1 there
since we'll only get 1 result.
Are you sure?
JPQL: SELECT COUNT(DISTINCT p) FROM Employee e JOIN e.phoneNumbers p
WHERE e.lastName LIKE 'S%' GROUP BY e.lastName
So we are counting distinct phone numbers of all employees with a last
name starting with 'S'.
Generated SQL (I tried indenting it to make it more readable):
FROM CMP3_PHONENUMBER t0, CMP3_SALARY t2, CMP3_EMPLOYEE t1
(t2.EMP_ID = t1.EMP_ID) AND
SELECT DISTINCT t1.EMP_ID
FROM CMP3_PHONENUMBER t3
((t0.OWNER_ID = t3.OWNER_ID) AND (t0.TYPE = t3.TYPE))
AND (t1.L_NAME LIKE ?)
) AND (t2.EMP_ID = t1.EMP_ID)
) AND (t3.OWNER_ID = t1.EMP_ID)
GROUP BY t1.L_NAME
bind => [S%]
No matter how many rows the subquery finds, DISTINCT or not, it is used
with EXISTS so it is a simple TRUE (one or more employees) or FALSE (no
employees), isn't it?
When I run the query with 'SELECT 1' in the subquery I get the expected
result: [2, 5, 3].
In what kind of cases would you think this would return an incorrect result?
Dies Koper wrote:
The error occurs when we run the query that searches for the expected
result. It might be possible to alter that query.
I would like to focus on the next query and whether it is possible to
write reasonable SQL that works on Symfoware for that query:
"SELECT DISTINCT emp.id FROM Employee emp WHERE EXISTS ( SELECT
managedEmp.id FROM Employee managedEmp WHERE managedEmp = emp.manager)"
Can that JPQL execute successfully? (comment out: List expectedResult =
Yes, that worked.
If so, we can likely fix the test.
The key here is the WHERE clause (WHERE managedEmp = emp.manager). It
refers to the outer query from the subselect. Is there a way to write
Symfoware-legal SQL that allows that WHERE clause to work?
Well, the generated SQL worked:
SELECT DISTINCT t0.EMP_ID FROM CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE
(EXISTS (SELECT 1 FROM CMP3_SALARY t5, CMP3_EMPLOYEE t4, CMP3_SALARY
t3, CMP3_EMPLOYEE t2 WHERE ((((t2.EMP_ID = t4.MANAGER_EMP_ID) AND
(t0.EMP_ID = t4.EMP_ID)) AND (t3.EMP_ID = t2.EMP_ID)) AND (t5.EMP_ID =
t4.EMP_ID))) AND (t1.EMP_ID = t0.EMP_ID))
In the other test (JUnitJPQLComplexAggregateTestSuite's
complexCountOnJoinedVariableCompositePK) however, it is the third JPQL
query (marked with 'COUNT DISTINCT with inner join') that is failing.
The generated SQL is the following:
SELECT COUNT(t0.TYPE) FROM CMP3_PHONENUMBER t0, CMP3_SALARY t2,
CMP3_EMPLOYEE t1 WHERE (EXISTS (SELECT DISTINCT t1.EMP_ID FROM
CMP3_PHONENUMBER t3 WHERE (((((t0.OWNER_ID = t3.OWNER_ID) AND (t0.TYPE
= t3.TYPE)) AND (t1.L_NAME LIKE 'S%')) AND (t2.EMP_ID = t1.EMP_ID))
AND (t3.OWNER_ID = t1.EMP_ID))) AND (t2.EMP_ID = t1.EMP_ID)) GROUP BY
If I replace 'DISTINCT t1.EMP_ID' in the subquery with '1' and execute
it on the DB I get the expected results (2, 5, 3).
Do you have a suggestion for this test?
This test suite contains examples that come directly from the JPA
specification, so we cannot change the JPQL in the example. If we cannot
figure out a way to write the SQL for this JPQL, it looks like we will
just have to specify that subselects that refer to outer queries are not
supported when using Symfoware and disable this test for Symfoware.
BTW: I ran these tests in MySQL and subselects that refer to the outer
query appear to be supported (Updates and Deletes with those kinds of
subselects are not)
Dies Koper wrote:
Hi Tom, Andrei,
I think this is the issue we have discussed before related to
being unable to use a reference from the outer query in a subquery.
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
not an update or delete, I am not sure how MySQL is behaving, but
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
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 =
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.
Dies Koper wrote:
Another issue I found while running the JPA test set on Symfoware:
The test simply does a WHERE EXISTS with a subquery. The expected
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 =
However, the generated SQL statement (see below), has "t0.EMP_ID" in
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
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 =
Is this intended?
This query fails on Symfoware because of that reason.
Maybe other databases are more forgiving?