Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <21409397.1173791950190.JavaMail.jira@brutus>
Date: Tue, 13 Mar 2007 06:19:10 -0700 (PDT)
From: "John Peterson (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1620) SQL CASE statement returns ERROR
 42X89 when including NULL as a return value
In-Reply-To: <21161059.1154444113888.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-1620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480389 ] 

John Peterson commented on DERBY-1620:
--------------------------------------


I did run the entire test suite against it, and none of the problems
that occurred appeared to stem from the modification of the
ConditionalNode class.   My "extensive" testing can not be shared as
they are a set of proprietary queries not written by me against a
database not created by me.  You do make an excellent point, however, so
I'll write up a test suite that takes advantage of the existing tests
put into place by the author of the DERBY-7 fix.  I'll shoot for this
week.

John
 

> 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, 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.
-
You can reply to this email to add a comment to the issue online.