[ 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
