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] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Date Tue, 03 Oct 2006 09:05:20 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439421 ] 
            
Cédric Gérard commented on DERBY-1904:
--------------------------------------

I try with the latest Derby version 10.1.2.6 like you suggest and the same problems occurred.

C:\tools\db-derby-10.2.1.6-bin\frameworks\NetworkServer\bin>ij
version ij 10.2
ij> connect 'jdbc:derby://localhost:1528/WATDB_V2';
ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE);
ERREUR 42X01 : Syntax error: Encountered "NULL" at line 1, column 62.
ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(CAST(NULL AS BIGINT),
ITM_CODE);
ID_ITM              |ITM_CODE
-----------------------------------------
ERREUR 38000 : The exception 'java.lang.NullPointerException' was thrown while evaluating
an expression. SQLSTATE: XJ001
: Java exception: ': java.lang.NullPointerException'.

BUT It seems that the COALESCE function only works with a NULL parameter when used in the
SELECT clause and not in WHERE statement (see above) .... I hope it helps to solve the problem
because I still have no workaround to my problem.

ij> SELECT ID_ITM, COALESCE(NULL, ITM_CODE) FROM ITEMS;
ERREUR 42X01 : Syntax error: Encountered "NULL" at line 1, column 25.
ij> SELECT ID_ITM, COALESCE(CAST(NULL AS BIGINT), ITM_CODE) FROM ITEMS;
ID_ITM              |2
-----------------------------------------
1                   |1
2                   |2
3                   |3

3 lignes sélectionnées
ij>

Regards,

Cédric Gérard


> 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

       

Mime
View raw message