[
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13985423#comment-13985423
]
Lukas Eder commented on DERBY-1576:
-----------------------------------
Very nice, I wasn't aware of this interpretation of the SQL standard's <when
operand>, thanks for pointing that out. This appears to have been added in
SQL:2003, only - so this issue could be split into two:
- Implementing the SQL-1999 simple CASE expression
- Implementing the SQL-2003 simple CASE expression extensions
I had given this some more thought. I suspect that the simple CASE expression
is actually not equivalent to the searched CASE expression. Consider something
like:
{code}
-- Assuming random() or some other non-deterministic function exists
CASE random(0, 2)
WHEN 0 THEN 'a'
WHEN 1 THEN 'b'
WHEN 2 THEN 'c'
END
{code}
The above wouldn't be equivalent to:
{code}
CASE
WHEN random(0, 2) = 0 THEN 'a'
WHEN random(0, 2) = 1 THEN 'b'
WHEN random(0, 2) = 2 THEN 'c'
END
{code}
On the other hand, the standard says:
{quote}
6.12 <case expression>
2) If a <case specification> specifies a <simple case>, then let CO be the
<case operand>.
a) CO shall not generally contain a <routine invocation> whose subject
routine is an SQL-invoked routine that is possibly non-deterministic or that
possibly modifies SQL-data.
{quote}
... so calling random() would be illegal anyway
> 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
> Priority: Minor
> Labels: derby_triage10_11
>
> 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)
