[
https://issues.apache.org/jira/browse/DERBY-1620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463051
]
John Peterson commented on DERBY-1620:
--------------------------------------
Yes, that comment isn't very clear. I was trying to remind the reader
that the SQLParser parses NULL's in the CASE expression into
UntypedNullConstantNode's and then casts those nodes into CHAR's. Thus
the only situation in which the case statement is returning a CHAR will
this check need to be performed. Ultimately to avoid having the NULL
casted to a CHAR for a second time (duplicate work). Thanks for
pointing it out, I will have to clarify.
I was working on some test cases, but while writing them I realized that
patch I provided doesn't handle nested WHEN's. A rather severe
oversight. So once solution is to recursively examine "else" nodes to
determine if they are another conditional node. I have been delaying
updating the code, however, because it's not clear to me as to why Derby
is casting NULL's into CHAR's in the first place. This question of
course begs a more detailed look into the underlying Derby code than I
have yet undergone. In fact, I am planning on posing it to the Derby
community, for I would be most interested in any feedback.
> 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.txt,
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
