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