Hi Michael,
Sorry for the slow response. I was out of town last week and am just
getting caught up.
The fix looks good to me.
-Tom
Michael Bouschen wrote:
> Hi Tom,
>
> attached you find my fix for issue 2750 "To many bracket in subquery
> using MySQL": https://glassfish.dev.java.net/issues/show_bug.cgi?id=2750
>
> Today the generated SQL for an JPQL IN expression with a subquery
> includes multiple parenthesis:
> JPQL: SELECT ... WHERE o.id IN (SELECT c.id FROM ...)
> SQL: SELECT ... WHERE t0.ID IN ((SELECT DISTINCT t1.ID FROM ...))
>
> MySQL does not like this in case the subquery returns more than one
> result and throws a SQLException "Subquery returns more than 1 row". I
> think MySQL interprets one of the parenthesis as a tuple construction
> operator and complains that the subquery represents multiple values.
> We generate the same SQL for other databases (oracle and derby), but
> they seem to ignore the additional pair of parenthesis. The issue is
> that the in-ExpressionOperator generates the first pair of parenthesis
> and the subquery itself is always enclosed in parenthesis which adds a
> second pair.
>
> I added a new operator called inSubQuery to class ExpressionOperator
> which does not generate any parenthesis. Method Expression.in taking a
> ReportQuery maps to the new ExpressionOperator. I did a similar change
> for Expression method notIn taking a ReportQuery. You find the fix
> attached. Please have a look.
>
> Thanks!
>
> Regards Michael