openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL, ANY results in SQL syntax errors on some database systems
Date Fri, 23 May 2008 16:41:56 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-607?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12599422#action_12599422
] 

Catalina Wei commented on OPENJPA-607:
--------------------------------------

case 1. ALL and ANY :
apply to subquery only.  OpenJPA always pushdown subquery in enclosing parenthesis.
ALL or ANY operator puts additional pair of parenthesis.
The fix is to not putting in unneeded pair, because Sybase reports syntax
error for ANY or ALL subquery having the extra enclosing parenthesis.
case 2. Distinct function
first, do not confuse this DISTINCT function with SELECT DISTINCT.
SELECT DISTINCT will not got through the Distinct.class code.
The Distinct function is only associated with a COUNT function.
It is safe that given the fact  OpenJpa only pushdown  COUNT(DISTINCT one-item),
we can enclose one-item with or without parenthesis.
The fix is not to enclose one-time to get around syntax error reported by Informix.

So far, I have verified the fix against DB2, Oracle, Sybase, SQLServer and Informix.

Help needed to verify other back-end systems.

> Extraneous "( )" on DISTINCT, ALL, ANY results in  SQL syntax errors on some database
systems
> ---------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-607
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-607
>             Project: OpenJPA
>          Issue Type: Bug
>            Reporter: Catalina Wei
>            Assignee: Catalina Wei
>             Fix For: 1.2.0
>
>
> 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

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message