db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-1576) Extend the CASE expression syntax for "simple case"
Date Mon, 26 May 2014 14:07:02 GMT

     [ 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:
>       CASE 4
>          WHEN 1 THEN 'one'
>          WHEN 2 THEN 'two'
>          WHEN 3 THEN 'three'
>          ELSE 'many'
>       END

This message was sent by Atlassian JIRA

View raw message