[ 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
