You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Dain Sundstrom <da...@iq80.com> on 2007/04/03 02:27:08 UTC
Bad Derby query
I have the following query:
SELECT a.alias FROM AliasBean AS a WHERE (a.alias IS NULL AND ?1 IS
NULL) OR a.alias = ?1
Which works great when run against HSQLDB. When I switch the
database to Derby I get the following exception:
<0|false|0.9.6-incubating>
org.apache.openjpa.persistence.PersistenceException: Syntax error:
Encountered "NULL" at line 1, column 68. {SELECT t0.ALIAS FROM
ALIASEJB_TABLE t0 WHERE (t0.ALIAS IS NULL AND NULL IS NULL OR
t0.ALIAS IS NULL)} [code=20000, state=42X01]
Now my code is setting the parameter to NULL but that should be
valid. Any idea what is going on? Also, I'm using 0.9.6 so is this
a known problem?
Thanks,
-dain
Re: Bad Derby query
Posted by Craig L Russell <Cr...@Sun.COM>.
On Apr 2, 2007, at 8:39 PM, Dain Sundstrom wrote:
> On Apr 2, 2007, at 8:06 PM, Craig L Russell wrote:
>
>> Hi Dain,
>>
>> I've seen this problem as well, with a different provider. The JPA
>> spec lead sez that "?1 IS NULL" is not a portable JPAQL query,
>> even though some providers can generate SQL that some databases
>> can execute properly.
>
> OpenJPA is handling this for hsql just not derby.
Do you mean that OpenJPA generates different SQL for hsql? Or that
hsql can execute the SQL that Derby cannot?
Craig
> Is there some plugin we can tweak to support this clause in derby?
>
>> It might not be an easy issue for OpenJPA to fix. How easy is it
>> for you to generate a different query?
>
> Impossible. This query is defined in the CMP TCK.
>
> -dain
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Bad Derby query
Posted by Dain Sundstrom <da...@iq80.com>.
On Apr 2, 2007, at 8:06 PM, Craig L Russell wrote:
> Hi Dain,
>
> I've seen this problem as well, with a different provider. The JPA
> spec lead sez that "?1 IS NULL" is not a portable JPAQL query, even
> though some providers can generate SQL that some databases can
> execute properly.
OpenJPA is handling this for hsql just not derby. Is there some
plugin we can tweak to support this clause in derby?
> It might not be an easy issue for OpenJPA to fix. How easy is it
> for you to generate a different query?
Impossible. This query is defined in the CMP TCK.
-dain
Re: Bad Derby query
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Dain,
I've seen this problem as well, with a different provider. The JPA
spec lead sez that "?1 IS NULL" is not a portable JPAQL query, even
though some providers can generate SQL that some databases can
execute properly.
It might not be an easy issue for OpenJPA to fix. How easy is it for
you to generate a different query?
Craig
On Apr 2, 2007, at 5:27 PM, Dain Sundstrom wrote:
> I have the following query:
>
> SELECT a.alias FROM AliasBean AS a WHERE (a.alias IS NULL AND ?1
> IS NULL) OR a.alias = ?1
>
> Which works great when run against HSQLDB. When I switch the
> database to Derby I get the following exception:
>
> <0|false|0.9.6-incubating>
> org.apache.openjpa.persistence.PersistenceException: Syntax error:
> Encountered "NULL" at line 1, column 68. {SELECT t0.ALIAS FROM
> ALIASEJB_TABLE t0 WHERE (t0.ALIAS IS NULL AND NULL IS NULL OR
> t0.ALIAS IS NULL)} [code=20000, state=42X01]
>
> Now my code is setting the parameter to NULL but that should be
> valid. Any idea what is going on? Also, I'm using 0.9.6 so is
> this a known problem?
>
> Thanks,
>
> -dain
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Bad Derby query
Posted by Dain Sundstrom <da...@iq80.com>.
Finally got some time to test 0.9.7 and it works great! Fixed all
the strange query problems I was seeing with derby.
-dain
On Apr 2, 2007, at 9:15 PM, Marc Prud'hommeaux wrote:
> Dain-
>
>> Also, I'm using 0.9.6 so is this a known problem?
>
> I have a very hazy recollection of this being something that was
> fixed recently, but a quick search through JIRA doesn't reveal
> anything.
>
> Could you try it out against the latest 0.9.7-incubating-SNAPSHOT
> and let us know if still happens?
>
>
>
> On Apr 2, 2007, at 5:27 PM, Dain Sundstrom wrote:
>
>> I have the following query:
>>
>> SELECT a.alias FROM AliasBean AS a WHERE (a.alias IS NULL AND ?1
>> IS NULL) OR a.alias = ?1
>>
>> Which works great when run against HSQLDB. When I switch the
>> database to Derby I get the following exception:
>>
>> <0|false|0.9.6-incubating>
>> org.apache.openjpa.persistence.PersistenceException: Syntax error:
>> Encountered "NULL" at line 1, column 68. {SELECT t0.ALIAS FROM
>> ALIASEJB_TABLE t0 WHERE (t0.ALIAS IS NULL AND NULL IS NULL OR
>> t0.ALIAS IS NULL)} [code=20000, state=42X01]
>>
>> Now my code is setting the parameter to NULL but that should be
>> valid. Any idea what is going on? Also, I'm using 0.9.6 so is
>> this a known problem?
>>
>> Thanks,
>>
>> -dain
>
Re: Bad Derby query
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Dain-
> Also, I'm using 0.9.6 so is this a known problem?
I have a very hazy recollection of this being something that was
fixed recently, but a quick search through JIRA doesn't reveal anything.
Could you try it out against the latest 0.9.7-incubating-SNAPSHOT and
let us know if still happens?
On Apr 2, 2007, at 5:27 PM, Dain Sundstrom wrote:
> I have the following query:
>
> SELECT a.alias FROM AliasBean AS a WHERE (a.alias IS NULL AND ?1
> IS NULL) OR a.alias = ?1
>
> Which works great when run against HSQLDB. When I switch the
> database to Derby I get the following exception:
>
> <0|false|0.9.6-incubating>
> org.apache.openjpa.persistence.PersistenceException: Syntax error:
> Encountered "NULL" at line 1, column 68. {SELECT t0.ALIAS FROM
> ALIASEJB_TABLE t0 WHERE (t0.ALIAS IS NULL AND NULL IS NULL OR
> t0.ALIAS IS NULL)} [code=20000, state=42X01]
>
> Now my code is setting the parameter to NULL but that should be
> valid. Any idea what is going on? Also, I'm using 0.9.6 so is
> this a known problem?
>
> Thanks,
>
> -dain