[ http://issues.apache.org/jira/browse/DERBY-1620?page=all ]
John Peterson updated DERBY-1620:
---------------------------------
Attachment: ConditionalNode.diff
derbyall_report.txt
The org.apache.derby.impl.sql.compile.SQLParser parses NULL's found in SQL case
expressions into UntypedNullConstantNode nodes and casts those nodes to the
CHAR type. (See SQLParser.caseExpression() on line ~13180).
The DERBY-7 bug fix took this into account to enable the NULLIF() function to
work properly, but this code is only executed if NULLIF() is parsed. (See
SQLParser.valueSpecification() on line ~13111). The SQL equivalent to
NULLIF(), which is CASE V1=V2 THEN NULL ELSE V1 END, is not flagged to be given
this special consideration, and therefore Derby returns the 42X89 error. The
error also afflicts the related statements CASE V1=V2 THEN NULL ELSE V3 and
CASE V1=V2 THEN V3 ELSE NULL.
In the attached proposed patch of
org.apache.derby.impl.sql.compile.ConditionalNode, Derby will now look more
closely at the "then" and "else" nodes during the bindExpression() method. If
the node meets the following four conditions, then it is cast to the type of
the other node. Otherwise nothing happens, and Derby will proceed as usual.
1) The "then" or "else" node is a CAST_NODE
2) The CAST_NODE node operand is an UntypedNullConstantNode
3) The other node ("else" or "then") has a type service
4) The "else" and "then" nodes don't have the same type
These four conditions ensure only NULL's will be cast, and that they'll only be
cast if the other node has a type assigned to it that is different than CHAR.
We have been testing this fix by using our product with Derby, and it appears
to be doing the job. I've also executed the derbyall test suite, and the
problems which occurred do not seem to stem from this change. The next step is
a more extensive testing using all possible database types. I'm planning on
moving forward with that, but I wanted to post this patch for review and
comment.
> SQL CASE statement returns ERROR 42X89 when including NULL as a return value
> ----------------------------------------------------------------------------
>
> Key: DERBY-1620
> URL: http://issues.apache.org/jira/browse/DERBY-1620
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1
> Environment: Windows XP
> Reporter: John Peterson
> 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:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
