[OPENJPA-607] Extraneous "( )" on DISTINCT, ALL, ANY results in SQL syntax errors on some database systems

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.2.0
• Assignee: Catalina Wei
• Reporter: Catalina Wei
• Created: 22/May/08 07:03
• Updated: 09/Mar/10 18:32
• Resolved: 22/May/08 15:58

Description

In testing various back-end database systems such as Sybase, Informix. there are cases where the extra pair of () resulted in SQL Syntax error:
For example,
1. in generating ALL, ANY subquery, there is extraneous pair of "( )" that are tolerated by most systems such as DB2, Oracle but not Sybase.
WHERE (t0.DEPT_DEPTNO = ANY ((SELECT t1.deptno FROM DeptBean t1 WHERE (t1.deptno = ? )))

2.In the case of DISTINCT function, Informix reports syntax error when argument/s of the DISTINCT is/are parenthesized:
SELECT COUNT(DISTINCT (t1.name)) FROM EmpBean t1