[
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-1576:
--------------------------------------
Attachment: d1576-2a.diff
I mentioned that there were problems with untyped parameters in the first
patch. The problem is if the case operand is an untyped parameter. The current
type inference code isn't prepared for a rewrite of the kind that happens here,
where the untyped parameter ends up multiple places in the AST.
Take for example the expression {{CASE ? WHEN 1.1 THEN 1 WHEN 2 THEN 2 END}}.
It gets rewritten to {{CASE WHEN (?=1.1) THEN 1 WHEN (?=2) THEN 2 END}}. Type
inference will be performed twice on the parameter. First as part of the
expression (?=1.1), which suggests that it should be a double. Later, it's
performed on (?=2), which suggests that it should be an integer. With the
current type inference, the type found in the latter overwrites the type found
in the former, so the type of the parameter ends up as integer. This means that
the parameter value will always be converted to an integer, and it will never
match the first WHEN clause.
Other examples: {{case ? when 1 then true when like ''abc'' then false end}}
won't detect the type mismatch (number vs string) at compile time, and may or
may not fail at execution depending on the actual value passed in. {{case ?
when like ''abc'' then false when 1 then true end}} falls over with an assert
failure at compile time in debug builds (and probably an AbstractMethodError at
execution time in production builds).
The type inference can probably be improved to do the right thing in these
expressions too, but for now I'll just forbid untyped parameters in the case
operand to prevent running into these other problems. Patch [^d1576-2a.diff]
adds that restriction. All regression tests passed with the patch.
Note that it doesn't forbid parameters altogether in the case operand, only
untyped ones. You can still do {{CASE CAST(? AS DOUBLE) WHEN 1.1 THEN 1 WHEN 2
THEN 2 END}}.
> Extend the CASE expression syntax for "simple case"
> ---------------------------------------------------
>
> Key: DERBY-1576
> URL: https://issues.apache.org/jira/browse/DERBY-1576
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Christian d'Heureuse
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Labels: derby_triage10_11
> Attachments: d1576-1a.diff, d1576-2a.diff, simple-simple.diff
>
>
> The ISO/IEC 9075-2:1999 SQL standard describes two kinds of CASE expressions:
> "simple case" and "searched case".
> The current Derby version supports "searched case" but not "simple case".
> The syntax for "simple case" is:
> CASE Expression
> WHEN Expression THEN Expression
> [ WHEN Expression THEN Expression ]
> ...
> ELSE ElseExpression
> END
> Example:
> VALUES
> CASE 4
> WHEN 1 THEN 'one'
> WHEN 2 THEN 'two'
> WHEN 3 THEN 'three'
> ELSE 'many'
> END
--
This message was sent by Atlassian JIRA
(v6.2#6252)
