You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "A B (JIRA)" <de...@db.apache.org> on 2006/10/02 18:54:22 UTC
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439221 ]
A B commented on DERBY-1904:
----------------------------
Posting email reply from Dag Wanvik (derby-dev) in case Cédric isn't subscribed to that list:
<begin Dag>
>> ij version 10.1
>> ij> connect 'jdbc:derby://localhost:1528/watDB_v2';
>> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE);
>> ERROR 42X01: Syntax error: Encountered "NULL" at line 1, column 62.
The "NULL" literal here is not legal SQL, you need to cast the value as in your next example.
>> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(CAST(NULL AS BIGINT), ITM_CODE);
>> ID_ITM |ITM_CODE
>> ---------------------------------------------
>> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001:
>> Java exception: ': java.lang.NullPointerException'.
I tried this on trunk and it works for me. It may be that this has been corrected already. Are you able to try it with the 10.2 release candidate? You can find it here: http://people.apache.org/~rhillegas/10.2.1.5/
<end Dag>
> COALESCE with NULL parameter problem
> ------------------------------------
>
> Key: DERBY-1904
> URL: http://issues.apache.org/jira/browse/DERBY-1904
> Project: Derby
> Issue Type: Bug
> Components: JDBC, SQL
> Affects Versions: 10.1.3.1
> Environment: Windows 2000 SP4
> JSTL 1.0.6 on J2EE 1.3
> Tomcat 5.0.28 using a SUN JVM 1.4.2_03
> Reporter: Cédric Gérard
> Priority: Blocker
> Attachments: dblook.log, stackfromderby.log
>
>
> Hi,
> My initial objective was to execute this query with the JSTL sql tag library :
>
> SELECT ID_ITM, ITM_NAME, ITM_CODE
> FROM ITEMS
> WHERE ITM_CODE = COALESCE(?,ITM_CODE)
>
> ITM_CODE is a NUMERIC column, here's the DDL script of the ITEMS table :
> CREATE TABLE ITEMS (
> ID_ITM BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
> ITM_CODE NUMERIC (22) NOT NULL
> );
> When ITM_CODE is not set in my application, JSTL bind the parameter as null. When ITM_CODE is set, it works.
>
> I got this message error :
> : Invalid data conversion: Parameter object type is invalid for requested conversion. (Apache
> Derby release 10.1.3.1 client driver)
> => We try to use CAST(? AS NUMERIC) in place of ?. We obtain the same error when NULL is passed.
> : The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating
> an expression. SQLSTATE: XJ001: Java exception: '-1: java.lang.ArrayIndexOutOfBoundsException'.
> (Apache Derby snapshot-10.2.0.4 client driver)
> With ij, we have the same problem (but not the same message)
> ij> SELECT ID_ITM, ITM_NAME, ITM_CODE
> FROM ITEMS
> WHERE ITM_CODE = COALESCE(NULL,ITM_CODE );
> ERROR 42X01: Syntax error: Encountered "NULL" at line 3, column 31.
> ij> SELECT ID_ITM, ITM_NAME, ITM_CODE
> FROM ITEMS
> WHERE ITM_CODE = COALESCE(NULLIF(1,1),ITM_CODE );
> ID_ITM |ITM_NAME |ITM_CODE
> ------------------------------------------------------------------------------------------------
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.NullPointerException'.
> ij> SELECT ID_ITM, ITM_NAME, ITM_CODE
> FROM ITEMS
> WHERE ITM_CODE = COALESCE(CAST(NULLIF(1,1) AS BIGINT), ITM_CODE);
> ID_ITM |ITM_NAME |ITM_CODE
> ------------------------------------------------------------------------------------------------
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.NullPointerException'.
> We have the same errors using 10.2.0.4 snapshot.
> The coalesce function should accept NULL parameter.
> Of course, My sample is very simple and I have n search criteria; so I don't want to create 2^n SQL queries to deal with null or not null criterion.
> I try to replace the coalesce function with a CASE statement but I encountered the same problems...
> So I don't actually have any workaround.
> Regards,
> Cédric
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira