Derby
  1. Derby
  2. DERBY-1904

COALESCE with NULL parameter problem

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Invalid
    • Affects Version/s: 10.1.3.1
    • Fix Version/s: None
    • Component/s: JDBC, SQL
    • Labels:
      None
    • 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

      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

      1. stackfromderby.log
        5 kB
        Cédric Gérard
      2. dblook.log
        1 kB
        Cédric Gérard

        Issue Links

          Activity

          No work has yet been logged on this issue.

            People

            • Assignee:
              Unassigned
              Reporter:
              Cédric Gérard
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development