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 "Dag H. Wanvik (JIRA)" <de...@db.apache.org> on 2006/10/06 00:10:20 UTC
[jira] Closed: (DERBY-1904) COALESCE with NULL parameter problem
[ http://issues.apache.org/jira/browse/DERBY-1904?page=all ]
Dag H. Wanvik closed DERBY-1904.
--------------------------------
Resolution: Invalid
Closing as agreed, not a bug.
> 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