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. dblook.log
        1 kB
        Cédric Gérard
      2. stackfromderby.log
        5 kB
        Cédric Gérard

        Issue Links

          Activity

          Hide
          A B added a comment -

          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!

          Show
          A B added a comment - 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!
          Hide
          Cédric Gérard added a comment -

          Hi,

          Here are the asked files attached to these thread.

          ITM_NAME was removed from my previous post to be more clear and simple.

          This is the full ij command to reproduce the problem :

          C:\tools\db-derby-10.1.3.1-bin\frameworks\NetworkServer\bin>java -Dij.driver=org.apache.derby.jdbc.ClientDriver -Dij.protocol=jdbc:derby://localhost:1528/ -Dij.user=WAT_V2 -Dij.password=WAT_V2 org.apache.derby.tools.ij
          ij version 10.1
          ij> connect 'jdbc:derby://localhost:1528/watDB_v2';
          ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE);
          ERROR 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
          ---------------------------------------------
          ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001:
          Java exception: ': java.lang.NullPointerException'.

          Thanks a lot for your work.

          Regards

          Cédric

          Show
          Cédric Gérard added a comment - Hi, Here are the asked files attached to these thread. ITM_NAME was removed from my previous post to be more clear and simple. This is the full ij command to reproduce the problem : C:\tools\db-derby-10.1.3.1-bin\frameworks\NetworkServer\bin>java -Dij.driver=org.apache.derby.jdbc.ClientDriver -Dij.protocol=jdbc:derby://localhost:1528/ -Dij.user=WAT_V2 -Dij.password=WAT_V2 org.apache.derby.tools.ij ij version 10.1 ij> connect 'jdbc:derby://localhost:1528/watDB_v2'; ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE); ERROR 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 --------------------------------------------- ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang.NullPointerException'. Thanks a lot for your work. Regards Cédric
          Hide
          A B added a comment -

          Posting email reply from Dag Wanvik (derby-dev) in case Cédric isn't subscribed to that list:

          <begin Dag>

          >> ij version 10.1
          >> ij> connect 'jdbc:derby://localhost:1528/watDB_v2';
          >> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE);
          >> ERROR 42X01: Syntax error: Encountered "NULL" at line 1, column 62.

          The "NULL" literal here is not legal SQL, you need to cast the value as in your next example.

          >> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(CAST(NULL AS BIGINT), ITM_CODE);
          >> ID_ITM |ITM_CODE
          >> ---------------------------------------------
          >> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001:
          >> Java exception: ': java.lang.NullPointerException'.

          I tried this on trunk and it works for me. It may be that this has been corrected already. Are you able to try it with the 10.2 release candidate? You can find it here: http://people.apache.org/~rhillegas/10.2.1.5/

          <end Dag>

          Show
          A B added a comment - Posting email reply from Dag Wanvik (derby-dev) in case Cédric isn't subscribed to that list: <begin Dag> >> ij version 10.1 >> ij> connect 'jdbc:derby://localhost:1528/watDB_v2'; >> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(NULL, ITM_CODE); >> ERROR 42X01: Syntax error: Encountered "NULL" at line 1, column 62. The "NULL" literal here is not legal SQL, you need to cast the value as in your next example. >> ij> SELECT ID_ITM, ITM_CODE FROM ITEMS WHERE ITM_CODE = COALESCE(CAST(NULL AS BIGINT), ITM_CODE); >> ID_ITM |ITM_CODE >> --------------------------------------------- >> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. SQLSTATE: XJ001: >> Java exception: ': java.lang.NullPointerException'. I tried this on trunk and it works for me. It may be that this has been corrected already. Are you able to try it with the 10.2 release candidate? You can find it here: http://people.apache.org/~rhillegas/10.2.1.5/ <end Dag>
          Hide
          Cédric Gérard added a comment -

          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

          Show
          Cédric Gérard added a comment - 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
          Hide
          Cédric Gérard added a comment -

          I have some new stuff for you.

          When I use the Derby 10.1.2.6 Client Driver for all my clients (so it was not the case previously). Its works with ij but a cast is needed ;-(

          But my problem is now when using JSTL with the same query. It is impossible to pass null argument to this.
          I activate the stack trace from the JDBC driver using tracefile parameter and I obtain the following error log when my parameter is null

          [derby][ParameterMetaData@c52200] BEGIN TRACE_PARAMETER_META_DATA
          [derby][ParameterMetaData@c52200] Parameter meta data for statement Statement@a29c6e
          [derby][ParameterMetaData@c52200] Number of parameter columns: 1
          [derby][ParameterMetaData@c52200] Column 1:

          { label=1, name=1, type name=BIGINT, type=-5, nullable=1, precision=19, scale=0, schema name=, table name=, writable=false, sqlPrecision=19, sqlScale=0, sqlLength=8, sqlType=493, sqlCcsid=0, sqlName=1, sqlLabel=null, sqlUnnamed=0, sqlComment=null, sqlxKeymem=0, sqlxGenerated=0, sqlxParmmode=1, sqlxCorname=null, sqlxName=null, sqlxBasename=null, sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=, internal type=-5, }

          [derby][ParameterMetaData@c52200]

          { sqldHold=1, sqldReturn=0, sqldScroll=0, sqldSensitive=0, sqldFcode=0, sqldKeytype=0, sqldRdbnam=, sqldSchema=null }

          [derby][ParameterMetaData@c52200] END TRACE_PARAMETER_META_DATA
          [derby][Time:1159872072810][Thread:http-8080-Processor24][Connection@ab7165] prepareStatement () returned PreparedStatement@a29c6e
          [derby][Time:1159872072810][Thread:http-8080-Processor24][PreparedStatement@a29c6e] setObject (1, null) called
          [derby][Time:1159872072810][Thread:http-8080-Processor24][Connection@ab7165] isClosed () returned false
          [derby][Time:1159872072810][Thread:http-8080-Processor24][Connection@ab7165] isClosed () returned false
          [derby] BEGIN TRACE_DIAGNOSTICS
          [derby][SQLException@53c3f5] java.sql.SQLException
          [derby][SQLException@53c3f5] SQL state = XJ021
          [derby][SQLException@53c3f5] Error code = 20000
          [derby][SQLException@53c3f5] Message = Type is not supported.
          [derby][SQLException@53c3f5] Stack trace follows
          org.apache.derby.client.am.SqlException: Type is not supported.
          at org.apache.derby.client.am.PreparedStatement.setObject(Unknown Source)
          at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165)
          at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.setParameters(QueryTagSupport.java:295)
          at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:214)
          ...
          [derby] END TRACE_DIAGNOSTICS

          So with 10.2.1.6, it works SQL side but still not in the JDBC layer ...

          Can you help me to solve this issue ?

          Regards,

          Cédric

          Show
          Cédric Gérard added a comment - I have some new stuff for you. When I use the Derby 10.1.2.6 Client Driver for all my clients (so it was not the case previously). Its works with ij but a cast is needed ;-( But my problem is now when using JSTL with the same query. It is impossible to pass null argument to this. I activate the stack trace from the JDBC driver using tracefile parameter and I obtain the following error log when my parameter is null [derby] [ParameterMetaData@c52200] BEGIN TRACE_PARAMETER_META_DATA [derby] [ParameterMetaData@c52200] Parameter meta data for statement Statement@a29c6e [derby] [ParameterMetaData@c52200] Number of parameter columns: 1 [derby] [ParameterMetaData@c52200] Column 1: { label=1, name=1, type name=BIGINT, type=-5, nullable=1, precision=19, scale=0, schema name=, table name=, writable=false, sqlPrecision=19, sqlScale=0, sqlLength=8, sqlType=493, sqlCcsid=0, sqlName=1, sqlLabel=null, sqlUnnamed=0, sqlComment=null, sqlxKeymem=0, sqlxGenerated=0, sqlxParmmode=1, sqlxCorname=null, sqlxName=null, sqlxBasename=null, sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=, internal type=-5, } [derby] [ParameterMetaData@c52200] { sqldHold=1, sqldReturn=0, sqldScroll=0, sqldSensitive=0, sqldFcode=0, sqldKeytype=0, sqldRdbnam=, sqldSchema=null } [derby] [ParameterMetaData@c52200] END TRACE_PARAMETER_META_DATA [derby] [Time:1159872072810] [Thread:http-8080-Processor24] [Connection@ab7165] prepareStatement () returned PreparedStatement@a29c6e [derby] [Time:1159872072810] [Thread:http-8080-Processor24] [PreparedStatement@a29c6e] setObject (1, null) called [derby] [Time:1159872072810] [Thread:http-8080-Processor24] [Connection@ab7165] isClosed () returned false [derby] [Time:1159872072810] [Thread:http-8080-Processor24] [Connection@ab7165] isClosed () returned false [derby] BEGIN TRACE_DIAGNOSTICS [derby] [SQLException@53c3f5] java.sql.SQLException [derby] [SQLException@53c3f5] SQL state = XJ021 [derby] [SQLException@53c3f5] Error code = 20000 [derby] [SQLException@53c3f5] Message = Type is not supported. [derby] [SQLException@53c3f5] Stack trace follows org.apache.derby.client.am.SqlException: Type is not supported. at org.apache.derby.client.am.PreparedStatement.setObject(Unknown Source) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.setParameters(QueryTagSupport.java:295) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:214) ... [derby] END TRACE_DIAGNOSTICS So with 10.2.1.6, it works SQL side but still not in the JDBC layer ... Can you help me to solve this issue ? Regards, Cédric
          Hide
          Cédric Gérard added a comment -

          I can confirm that in your JDBC Client driver source, the case where arg is null in the method setObject(idx, arg) is not treated.
          Your should call internally in this case the method setNull(idx, type);

          JSTL Release notes (bug fix list) says that :
          "According to the JDBC specification, a null can be passed to the PreparedStatement.setObject() and the parameter will be set to JDBC NULL properly. The broken PreparedStatment.setNull() call has been removed."

          Today using JSTL tag <sql:param> is not quite possible with Derby database.

          Is it possible to fix this issue ?

          Thanks a lot.

          Cédric Gérard

          Show
          Cédric Gérard added a comment - I can confirm that in your JDBC Client driver source, the case where arg is null in the method setObject(idx, arg) is not treated. Your should call internally in this case the method setNull(idx, type); JSTL Release notes (bug fix list) says that : "According to the JDBC specification, a null can be passed to the PreparedStatement.setObject() and the parameter will be set to JDBC NULL properly. The broken PreparedStatment.setNull() call has been removed." Today using JSTL tag <sql:param> is not quite possible with Derby database. Is it possible to fix this issue ? Thanks a lot. Cédric Gérard
          Hide
          A B added a comment -

          Thanks for continuing to investigate this problem, Cédric.

          > I can confirm that in your JDBC Client driver source, the case where arg is null in the
          > method setObject(idx, arg) is not treated. Your should call internally in this case the
          > method setNull(idx, type);

          The problem with this approach is that we don't know what "type" should be. If the object in setObject(idx, arg) is a Java null, how does one figure out the corresponding SQL type?

          I know very (very) little about what JDBC expects here, but I did look at the JDBC API for 1.4.2 and the description of setObject(parameterIndex, x) says the following:

          "The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database. [...] This method throws an exception if there is an ambiguity, for example, if the object is of a class implementing more than one of the interfaces named above."

          Does JDBC specify a mapping for a typeless null Java object? If not, that seems to suggest "ambiguity" to me and hence Derby would be doing the right thing by throwing an exception. But again, I don't know enough about JDBC to say for sure.

          Note that there is another JDBC call, setObject(parameterIndex, x, targetSqlType), which allows the caller to explicitly give a target SQL type, thus removing ambiguity. In that case Derby correctly binds the parameter and executes without error (I wrote up a quick test to verify)...

          Show
          A B added a comment - Thanks for continuing to investigate this problem, Cédric. > I can confirm that in your JDBC Client driver source, the case where arg is null in the > method setObject(idx, arg) is not treated. Your should call internally in this case the > method setNull(idx, type); The problem with this approach is that we don't know what "type" should be. If the object in setObject(idx, arg) is a Java null, how does one figure out the corresponding SQL type? I know very (very) little about what JDBC expects here, but I did look at the JDBC API for 1.4.2 and the description of setObject(parameterIndex, x) says the following: "The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database. [...] This method throws an exception if there is an ambiguity, for example, if the object is of a class implementing more than one of the interfaces named above." Does JDBC specify a mapping for a typeless null Java object? If not, that seems to suggest "ambiguity" to me and hence Derby would be doing the right thing by throwing an exception. But again, I don't know enough about JDBC to say for sure. Note that there is another JDBC call, setObject(parameterIndex, x, targetSqlType), which allows the caller to explicitly give a target SQL type, thus removing ambiguity. In that case Derby correctly binds the parameter and executes without error (I wrote up a quick test to verify)...
          Hide
          Dag H. Wanvik added a comment -

          Derby supports using setObject with a null value only if the
          type is provided, that is, the form:

          void setObject(int parameterIndex,
          Object x,
          int targetSqlType) throws SQLException

          e.g.
          ps.setObject(1, null, java.sql.Types.BIGINT);

          The JDBC4 SQL API warns against using the form without the explicit type:
          (quote):

          void setObject(int parameterIndex,
          Object x) throws SQLException

          :

          Note: Not all databases allow for a non-typed Null to be sent to
          the backend. For maximum portability, the setNull or the
          setObject(int parameterIndex, Object x, int sqlType) method
          should be used instead of setObject(int parameterIndex, Object
          x).

          I tried both forms with 10.2 and the former works for both embedded
          and client drivers.

          I am not familiar with JSTL, is there any way you can influence what
          SQL is generated?

          Show
          Dag H. Wanvik added a comment - Derby supports using setObject with a null value only if the type is provided, that is, the form: void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException e.g. ps.setObject(1, null, java.sql.Types.BIGINT); The JDBC4 SQL API warns against using the form without the explicit type: (quote): void setObject(int parameterIndex, Object x) throws SQLException : Note: Not all databases allow for a non-typed Null to be sent to the backend. For maximum portability, the setNull or the setObject(int parameterIndex, Object x, int sqlType) method should be used instead of setObject(int parameterIndex, Object x). I tried both forms with 10.2 and the former works for both embedded and client drivers. I am not familiar with JSTL, is there any way you can influence what SQL is generated?
          Hide
          Cédric Gérard added a comment -

          No, there is no way to influence in the way JSTL binds the parameter in the SQL queries.

          I saw the source code of JSTL 1.0 and 1.1. To bind parameter for PreparedStatement, JSTL uses the setObject(idx, param) method even if the parameter is null. No type is given whatever the parameter value is.

          So I can conclude that the Jakarta Standard Tag Library are not yet fully compatible with the Apache Derby Database.

          If JSTL will use the setObject(int parameterIndex, Object x, int targetSqlType), are you generated a NULL SQL typed ? That is to say that I can use COALESCE(?, COLUMN) without using the CAST function ? I don't think so, but I'm not sure. So you need the type for null in JDBC but you do not use this type in SQL ? I really don't understand that. Maybe can you explain.

          Other way of putting it is that the constraint of using the CAST function for NULL makes that my SQL is not SQL 92 compliant but derby specific. CAST is not an SQL 92 function, I think.

          I think you can help me if you decide to fix the setObject(int parameterIndex, Object x) to detect if the x is null, you can call setNullX that finally makes a setInput(parameterIndex, null). It will be giant.

          Why this full Java database needs to type the untyped Java null ?

          Thanks to help me.

          Regards,

          Cédric Gérard

          Show
          Cédric Gérard added a comment - No, there is no way to influence in the way JSTL binds the parameter in the SQL queries. I saw the source code of JSTL 1.0 and 1.1. To bind parameter for PreparedStatement, JSTL uses the setObject(idx, param) method even if the parameter is null. No type is given whatever the parameter value is. So I can conclude that the Jakarta Standard Tag Library are not yet fully compatible with the Apache Derby Database. If JSTL will use the setObject(int parameterIndex, Object x, int targetSqlType), are you generated a NULL SQL typed ? That is to say that I can use COALESCE(?, COLUMN) without using the CAST function ? I don't think so, but I'm not sure. So you need the type for null in JDBC but you do not use this type in SQL ? I really don't understand that. Maybe can you explain. Other way of putting it is that the constraint of using the CAST function for NULL makes that my SQL is not SQL 92 compliant but derby specific. CAST is not an SQL 92 function, I think. I think you can help me if you decide to fix the setObject(int parameterIndex, Object x) to detect if the x is null, you can call setNullX that finally makes a setInput(parameterIndex, null). It will be giant. Why this full Java database needs to type the untyped Java null ? Thanks to help me. Regards, Cédric Gérard
          Hide
          Tomohito Nakayama added a comment -

          Hello.

          I'm reading "A Guide to THE SQL STANDARD FOURTH EDITION" ( http://www.amazon.co.jp/gp/product/4756120474/ref=sr_11_1/503-3743940-2015100?ie=UTF8 ) to learn about NULL in SQL ....
          // I'm reading one translated into Japanese, then my expression may be different from yours

          It was written in the 16 th chapter as next.
          >There is no type for NULL ( Accurately, NULL is not value ).

          I believe this is true in spec of SQL.
          However, it seems that JDBC api regards NULL as value and there is type for NULL implicitly.

          I think there exists difficulty between JDBC and SQL and this prolem may be deep-rooted ....

          Show
          Tomohito Nakayama added a comment - Hello. I'm reading "A Guide to THE SQL STANDARD FOURTH EDITION" ( http://www.amazon.co.jp/gp/product/4756120474/ref=sr_11_1/503-3743940-2015100?ie=UTF8 ) to learn about NULL in SQL .... // I'm reading one translated into Japanese, then my expression may be different from yours It was written in the 16 th chapter as next. >There is no type for NULL ( Accurately, NULL is not value ). I believe this is true in spec of SQL. However, it seems that JDBC api regards NULL as value and there is type for NULL implicitly. I think there exists difficulty between JDBC and SQL and this prolem may be deep-rooted ....
          Hide
          Dag H. Wanvik added a comment -

          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?

          Show
          Dag H. Wanvik added a comment - 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?
          Hide
          Tomohito Nakayama added a comment -

          +1, at least for me.

          Show
          Tomohito Nakayama added a comment - +1, at least for me.
          Hide
          Cédric Gérard added a comment -

          OK, for me too.
          Thanks for your explaination. It is clear.
          Thanks to open the improvement as you suggest.

          I will ask and open the setObject with 3 args improvement to JSTL team on my side.

          I was thinking that Derby database will be the only one Java database because of its pakaging with Java 6.
          For the moment, i will used MySql to replace my Oracle database in place of Derby that are not already compatible with JSTL (J2EE 1.5).

          Thanks a lot.

          Cédric Gérard

          Show
          Cédric Gérard added a comment - OK, for me too. Thanks for your explaination. It is clear. Thanks to open the improvement as you suggest. I will ask and open the setObject with 3 args improvement to JSTL team on my side. I was thinking that Derby database will be the only one Java database because of its pakaging with Java 6. For the moment, i will used MySql to replace my Oracle database in place of Derby that are not already compatible with JSTL (J2EE 1.5). Thanks a lot. Cédric Gérard
          Hide
          Dag H. Wanvik added a comment -

          Closing as agreed, not a bug.

          Show
          Dag H. Wanvik added a comment - Closing as agreed, not a bug.

            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