db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John Peterson (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-1620) SQL CASE statement returns ERROR 42X89 when including NULL as a return value
Date Fri, 03 Nov 2006 16:41:19 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1620?page=all ]

John Peterson updated DERBY-1620:

    Attachment: ConditionalNode.diff

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

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:
>         Environment: Windows XP
>            Reporter: John Peterson
>         Attachments: ConditionalNode.diff, Derby_Community_Discussion.doc, derbyall_report.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


View raw message