[
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-3a.diff
I think it would be good if the case operand of a simple case expression is
evaluated only once per evaluation of a case expression.
One reason is performance. It is a waste of time to re-evaluate the operand for
every when clause, especially if the operand calls an expensive function or
contains a sub-query.
Another reason is to make the results more reliable. As mentioned in earlier
comments, the results could be unreliable if the case operand calls a
non-deterministic function such as RANDOM. That specific case has been
explicitly forbidden, but I think there could be other cases where there's a
possibility for getting different results when re-evaluating the expression.
Especially if it contains a sub-query. For example, the optimizer might choose
different execution plans for two occurrences of the same sub-query. Also, with
transaction isolation levels lower than serializable, the result of the
sub-query might change because of activity in other transactions.
The attached patch [^d1576-3a.diff] makes the case expression cache the result
of the case operand when the first when clause is evaluated, and reuse that
value in the other when clauses. It uses the CachedValueNode class from the 1a
patch attached to DERBY-6569 with two improvements: (1) It adds a categorize()
method that was found to be missing during the testing of the patch for
DERBY-6569. (2) It adds code for forgetting the cached value after the case
expression has been evaluated, so that it doesn't stay in memory unnecessarily
long.
The patch also adds some more test cases to verify that the case operand is
evaluated only once, and that it works with large objects.
All regression tests passed with the patch.
> 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, d1576-3a.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)
