[jira] [Commented] (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:comment-tabpanel&focusedCommentId=14013416#comment-14013416 ] 

Knut Anders Hatlen commented on DERBY-2002:
-------------------------------------------

Currently, if the type of an untyped NULL in a case expression cannot be inferred, it defaults to CHAR(1). Derby also allows untyped parameters in the case result clauses, but the type inference is different for them. This leads to some confusing behaviour:

Examples:

1) CASE WHEN x THEN NULL ELSE NULL END evaluates to a value of type CHAR(1)

2) CASE WHEN x THEN ? ELSE ? END produces an error:

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

3) CASE WHEN x THEN ? END evaluates to a value of type CHAR(1). It gets the type from the implicit ELSE NULL clause. However, even though the metadata says the type is CHAR(1), the parameter accepts longer values, and the returned value will in that case be longer than the metadata says it can be.

4) CASE WHEN x THEN CAST(? AS CHAR(1)) END evaluates to a value of type CHAR(1). However, unlike (3), when the parameter is given a longer value, that value will be truncated to length 1, and a data truncation warning will be added to the ResultSet.

I think it would be reasonable to expect cases 1, 2 and 3 to have the same behaviour. Implying the type CHAR(1) when no type is specified feels somewhat arbitrary, so I'm leaning towards raising an error and let the users disambiguate their queries.

If, however, making 1, 2 and 3 all use type CHAR(1) ends up being the preferred solution, I think the behaviour of 4 (truncate longer values and produce a warning) is less wrong than returning a value that is longer than its metadata permits.

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