db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John Peterson (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1620) SQL CASE statement returns ERROR 42X89 when including NULL as a return value
Date Tue, 01 Aug 2006 18:17:17 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1620?page=comments#action_12424967 ] 
            
John Peterson commented on DERBY-1620:
--------------------------------------

It has been brought to my attention that the SQL2003_Standard_Case_Expression.doc is a violation
of the ISO copyright, I suggest it be removed as soon as possible.  Instead, please refer
to http://en.wikipedia.org/wiki/SQL:2003 for a link to a .zip file (sql_2003_standard.zip),
and open the 5WD-02-Foundation-2003-09.pdf within.  Turn to page 197 (221 of 1332) (Chapter
6.11) to view the Case Expression standard.

> 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: Derby_Community_Discussion.doc, SQL2003_Standard_Case_Expression.doc,
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

        

Mime
View raw message