db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Date Wed, 04 Oct 2006 15:23:20 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439856 ] 
            
Dag H. Wanvik commented on DERBY-1904:
--------------------------------------

Actually, if ones uses the typed variant of setObject, a CAST will not
be necessary:

I just tested the following fragment with 10.2:

            s = con.prepareStatement("SELECT id_itm, itm_code from items " + 
                                     "where itm_code = coalesce(?, itm_code)");
            s.setObject(1, null, java.sql.Types.BIGINT);
            rs = s.executeQuery();

and this works. As for CAST, this is a standard part of SQL (quote
ISO/IEC 9075-2:2003):

> 6.12 <cast specification> Function Specify a data conversion. 
> 
> Format 
> 
> <cast specification> ::= CAST <left paren><cast operand> AS 
>                               <cast target><right paren>
> <cast operand> ::= <value expression> | <implicitly typed value specification>

> 
> <cast target>::= <domain name> |<data type> 

In plain SQL, a CAST *is* required by the standard for for passing a
NULL into COALESCE in SQL, cf. definition of COALESCE:

> <case abbreviation> ::= NULLIF <left paren> <value expression> <comma>
>                                <value expression> <right paren> 
>                        | COALESCE <left paren> <value expression> {<comma><value
expression>}
>                                   ...<right paren>

and <value expression> does not comprise NULL.  The <implicitly typed
value specification> given as a legal argument for CAST, does comprise
NULL, however. 

So Derby is compliant with the standard.

Another thing is that it would be good if Derby did support the untyped
(2 arg) version of setObject(<argno>, null), but it does not
presently.

Perhaps we could persuade JSTL to use the typed setObject version for
Derby, especially since the JDBC API now recommends this version for
portability.

I suggest we close this issue as not a bug and file an
improvement issue for setObject instead. Is that OK with you? 


> 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