[
https://issues.apache.org/jira/browse/DERBY-1620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480182
]
John Peterson commented on DERBY-1620:
--------------------------------------
The latest patch (March 12, 2007) fixes the two problems my last patch still
had issues with.
1) NULL's inside the THEN braches of the CASE Expressions where not being cast.
Causing the exception to be thrown.
(e.g. VALUES CASE WHEN 1 = 1 THEN (CASE WHEN 1 = 1 THEN NULL ELSE 1 END) ELSE
NULL END;)
2) Column References were ignored. Causing the exception to be thrown.
(e.g. SELECT Count(CASE WHEN 1 = 1 THEN aDecimalColumnName ELSE NULL END)
FROM aTableName;)
I have done extensive testing, and I believe this patch finally fixes this bug.
Therefore, once these changes are commited, I will close out this bug.
Thanks,
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, 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.
