You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Michael Dick (JIRA)" <ji...@apache.org> on 2008/05/21 11:43:55 UTC
[jira] Commented: (OPENJPA-606) InformixDictionary default setting
beaks many testcases
[ https://issues.apache.org/jira/browse/OPENJPA-606?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12598604#action_12598604 ]
Michael Dick commented on OPENJPA-606:
--------------------------------------
It seems that we can move some of the logic in Distinct.java into the DBDictionary. Or at least add a flag in the DBDictionary that indicates whether ( ) are needed. Leaving :
if (sel.getConfiguration().getDBDictionaryInstance().platform.indexOf("Informix") > -1) {
in the code is rather ugly.
> InformixDictionary default setting beaks many testcases
> -------------------------------------------------------
>
> Key: OPENJPA-606
> URL: https://issues.apache.org/jira/browse/OPENJPA-606
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 1.2.0
> Reporter: Catalina Wei
>
> In testing Informix backend store, there are many query test string failed:
> 1. parameter of a boolean value does not work, should use 't for true, 'f' for false
> WHERE (t1.isManager = ?) [params=(boolean) false]
>
> should generate WHERE (t1.isManager = ?) [params=(String) f]
> 2. SELECT COUNT(DISTINCT(t1.name)) reported syntax error [code=-201, state=42000]
> should generate COUNT(DISTINCT t1.name )
> 3. CROSS JOIN syntax error
>
> should generate JOIN with ON 1 = 1
>
> 4. There is no equivalent function for LOCATE. Informix users must create INSTR as a user defined function.
> 3. CONCAT function in '||' does not take parameter markers
> WHERE ((?||t0.name) LIKE ? ESCAPE '\')
> should generate CONCAT(?, t0.name)
--
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-606) InformixDictionary default setting beaks many testcases
Posted by catalina wei <ca...@gmail.com>.
Hi Mike,
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.
Catalina
On Thu, May 22, 2008 at 10:34 AM, Michael Dick <mi...@apache.org> wrote:
> Thanks Catalina,
>
> I made comments on OPENJPA-607 as well, but I can make them here too.
>
> The fix for 607 arbitrarily removes (...) for DISTINCT, ALL, and ANY
> regardless of the Database that's being used. Do we need to do the same
> thing for other UnaryOps?
>
> It's also a bit riskier than 606 was the change affects all databases. I'd
> rather add a flag to DBDictionary in case we find a database that does
> require the (...).
>
> Thanks,
>
> -Mike
>
>
> On Thu, May 22, 2008 at 5:21 PM, catalina wei <ca...@gmail.com>
> wrote:
>
> > Hi Mike,
> > Your comment is accepted.
> > A general solution for not placing extra "( )" for DISTINCT/ALL/ANY is
> > filed
> > under OPENJPA-607 and resolved.
> >
> > Catalina
> >
> > On 5/21/08, Michael Dick (JIRA) <ji...@apache.org> wrote:
> > >
> > >
> > > [
> > >
> >
> https://issues.apache.org/jira/browse/OPENJPA-606?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12598604#action_12598604
> > ]
> > >
> > > Michael Dick commented on OPENJPA-606:
> > > --------------------------------------
> > >
> > > It seems that we can move some of the logic in Distinct.java into the
> > > DBDictionary. Or at least add a flag in the DBDictionary that indicates
> > > whether ( ) are needed. Leaving :
> > > if
> > >
> >
> (sel.getConfiguration().getDBDictionaryInstance().platform.indexOf("Informix")
> > > > -1) {
> > >
> > > in the code is rather ugly.
> > >
> > > > InformixDictionary default setting beaks many testcases
> > > > -------------------------------------------------------
> > > >
> > > > Key: OPENJPA-606
> > > > URL:
> https://issues.apache.org/jira/browse/OPENJPA-606
> > > > Project: OpenJPA
> > > > Issue Type: Bug
> > > > Components: sql
> > > > Affects Versions: 1.2.0
> > > > Reporter: Catalina Wei
> > > >
> > > > In testing Informix backend store, there are many query test string
> > > failed:
> > > > 1. parameter of a boolean value does not work, should use 't for
> true,
> > > 'f' for false
> > > > WHERE (t1.isManager = ?) [params=(boolean) false]
> > > >
> > > > should generate WHERE (t1.isManager = ?) [params=(String) f]
> > > > 2. SELECT COUNT(DISTINCT(t1.name)) reported syntax error
> [code=-201,
> > > state=42000]
> > > > should generate COUNT(DISTINCT t1.name )
> > > > 3. CROSS JOIN syntax error
> > > >
> > > > should generate JOIN with ON 1 = 1
> > > >
> > > > 4. There is no equivalent function for LOCATE. Informix users must
> > > create INSTR as a user defined function.
> > > > 3. CONCAT function in '||' does not take parameter markers
> > > > WHERE ((?||t0.name) LIKE ? ESCAPE '\')
> > > > should generate CONCAT(?, t0.name)
> > >
> > >
> > > --
> > > 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-606) InformixDictionary default setting beaks many testcases
Posted by Michael Dick <mi...@apache.org>.
Thanks Catalina,
I made comments on OPENJPA-607 as well, but I can make them here too.
The fix for 607 arbitrarily removes (...) for DISTINCT, ALL, and ANY
regardless of the Database that's being used. Do we need to do the same
thing for other UnaryOps?
It's also a bit riskier than 606 was the change affects all databases. I'd
rather add a flag to DBDictionary in case we find a database that does
require the (...).
Thanks,
-Mike
On Thu, May 22, 2008 at 5:21 PM, catalina wei <ca...@gmail.com>
wrote:
> Hi Mike,
> Your comment is accepted.
> A general solution for not placing extra "( )" for DISTINCT/ALL/ANY is
> filed
> under OPENJPA-607 and resolved.
>
> Catalina
>
> On 5/21/08, Michael Dick (JIRA) <ji...@apache.org> wrote:
> >
> >
> > [
> >
> https://issues.apache.org/jira/browse/OPENJPA-606?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12598604#action_12598604
> ]
> >
> > Michael Dick commented on OPENJPA-606:
> > --------------------------------------
> >
> > It seems that we can move some of the logic in Distinct.java into the
> > DBDictionary. Or at least add a flag in the DBDictionary that indicates
> > whether ( ) are needed. Leaving :
> > if
> >
> (sel.getConfiguration().getDBDictionaryInstance().platform.indexOf("Informix")
> > > -1) {
> >
> > in the code is rather ugly.
> >
> > > InformixDictionary default setting beaks many testcases
> > > -------------------------------------------------------
> > >
> > > Key: OPENJPA-606
> > > URL: https://issues.apache.org/jira/browse/OPENJPA-606
> > > Project: OpenJPA
> > > Issue Type: Bug
> > > Components: sql
> > > Affects Versions: 1.2.0
> > > Reporter: Catalina Wei
> > >
> > > In testing Informix backend store, there are many query test string
> > failed:
> > > 1. parameter of a boolean value does not work, should use 't for true,
> > 'f' for false
> > > WHERE (t1.isManager = ?) [params=(boolean) false]
> > >
> > > should generate WHERE (t1.isManager = ?) [params=(String) f]
> > > 2. SELECT COUNT(DISTINCT(t1.name)) reported syntax error [code=-201,
> > state=42000]
> > > should generate COUNT(DISTINCT t1.name )
> > > 3. CROSS JOIN syntax error
> > >
> > > should generate JOIN with ON 1 = 1
> > >
> > > 4. There is no equivalent function for LOCATE. Informix users must
> > create INSTR as a user defined function.
> > > 3. CONCAT function in '||' does not take parameter markers
> > > WHERE ((?||t0.name) LIKE ? ESCAPE '\')
> > > should generate CONCAT(?, t0.name)
> >
> >
> > --
> > 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-606) InformixDictionary default setting beaks many testcases
Posted by catalina wei <ca...@gmail.com>.
Hi Mike,
Your comment is accepted.
A general solution for not placing extra "( )" for DISTINCT/ALL/ANY is filed
under OPENJPA-607 and resolved.
Catalina
On 5/21/08, Michael Dick (JIRA) <ji...@apache.org> wrote:
>
>
> [
> https://issues.apache.org/jira/browse/OPENJPA-606?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12598604#action_12598604]
>
> Michael Dick commented on OPENJPA-606:
> --------------------------------------
>
> It seems that we can move some of the logic in Distinct.java into the
> DBDictionary. Or at least add a flag in the DBDictionary that indicates
> whether ( ) are needed. Leaving :
> if
> (sel.getConfiguration().getDBDictionaryInstance().platform.indexOf("Informix")
> > -1) {
>
> in the code is rather ugly.
>
> > InformixDictionary default setting beaks many testcases
> > -------------------------------------------------------
> >
> > Key: OPENJPA-606
> > URL: https://issues.apache.org/jira/browse/OPENJPA-606
> > Project: OpenJPA
> > Issue Type: Bug
> > Components: sql
> > Affects Versions: 1.2.0
> > Reporter: Catalina Wei
> >
> > In testing Informix backend store, there are many query test string
> failed:
> > 1. parameter of a boolean value does not work, should use 't for true,
> 'f' for false
> > WHERE (t1.isManager = ?) [params=(boolean) false]
> >
> > should generate WHERE (t1.isManager = ?) [params=(String) f]
> > 2. SELECT COUNT(DISTINCT(t1.name)) reported syntax error [code=-201,
> state=42000]
> > should generate COUNT(DISTINCT t1.name )
> > 3. CROSS JOIN syntax error
> >
> > should generate JOIN with ON 1 = 1
> >
> > 4. There is no equivalent function for LOCATE. Informix users must
> create INSTR as a user defined function.
> > 3. CONCAT function in '||' does not take parameter markers
> > WHERE ((?||t0.name) LIKE ? ESCAPE '\')
> > should generate CONCAT(?, t0.name)
>
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>