[jira] Updated: (DERBY-1620) SQL CASE statement returns ERROR 42X89 when including NULL as a return value

["John Peterson (JIRA)" <ji...@apache.org>]

     [ 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