Re: [eclipselink-dev] t0 not in FROM clause in generated SQL for JUnitJPQLExamplesTestSuite's testExistsExpression
Dies Koper wrote:
To make the JUnitJPQLExamplesTestSuite#testExistsExpression test work
on Symfoware, try changing
the manager expression to the following in the subQuery.
Sorry, sent the wrong line... Try changing:
subQuery.addAttribute("one", new ConstantExpression(new Integer(1),
That worked, thanks.
Was your suggestion to change the test this way, or use isSymfoware()?
It is fine to just change the test.
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
It looks like you are right about this. Nonetheless, this test is
explicitly testing distinct, so I would be hesitant to alter the query
for other platforms.
You could do a check for isSymfoware and run the alternate query.
I have changed the source in ReportQuery.java accordingly and the test
But I don't quite understand yet how this generated SQL works without
DISTINCT, and whether this solution would always work, so I might make
it a restriction instead and let it fail on Symfoware with a DB error.
eclipselink-dev mailing list