[jira] [Updated] (DERBY-1576) Extend the CASE expression syntax for "simple case"

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-1576:
--------------------------------------

    Attachment: simple-simple.diff

The attached patch simple-simple.diff appears to make the simplest of the simple case expressions work. I had expected problems when grafting a single ValueNode (the case operand) into the tree multiple times, as that would mean bind and code generation would run multiple times on that node. I didn't think that was supported, but I may be wrong. Nothing seemed to pop up in the test cases I've run so far, at least.

The current patch does not support the more advanced features of simple case expressions, such as multiple values in a single WHEN clause, or use of BETWEEN/LIKE operators. Neither does it prevent the use of non-deterministic functions (such as RANDOM) in the WHEN operand, and the WHEN operand is evaluated multiple times.

But here goes:

{noformat}
ij version 10.11
ij> CONNECT 'jdbc:derby:memory:db;create=true';
ij> SELECT
    CASE i
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        WHEN 3 THEN 'three'
        ELSE 'many'
    END
    FROM (VALUES 1, 2, 3, 4) v(i);
1    
-----
one  
two  
three
many 

4 rows selected
ij> SELECT
    CASE COUNT(i)
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        WHEN 3 THEN 'three'
        ELSE 'many'
    END
    FROM (VALUES 1, 2, 3, 4) v(i);
1    
-----
many 

1 row selected
ij> SELECT
    CASE INT(RANDOM() * 3)
        WHEN 0 THEN 'a'
        WHEN 1 THEN 'b'
        WHEN 2 THEN 'c'
        ELSE 'Now, that''s very odd!'
    END
    FROM SYS.SYSSCHEMAS;
1                    
---------------------
Now, that's very odd!
Now, that's very odd!
Now, that's very odd!
Now, that's very odd!
a                    
c                    
c                    
b                    
c                    
Now, that's very odd!
b                    

11 rows selected
{noformat}

> 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
>         Attachments: 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)