Hi,
glassfish issue 148 describes a problem with an EJBQL query having an
exists subquery on derby
(
https://glassfish.dev.java.net/issues/show_bug.cgi?id=148):
SELECT c FROM Customer c WHERE
EXISTS (SELECT o FROM c.orders o WHERE ...)
The subquery selects entities which gets mapped to a SQL query having
multiple columns in the SELECT clause of the subquery and derby does not
support this.
The current idea is fixing this at the EJBQL parser level by mapping the
query to an equivalent EJBQL query:
SELECT c FROM Customer c WHERE
EXISTS (SELECT 1 FROM c.orders o where ...)
The above works for an EXISTS subquery, because it really does not
matter which particular values the subquery returns.
I checked the EJBQL spec for other usages of subqueries: IN-, ANY-,
SOME-, ALL- and comparison-expression allow subqueries, but only if they
return a numeric, string or datetime type. So these subqueries do not
have the same problem, unless EJB allows mapping such a field to
multiple columns. Is this possible?
So I will implement the above proposal to fix issue 148. Just as a heads
up: in case a future EJBQL extension allows subqueries in other places
e.g. COUNT(subquery) or comparison expressions including entity types,
the above fix does not work and we need to find a different solution.
Regards Michael