[DERBY-1904] COALESCE with NULL parameter problem

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Invalid
• Affects Version/s: 10.1.3.1
• Component/s: JDBC, SQL
• Environment: Windows 2000 SP4; JSTL 1.0.6 on J2EE 1.3; Tomcat 5.0.28 using a SUN JVM 1.4.2_03
• Urgency: Urgent
• Assignee: Unassigned
• Reporter: Cédric Gérard
• Created: 29/Sep/06 13:26
• Updated: 05/Oct/06 22:41
• Resolved: 05/Oct/06 22:09

Description

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

Issue Links

• relates to: DERBY-1610 Resolve difference of type compatibility between Embedded and NetworkServer/NetworkDriver
• relates to: DERBY-1938 Add support for setObject(<arg>, null)