[jira] [Updated] (DERBY-2002) Case expression allows NULL in all parts of

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-2002?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-2002:
--------------------------------------

    Attachment: d2002-1a.diff

The attached patch [^d2002-1a.diff] shows how we can forbid CASE expressions with unknown return type. It removes the code that makes these expressions get type CHAR(1) and instead raises the same error condition as is currently raised if all THEN/ELSE expressions are untyped parameters. The error message is changed from

ERROR 42X87: At least one result expression (THEN or ELSE) of the 'conditional' expression must not be a '?'.

to

ERROR 42X87: At least one result expression (THEN or ELSE) of the CASE expression must have a known type.

The patch also adds test cases to CaseExpressionTest that verify the fix.

All regression tests passed with the patch.

> Case expression allows NULL in all parts of <result>
> ----------------------------------------------------
>
>                 Key: DERBY-2002
>                 URL: https://issues.apache.org/jira/browse/DERBY-2002
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6, 10.3.1.4
>         Environment: Any
>            Reporter: Yip Ng
>              Labels: derby_triage10_5_2
>         Attachments: d2002-1a.diff
>
>
> 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 was sent by Atlassian JIRA
(v6.2#6252)