db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2002) Case expression allows NULL in all parts of <result>
Date Sun, 29 Oct 2006 21:08:53 GMT
    [ http://issues.apache.org/jira/browse/DERBY-2002?page=comments#action_12445460 ] 
            
Yip Ng commented on DERBY-2002:
-------------------------------

>I agree that a CASE statement with all NULLs does not conform to the syntax rule of the
SQL standard and >therefore it's not conforming SQL. But a conforming implementation is
allowed to process non-conforming SQL >without raising an exception. 

Right, I am not disagreeing with you here.  Of course, the implementation is free from choosing
how to handle non-conforming SQL.    We both agree that  the CASE expression with all NULLs
violates syntax rule 3, making it non-conforming SQL which it is the reason why I filed this
jira in the first place - to make it *conforming SQL*.  So from a conforming perspective,
then it should raise the syntax exception which is what I tried to say in my previous comment.

But I have to admit that the current behavior is somewhat awkward since the implementation
has no way of deducing the resultant data type returned by such an expresson which will force
the implementation to default to some default datatype.  Not very portable for sure.   =)

If it is not handled in the conforming manner, then it should be documented in the reference
manual so that there are no surprises.


> Case expression allows NULL in all parts of <result>
> ----------------------------------------------------
>
>                 Key: DERBY-2002
>                 URL: http://issues.apache.org/jira/browse/DERBY-2002
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6, 10.3.0.0
>         Environment: Any
>            Reporter: Yip Ng
>
> According to the SQL:2003 spec, section 6.11 <case expression> Syntax Rule 3. 
At least one <result> in a 
> <case specification> shall specify a <result expression>.  Derby currently
is violating this rule.  e.g.:
> ij> values case when 1=2 then NULL when 1=3 then NULL else NULL end;
> 1
> ----
> NULL
> 1 row selected
> 6.11 <case expression> Format section defines <result> as the following:
> <result> ::= 
>     <result expression>
>     | NULL
> The above statement should have thrown a SQLException instead of returning a result.
> sysinfo:
> ------------------ Java Information ------------------
> Java Version:    1.4.2_12
> Java Vendor:     Sun Microsystems Inc.
> Java home:       C:\jdk142\jre
> Java classpath:  classes;.
> OS name:         Windows XP
> OS architecture: x86
> OS version:      5.1
> Java user name:  yip
> Java user home:  C:\Documents and Settings\Administrator
> Java user dir:   C:\derby\trunk
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.4
> --------- Derby Information --------
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
> [C:\derby\trunk\classes] 10.3.0.0 alpha - (1)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale :  [English/United States [en_US]]
> Found support for locale: [de_DE]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [es]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [fr]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [it]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [ja_JP]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [ko_KR]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [pt_BR]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [zh_CN]
>          version: 10.3.0.0 alpha - (1)
> Found support for locale: [zh_TW]
>          version: 10.3.0.0 alpha - (1)
> ------------------------------------------------------

-- 
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