db-derby-dev mailing list archives

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

> So the implementation is trying to process a conforming SQL language in a nonconforming
> manner, so it should raise a syntax exception.

No, the contrary is true. The standard says "... and the implementation is neither ... nor
processing conforming SQL language in a nonconforming manner, then an exception condition
is raised" (note the "nor" in the sentence), This means that if the "implementation is trying
to process a conforming SQL language in a nonconforming manner" (as you have written), it
does not have to raise an exception.

The purpose of the conformance rules of the SQL standard is to define a common subset of SQL
that is portable among the DBMS. Non-conforming SQL may be detected by an SQL flagger (see
ISO 9075-1 8.5), but it's not forbidden for a conforming implementation. The standard only
demands that conforming SQL behaves in the predicted way. All DBMS have non-conforming SQL
syntax.

> think the conflict here is that you see this as a proprietary case expression behavior
> in Derby where as I see this a bug since the current implementation of case expression
> does not conform to the SQL:2003 spec.

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.

ISO 9075-1 8.6.2 (Requirements for SQL-implementations) states:
"A conforming SQL-implementation shall process conforming SQL language according to the associated
General Rules, Definitions, and Descriptions."
This means that for conforming SQL (the common subset of the DBMS), the implementation must
obey the rules. But for non-conforming SQL, the implementation is free to do what it likes.


> 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