You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Peterson, John" <Jo...@pega.com> on 2006/07/27 21:04:49 UTC
ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.
Hello,
It was reported to me that one of the reasons our software currently
doesn't work with Apache Derby is because of ERROR 42X89. One of the
engineering teams reported: "We found that there is a bug in Derby in
its support for the Case statement. There is a workaround but that will
require us to change the SQL generation code to specifically check if
we're connected to Derby and generate a different SQL syntax." After
inquiring after the specifics, I was provided with the following
example:
ij>values case when 1=2 then 3 else NULL end;
ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
Neither type is assignable to the other type.
Is this indeed a bug (that will be fixed in the future), or is Derby
behaving as it was intended?
Thanks for your help,
John
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
Neither type is assignable to the other type.
Posted by Daniel John Debrunner <dj...@apache.org>.
Peterson, John wrote:
> Our concern with casting NULL to the appropriate type is that it might
> have a performance impact on the other databases. At this point it
> appears as though we would have to do performance testing of our
> software on all supported databases to determine if the impact of the
> CAST is worth supporting Derby as well. However, if this is a bug which
> will be fixed in a future release our efforts will be for naught.
> Clearly it would be preferable if we could avoid expending our resources
> on adapting and testing code which currently already works. We are
> trying to assess whether the Derby community considers this issue a bug
> before making our decision about whether or not our software will
> support Derby.
I think it's a bug, similar to DERBY-7 (sorry for the earlier
confusion). Since this is open source, rather than spending the time
modifying your application you could get involved in the development of
Derby. The community is always open to new folks, please join.
Subscribe to derby-dev, read the wiki, and ask questions on derby-dev.
http://wiki.apache.org/db-derby/DerbyDev
Dan.
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
Neither type is assignable to the other type.
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
IMHO, any data type could be automatically treated as null (and null
should be automatically cast to any data type - depending only on
expression context), and your CASE expression is fine (analyzing the
syntax, is just correct).
So, it's a Derby bug, of course, and should be fixed ASAP (but it's only
MHO).
I have several multi-database applications, and is very weird having 3
or more versions of each report just because some database need some
casting (like this case), or don't accept some kind of join.
To have one idea, any report using MaxDB and aggregate functions that
returns decimals, need to start the expression with 1.0*(the expression)
or you will be out of decimals in your report. This occur since SapDB
days, and was never :( fixed - this is just an example I know (there is
lot's more on MaxDB product), and is the reason we don't support MaxDB
any more in our software...
Derby, in general, don't show this weird behaviors, but your case
expression made me re-evaluate all my reports to check if I'm not going
to show wrong results to my customers due to this bug.
Regards,
Richter
Peterson, John escreveu:
> Our concern with casting NULL to the appropriate type is that it might
> have a performance impact on the other databases. At this point it
> appears as though we would have to do performance testing of our
> software on all supported databases to determine if the impact of the
> CAST is worth supporting Derby as well. However, if this is a bug which
> will be fixed in a future release our efforts will be for naught.
> Clearly it would be preferable if we could avoid expending our resources
> on adapting and testing code which currently already works. We are
> trying to assess whether the Derby community considers this issue a bug
> before making our decision about whether or not our software will
> support Derby.
>
> Thanks,
> John
>
RE: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.
Posted by "Peterson, John" <Jo...@pega.com>.
Our concern with casting NULL to the appropriate type is that it might
have a performance impact on the other databases. At this point it
appears as though we would have to do performance testing of our
software on all supported databases to determine if the impact of the
CAST is worth supporting Derby as well. However, if this is a bug which
will be fixed in a future release our efforts will be for naught.
Clearly it would be preferable if we could avoid expending our resources
on adapting and testing code which currently already works. We are
trying to assess whether the Derby community considers this issue a bug
before making our decision about whether or not our software will
support Derby.
Thanks,
John
-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
Sent: Thursday, July 27, 2006 3:53 PM
To: Derby Discussion
Subject: Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type
compatible. Neither type is assignable to the other type.
Peterson, John wrote:
> Hello,
>
> It was reported to me that one of the reasons our software currently
> doesn't work with Apache Derby is because of ERROR 42X89. One of the
> engineering teams reported: "We found that there is a bug in Derby in
> its support for the Case statement. There is a workaround but that
> will require us to change the SQL generation code to specifically
> check if we're connected to Derby and generate a different SQL
> syntax." After inquiring after the specifics, I was provided with the
> following
> example:
>
> ij>values case when 1=2 then 3 else NULL end;
> ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
> Neither type is assignable to the other type.
>
> Is this indeed a bug (that will be fixed in the future), or is Derby
> behaving as it was intended?
Hello John,
I can't answer your specific question, but what is the workaround you
know of?
For instance, changing the query to "values case 1=2 then 3 else
case(NULL as INT) end;", makes it work. Maybe this is supported by (all)
other database systems as well.
Seems to me Derby should be able to handle this on its own, but I know
very little about this issue. I'm sure someone with more knowledge about
the implementation of this part of Derby will speak up.
It would also be good to know what is said in the standards on this
issue.
Regards,
--
Kristian
>
> Thanks for your help,
> John
>
>
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
Neither type is assignable to the other type.
Posted by Kristian Waagan <Kr...@Sun.COM>.
Peterson, John wrote:
> Hello,
>
> It was reported to me that one of the reasons our software currently
> doesn't work with Apache Derby is because of ERROR 42X89. One of the
> engineering teams reported: "We found that there is a bug in Derby in
> its support for the Case statement. There is a workaround but that will
> require us to change the SQL generation code to specifically check if
> we're connected to Derby and generate a different SQL syntax." After
> inquiring after the specifics, I was provided with the following
> example:
>
> ij>values case when 1=2 then 3 else NULL end;
> ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
> Neither type is assignable to the other type.
>
> Is this indeed a bug (that will be fixed in the future), or is Derby
> behaving as it was intended?
Hello John,
I can't answer your specific question, but what is the workaround you
know of?
For instance, changing the query to "values case 1=2 then 3 else
case(NULL as INT) end;", makes it work. Maybe this is supported by (all)
other database systems as well.
Seems to me Derby should be able to handle this on its own, but I know
very little about this issue. I'm sure someone with more knowledge about
the implementation of this part of Derby will speak up.
It would also be good to know what is said in the standards on this issue.
Regards,
--
Kristian
>
> Thanks for your help,
> John
>
>
RE: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither
type is assignable to the other type.
Posted by Prasenjit Sarkar <ps...@almaden.ibm.com>.
I would second this issue - it cost us a lot of time to change all integer
case statements using CAST AS CHAR...
Prasenjit Sarkar
Research Staff Member
Master Inventor
Storage Systems
IBM Almaden Research
"Peterson, John"
<John.Peterson@pe
ga.com> To
"Derby Discussion"
07/27/2006 01:30 <de...@db.apache.org>
PM cc
Subject
Please respond to RE: ERROR 42X89: Types 'INTEGER'
"Derby and 'CHAR' are not type compatible.
Discussion" Neither type is assignable to the
<derby-user@db.ap other type.
ache.org>
Dan,
I forgot to mention that I verified the reported behavior both on
release 10.1.3.1 and on snapshot 10.2.0.4-423199. The DERBY-7 issue
appears to be different, as it relates to the nullif() function rather
than the case statement.
John
-----Original Message-----
From: Daniel John Debrunner [mailto:djd@apache.org]
Sent: Thursday, July 27, 2006 4:20 PM
To: Derby Discussion
Subject: Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type
compatible. Neither type is assignable to the other type.
Peterson, John wrote:
> Hello,
>
> It was reported to me that one of the reasons our software currently
> doesn't work with Apache Derby is because of ERROR 42X89. One of the
> engineering teams reported: "We found that there is a bug in Derby in
> its support for the Case statement. There is a workaround but that
> will require us to change the SQL generation code to specifically
> check if we're connected to Derby and generate a different SQL
> syntax." After inquiring after the specifics, I was provided with the
> following
> example:
>
> ij>values case when 1=2 then 3 else NULL end;
> ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
> Neither type is assignable to the other type.
>
> Is this indeed a bug (that will be fixed in the future), or is Derby
> behaving as it was intended?
I think this is DERBY-7
http://issues.apache.org/jira/browse/DERBY-7
which was fixed in the 10.1.3 release.
Dan.
RE: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible. Neither type is assignable to the other type.
Posted by "Peterson, John" <Jo...@pega.com>.
Dan,
I forgot to mention that I verified the reported behavior both on
release 10.1.3.1 and on snapshot 10.2.0.4-423199. The DERBY-7 issue
appears to be different, as it relates to the nullif() function rather
than the case statement.
John
-----Original Message-----
From: Daniel John Debrunner [mailto:djd@apache.org]
Sent: Thursday, July 27, 2006 4:20 PM
To: Derby Discussion
Subject: Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type
compatible. Neither type is assignable to the other type.
Peterson, John wrote:
> Hello,
>
> It was reported to me that one of the reasons our software currently
> doesn't work with Apache Derby is because of ERROR 42X89. One of the
> engineering teams reported: "We found that there is a bug in Derby in
> its support for the Case statement. There is a workaround but that
> will require us to change the SQL generation code to specifically
> check if we're connected to Derby and generate a different SQL
> syntax." After inquiring after the specifics, I was provided with the
> following
> example:
>
> ij>values case when 1=2 then 3 else NULL end;
> ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
> Neither type is assignable to the other type.
>
> Is this indeed a bug (that will be fixed in the future), or is Derby
> behaving as it was intended?
I think this is DERBY-7
http://issues.apache.org/jira/browse/DERBY-7
which was fixed in the 10.1.3 release.
Dan.
Re: ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
Neither type is assignable to the other type.
Posted by Daniel John Debrunner <dj...@apache.org>.
Peterson, John wrote:
> Hello,
>
> It was reported to me that one of the reasons our software currently
> doesn't work with Apache Derby is because of ERROR 42X89. One of the
> engineering teams reported: "We found that there is a bug in Derby in
> its support for the Case statement. There is a workaround but that will
> require us to change the SQL generation code to specifically check if
> we're connected to Derby and generate a different SQL syntax." After
> inquiring after the specifics, I was provided with the following
> example:
>
> ij>values case when 1=2 then 3 else NULL end;
> ERROR 42X89: Types 'INTEGER' and 'CHAR' are not type compatible.
> Neither type is assignable to the other type.
>
> Is this indeed a bug (that will be fixed in the future), or is Derby
> behaving as it was intended?
I think this is DERBY-7
http://issues.apache.org/jira/browse/DERBY-7
which was fixed in the 10.1.3 release.
Dan.