[
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 Dies,
Comment inline:
Dies Koper wrote:
Hi Tom,
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("id");
to:
subQuery.addAttribute("one", new ConstantExpression(new Integer(1),
subQuery.getExpressionBuilder()));
That worked, thanks.
Was your suggestion to change the test this way, or use isSymfoware()?
It is fine to just change the test.
-Tom
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):
SELECT COUNT(t0.TYPE)
FROM CMP3_PHONENUMBER t0, CMP3_SALARY t2, CMP3_EMPLOYEE t1
WHERE (
(t2.EMP_ID = t1.EMP_ID) AND
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 ?)
) 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?
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
now passes.
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.
Thanks,
Dies
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev
