[
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
The results of the test indicate that problems occur when trying to cast the
casted NULL into something that can not be converted from a CHAR, (e.g. From
line 2090 of resultset.tmp:
SELECT CASE WHEN 1 = 1 THEN REALCOL ELSE NULL END from AllDataTypesTable
ERROR 42846: Cannot convert types 'CHAR' to 'REAL'. )
Even though the statement is no longer issuing the 42X89 error, it's still not
up to SQL Standard. Since I am unsure of the impact of changing the SQLParser
(which sets NULL's to type CHAR), I'm going to change the cast type from CHAR
to REAL instead of casting the casted NULL to a REAL (causing the error). The
good news is that this will be a substantial improvement for anyone who wants
to use NULL's with a type that can't be casted from a CHAR in a CASE
Expression, for which there is currently no workaround.
John
> 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,
> Derby_Community_Discussion.doc, derbyall_report.txt, 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.
