Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <29917498.1159892064620.JavaMail.root@brutus>
Date: Tue, 3 Oct 2006 09:14:24 -0700 (PDT)
From: "A B (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1904) COALESCE with NULL parameter problem
In-Reply-To: <17009543.1159536415004.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

    [ http://issues.apache.org/jira/browse/DERBY-1904?page=3Dcomments#actio=
n_12439557 ]=20
           =20
A B commented on DERBY-1904:
----------------------------

Thanks for continuing to investigate this problem, C=C3=A9dric.

> 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 i=
n this case the
> method setNull(idx, type);=20

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 t=
he JDBC API for 1.4.2 and the description of setObject(parameterIndex, x) s=
ays 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 SQ=
L type before being sent to the database. [...]  This method throws an exce=
ption if there is an ambiguity, for example, if the object is of a class im=
plementing 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, targetSq=
lType), which allows the caller to explicitly give a target SQL type, thus =
removing ambiguity.  In that case Derby correctly binds the parameter and e=
xecutes 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=C3=A9dric G=C3=A9rard
>            Priority: Blocker
>         Attachments: dblook.log, stackfromderby.log
>
>
> Hi,
> My  initial objective was to execute this query with  the JSTL sql tag li=
brary  :
>  =20
> SELECT  ID_ITM, ITM_NAME, ITM_CODE
>     FROM  ITEMS
>     WHERE  ITM_CODE =3D COALESCE(?,ITM_CODE)
>  =20
> ITM_CODE is a NUMERIC column, here's the DDL script of the ITEMS  table :
> CREATE  TABLE  ITEMS ( =20
>          ID_ITM                BIGINT NOT  NULL  GENERATED ALWAYS AS  IDE=
NTITY,=20
>          ITM_CODE         NUMERIC  (22) NOT  NULL
>  );
> When ITM_CODE is not set in my application, JSTL bind the parameter as nu=
ll. When ITM_CODE is set, it works.
>  =20
>  I  got this message error :=20
>  : Invalid  data conversion: Parameter object type is invalid for request=
ed conversion.  (Apache
> Derby  release 10.1.3.1 client  driver)
> =3D> We try to use CAST(? AS NUMERIC) in place of ?. We obtain the same e=
rror when NULL is passed.
>  : The  exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thro=
wn while  evaluating
> an expression. SQLSTATE: XJ001: Java exception: '-1:  java.lang.ArrayInde=
xOutOfBoundsException'.
> (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 =3D 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 =3D COALESCE(NULLIF(1,1),ITM_CODE );
> ID_ITM              |ITM_NAME                                          |I=
TM_CODE
> -------------------------------------------------------------------------=
-----------------------
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown wh=
ile evaluating an expression. SQLSTATE: XJ001: Java exception: ': java.lang=
.NullPointerException'.
> ij> SELECT ID_ITM, ITM_NAME, ITM_CODE
>      FROM ITEMS
>      WHERE ITM_CODE =3D COALESCE(CAST(NULLIF(1,1) AS BIGINT), ITM_CODE);
> ID_ITM              |ITM_NAME                                          |I=
TM_CODE
> -------------------------------------------------------------------------=
-----------------------
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown wh=
ile 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 do=
n'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 encoun=
tered the same problems...
> So I don't actually have any workaround.
> Regards,
> C=C3=A9dric

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: htt=
p://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira