Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <6873566.1168188147742.JavaMail.jira@brutus>
Date: Sun, 7 Jan 2007 08:42:27 -0800 (PST)
From: "Bryan Pendleton (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_12462834 ] 

Bryan Pendleton commented on DERBY-1620:
----------------------------------------

The new version of the diff looks very good, the code reads very well and the comments are
very well-written and clear.

The only tiny little confusion I had is over sub-condition (4). In your 3-Nov comment you say:
      4) The "else" and "then" nodes don't have the same type
and that seems to be what the code implements. But in the code itself your comment says
     4) the other node's type isn't CHAR  (avoids duplicate work)
Can you explain that in just a bit more detail?

Also, would it be possible for you to include some tests? Perhaps some various examples
of CASE statements with nulls in various combinations, and also some various
examples of NULLIF function calls? I see that you indicated in your 3-Nov comment that
you were working on that; let us know if you've run into any snags or roadblocks.


> 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.txt, 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