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 "Cédric Gérard (JIRA)" <de...@db.apache.org> on 2006/09/29 15:26:55 UTC
[jira] Created: (DERBY-1904) COALESCE with NULL parameter problem
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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Tomohito Nakayama (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439883 ]
Tomohito Nakayama commented on DERBY-1904:
------------------------------------------
+1, at least for me.
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439441 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
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
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439757 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
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
> 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
Re: [jira] Updated: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
"Cédric Gérard (JIRA)" <de...@db.apache.org> writes:
> 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/
Dag
[jira] Updated: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=all ]
Cédric Gérard updated DERBY-1904:
---------------------------------
Attachment: stackfromderby.log
dblook.log
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
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439557 ]
A B commented on DERBY-1904:
----------------------------
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)...
> 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
[jira] Closed: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Dag H. Wanvik (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=all ]
Dag H. Wanvik closed DERBY-1904.
--------------------------------
Resolution: Invalid
Closing as agreed, not a bug.
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "A B (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439221 ]
A B commented on DERBY-1904:
----------------------------
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>
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439484 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
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
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Dag H. Wanvik (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439894 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
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
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Cédric Gérard (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439421 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
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
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Tomohito Nakayama (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439851 ]
Tomohito Nakayama commented on DERBY-1904:
------------------------------------------
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 :P
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 ....
> 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
[jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
Posted by "Dag H. Wanvik (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439559 ]
Dag H. Wanvik commented on DERBY-1904:
--------------------------------------
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?
> 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