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