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

[catalina wei <ca...@gmail.com>]

Hi Mike,  repeating the same comment on OpenJPA-606
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 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 .

So far, I have verified DB2, Oracle, Sybase, SQLServer and Informix.
It is only the Informix reports syntax error if the distinct item is
enclosed with parenthesis.

Help needed to verify other back-end systems.



On Thu, May 22, 2008 at 1:03 AM, Michael Dick (JIRA) <ji...@apache.org>
wrote:

>
>    [
> https://issues.apache.org/jira/browse/OPENJPA-607?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12598937#action_12598937]
>
> Michael Dick commented on OPENJPA-607:
> --------------------------------------
>
> Hi Catalina,
>
> What's the benefit of leaving the parenthesis in? If they're truly
> extraneous I would think that the default would be to not use them unless
> they're needed.
>
> The code changes provided affect ALL, DISTINCT and ANY, but does the same
> problem occur with other UnaryOps?
>
> > 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
> >
> > 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.
>
>