[
https://issues.apache.org/jira/browse/DERBY-1620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12463374
]
John Peterson commented on DERBY-1620:
--------------------------------------
A comment in the
org.apache.derby.impl.sql.compile.SQLParser.valueSpecification() indicates an
assumption that the NULL's in an SQL statement will be taken care of at bind
time, from this I'm deducing that it's not the parser's responsibility to
ensure NULL's are cast correctly. Perhaps it would be a more complete and
efficient solution for the SQLParser to parse NULL's into a currently
non-existent NULL type and modify Derby on a larger more complex scale, but I
suspect this may not be an ideal solution because there currently doesn't seem
to be any demand for a NULL type other than this particular bug. With these
thoughts as my motivation, I've created a new patch that will handle NULL's in
nested CASE statements. I will provide more extensive tests and their results
shortly, however, in the meantime opinions on this are welcome and invited.
> 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, 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
