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/09/29 17:40:52 UTC
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12438737 ]
A B commented on DERBY-1904:
----------------------------
Do you have a full stack trace for the NullPointerException error that you see with ij? Ex. can you run the above query and then attach the derby.log file to this issue?
I did the simple CREATE TABLE statement and then tried to execute the query, but a) the query references a column ITM_NAME that isn't in your table, and b) when I removed the ITM_NAME from the select list, the queries run without error. So I'm wondering if the problem is specific to your table schema and/or data. Can you use the dblook tool to generate the DDL for your ITEMS table and then post that, as well? That is, run
java org.apache.derby.tools.dblook -d jdbc:derby:<dbname> -t items
and then include the output here.
Having a full stack trace and the full DDL for the table in question might make it easier for interested people to investigate this problem more...Thanks!
> 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