[ https://issues.apache.org/jira/browse/DERBY-1620?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
John Peterson updated DERBY-1620: --------------------------------- Attachment: resultset.tmp ConditionalNode.diff The latest patch, test code, and test results. The latest change was a simple one. If required, the NULL is relieved of it's CHAR association and recast to a more appropriate type. > SQL CASE statement returns ERROR 42X89 when including NULL as a return value > ---------------------------------------------------------------------------- > > Key: DERBY-1620 > URL: https://issues.apache.org/jira/browse/DERBY-1620 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.1.6 > Environment: Windows XP > Reporter: John Peterson > Assigned To: John Peterson > Priority: Minor > Attachments: ConditionalNode.diff, ConditionalNode.diff, > ConditionalNode.diff, Derby_Community_Discussion.doc, derbyall_report.txt, > resultset.tmp, resultset.tmp, sysinfo_and_example.txt > > > This bug appears to be related to the DERBY-7 bug (NULLIF() function). When > NULL is used during a CASE statement, Derby requires the NULL to be CAST to > the appropriate type. This does not appear to meet the SQL 2003 Standard for > the Case Expression (see attached Word document). See the attached Word > document to view the Derby Community Discussion about this issue. See the > attached .TXT to view the SYSINFO and to see an example of the steps to > reproduce using IJ. > Steps to Reproduce: > 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. > Current Workaround: > ij>values case when 1=2 then 3 else cast(NULL as INT) end; -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.