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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 > >
