db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Cédric Gérard (JIRA) <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1904) COALESCE with NULL parameter problem
Date Fri, 29 Sep 2006 13:26:55 GMT
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


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

       

Mime
View raw message