You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2008/05/22 09:03:55 UTC
[jira] Created: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL,
ANY results in SQL syntax errors on some database 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
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.
Re: [jira] Commented: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL, ANY results in SQL syntax errors on some database systems
Posted by 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.
>
>
[jira] Commented: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL,
ANY results in SQL syntax errors on some database systems
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL,
ANY results in SQL syntax errors on some database systems
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Resolved: (OPENJPA-607) Extraneous "( )" on DISTINCT, ALL,
ANY results in SQL syntax errors on some database systems
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-607?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei resolved OPENJPA-607.
----------------------------------
Resolution: Fixed
Fix Version/s: 1.2.0
Assignee: Catalina Wei
This problem is fixed under r659027.
> 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.