Derby
  1. Derby
  2. DERBY-1576

Extend the CASE expression syntax for "simple case"

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.11.1.1
    • Component/s: SQL
    • Urgency:
      Low

      Description

      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

      1. simple-simple.diff
        3 kB
        Knut Anders Hatlen
      2. d1576-1a.diff
        22 kB
        Knut Anders Hatlen
      3. d1576-2a.diff
        7 kB
        Knut Anders Hatlen
      4. d1576-3a.diff
        17 kB
        Knut Anders Hatlen
      5. d1576-4a.diff
        22 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          I believe the work on this issue is done. Resolving.

          Show
          Knut Anders Hatlen added a comment - I believe the work on this issue is done. Resolving.
          Hide
          ASF subversion and git services added a comment -

          Commit 1598529 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1598529 ]

          DERBY-1576: Fix javadoc warning

          Show
          ASF subversion and git services added a comment - Commit 1598529 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1598529 ] DERBY-1576 : Fix javadoc warning
          Hide
          ASF subversion and git services added a comment -

          Commit 1598472 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1598472 ]

          DERBY-1576: Extend the CASE expression syntax for "simple case"

          Allow untyped parameters in the case operand.

          Show
          ASF subversion and git services added a comment - Commit 1598472 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1598472 ] DERBY-1576 : Extend the CASE expression syntax for "simple case" Allow untyped parameters in the case operand.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch d1576-4a.diff lifts the restriction that the case operand cannot be an untyped parameter.

          The original problem was that the case operand would forget its previously inferred type each time it was rebound, so that its final type would be the type of the last when operand, rather than the union of all the when operand types.

          The patch solves this by rewriting the query tree at the beginning of the bind phase so that all occurrences of the case operand are replaced with dummy parameter nodes, one dummy node per when clause in the case expression. This way, when the when clauses are bound, each dummy parameter node is bound once, so we avoid the problem with the previous type being forgotten when the node is rebound.

          At the end of the bind phase, we can go through the list of dummy nodes, find the dominant type of those nodes, set the type of the case operand to that type, and finally reinsert the case operand into the query tree.

          The patch touches these files:

          impl/sql/compile/ConditionalNode.java

          • bindCaseOperand: Temporarily replace the case operand with dummy parameter nodes.
          • bindExpression: After binding the test conditions, check the types that the dummy parameter nodes were bound to. If they are not compatible, raise an error. If they are compatible, set the type of the case operand to the dominant type, and reinsert it into the tree.

          impl/sql/compile/ReplaceNodeVisitor.java

          • New visitor implementation that replaces a node with some other node. Used for reinserting the case operand into the tree.

          impl/sql/compile/ValueNodeList.java

          • getDominantTypeServices: Improve the method so that it works with lists that consists entirely of nodes that require type from context, as long as they already have been bound to a type.

          loc/messages.xml
          shared/common/reference/SQLState.java

          • Remove the error message that used to be raised if the case operand was an untyped parameter.

          tests/lang/CaseExpressionTest.java

          • Add tests that use untyped parameters in the case operand.
          Show
          Knut Anders Hatlen added a comment - The attached patch d1576-4a.diff lifts the restriction that the case operand cannot be an untyped parameter. The original problem was that the case operand would forget its previously inferred type each time it was rebound, so that its final type would be the type of the last when operand, rather than the union of all the when operand types. The patch solves this by rewriting the query tree at the beginning of the bind phase so that all occurrences of the case operand are replaced with dummy parameter nodes, one dummy node per when clause in the case expression. This way, when the when clauses are bound, each dummy parameter node is bound once, so we avoid the problem with the previous type being forgotten when the node is rebound. At the end of the bind phase, we can go through the list of dummy nodes, find the dominant type of those nodes, set the type of the case operand to that type, and finally reinsert the case operand into the query tree. The patch touches these files: impl/sql/compile/ConditionalNode.java bindCaseOperand: Temporarily replace the case operand with dummy parameter nodes. bindExpression: After binding the test conditions, check the types that the dummy parameter nodes were bound to. If they are not compatible, raise an error. If they are compatible, set the type of the case operand to the dominant type, and reinsert it into the tree. impl/sql/compile/ReplaceNodeVisitor.java New visitor implementation that replaces a node with some other node. Used for reinserting the case operand into the tree. impl/sql/compile/ValueNodeList.java getDominantTypeServices: Improve the method so that it works with lists that consists entirely of nodes that require type from context, as long as they already have been bound to a type. loc/messages.xml shared/common/reference/SQLState.java Remove the error message that used to be raised if the case operand was an untyped parameter. tests/lang/CaseExpressionTest.java Add tests that use untyped parameters in the case operand.
          Hide
          ASF subversion and git services added a comment -

          Commit 1597979 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1597979 ]

          DERBY-1576: Extend the CASE expression syntax for "simple case"

          Cache the case operand so that it is only evaluated once per
          evaluation of the CASE expression.

          Show
          ASF subversion and git services added a comment - Commit 1597979 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1597979 ] DERBY-1576 : Extend the CASE expression syntax for "simple case" Cache the case operand so that it is only evaluated once per evaluation of the CASE expression.
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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.
          Hide
          ASF subversion and git services added a comment -

          Commit 1597082 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1597082 ]

          DERBY-1576: Extend the CASE expression syntax for "simple case"

          Add test cases for subqueries.

          Show
          ASF subversion and git services added a comment - Commit 1597082 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1597082 ] DERBY-1576 : Extend the CASE expression syntax for "simple case" Add test cases for subqueries.
          Hide
          ASF subversion and git services added a comment -

          Commit 1596492 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1596492 ]

          DERBY-1576: Extend the CASE expression syntax for "simple case"

          Forbid untyped parameters in the case operand.

          Show
          ASF subversion and git services added a comment - Commit 1596492 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1596492 ] DERBY-1576 : Extend the CASE expression syntax for "simple case" Forbid untyped parameters in the case operand.
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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 .
          Hide
          Christian d'Heureuse added a comment -

          Thanks, Knut. It has been 8 years since I created this Jira issue, but now we have got more than I asked for...

          Show
          Christian d'Heureuse added a comment - Thanks, Knut. It has been 8 years since I created this Jira issue, but now we have got more than I asked for...
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Dag. I've committed the 1a patch as a first increment.

          Show
          Knut Anders Hatlen added a comment - Thanks, Dag. I've committed the 1a patch as a first increment.
          Hide
          ASF subversion and git services added a comment -

          Commit 1595778 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1595778 ]

          DERBY-1576: Extend the CASE expression syntax for "simple case"

          Implement simple case as described by the SQL:1999 standard, including the
          SQL:2003 extension F262 "Extended CASE expression", and the SQL:2011
          extension F263 "Comma-separated predicates in simple CASE expression".

          Show
          ASF subversion and git services added a comment - Commit 1595778 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1595778 ] DERBY-1576 : Extend the CASE expression syntax for "simple case" Implement simple case as described by the SQL:1999 standard, including the SQL:2003 extension F262 "Extended CASE expression", and the SQL:2011 extension F263 "Comma-separated predicates in simple CASE expression".
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Knut! Nice cleanup and improvement; +1

          Show
          Dag H. Wanvik added a comment - Thanks, Knut! Nice cleanup and improvement; +1
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch d1576-1a.diff implements simple case, including the SQL:2003 extension F262 Extended CASE expression, and the SQL:2011 extension F263 Comma-separated predicates in simple CASE expression. It adds some bind logic to disallow non-deterministic function calls in the CASE operand to prevent the previously discussed problem with the RANDOM function. It also adds a test case.

          More testing and polishing will be needed, though. For example, I just discovered that the compile-time type checking of parameterized expressions is too loose, so that some type mismatches aren't discovered until execution time. But I think it is ready for a first check-in.

          Here's a description of what the patch does:

          CompilerContext: Add a bit mask that describes what's not allowed in a case operand.

          ConditionalNode: Add bind logic that disallows non-deterministic functions, and routines that possibly modify data, in the case operand. (The latter restriction is currently redundant, since Derby only allows procedures to modify data, and the only routines that can be called from the case operand, are functions.)

          QueryTreeNode: Fix throwReliabilityException() so that it throws an exception with a relevant error message if the case operand contains illegal calls. Without this fix, the error message would say the problem was with a CHECK CONSTRAINT.

          sqlgrammar.jj: Add the new syntax for simple case by rewriting simple case expressions to searched case expressions. Reorganize the syntax rule for search conditions so that IS [ NOT ] NULL is handled the same way as other predicates, which allows IS [ NOT ] NULL to be used as a when operand with no special handling.

          messages.xml, SQLState.java: Add the new error message for illegal calls in case operands. (The error message is quite generic, since it's difficult to determine reliably if it actually is a case operand that causes the problem, at the place where the exception is thrown.)

          CaseExpressionTest: Add tests for the new syntax.

          All regression tests ran cleanly with the patch.

          Show
          Knut Anders Hatlen added a comment - The attached patch d1576-1a.diff implements simple case, including the SQL:2003 extension F262 Extended CASE expression, and the SQL:2011 extension F263 Comma-separated predicates in simple CASE expression. It adds some bind logic to disallow non-deterministic function calls in the CASE operand to prevent the previously discussed problem with the RANDOM function. It also adds a test case. More testing and polishing will be needed, though. For example, I just discovered that the compile-time type checking of parameterized expressions is too loose, so that some type mismatches aren't discovered until execution time. But I think it is ready for a first check-in. Here's a description of what the patch does: CompilerContext : Add a bit mask that describes what's not allowed in a case operand. ConditionalNode : Add bind logic that disallows non-deterministic functions, and routines that possibly modify data, in the case operand. (The latter restriction is currently redundant, since Derby only allows procedures to modify data, and the only routines that can be called from the case operand, are functions.) QueryTreeNode : Fix throwReliabilityException() so that it throws an exception with a relevant error message if the case operand contains illegal calls. Without this fix, the error message would say the problem was with a CHECK CONSTRAINT. sqlgrammar.jj : Add the new syntax for simple case by rewriting simple case expressions to searched case expressions. Reorganize the syntax rule for search conditions so that IS [ NOT ] NULL is handled the same way as other predicates, which allows IS [ NOT ] NULL to be used as a when operand with no special handling. messages.xml , SQLState.java : Add the new error message for illegal calls in case operands. (The error message is quite generic, since it's difficult to determine reliably if it actually is a case operand that causes the problem, at the place where the exception is thrown.) CaseExpressionTest : Add tests for the new syntax. All regression tests ran cleanly with the patch.
          Hide
          Knut Anders Hatlen added a comment -

          There is also a difference between the simple case syntax in SQL:2003 and SQL:2011. In the SQL:2003 standard, a <simple when clause> can only have one <when operand>, whereas SQL:2011 allows the <simple when clause> to contain a <when operand list>. The example I gave in my 30/Apr/14 comment, used the 2011 syntax.

          I agree that it would be fine to only implement the SQL:1999 syntax in this issue, as that would be a useful improvement on its own, and that's the version of the standard specified in the original request.

          Show
          Knut Anders Hatlen added a comment - There is also a difference between the simple case syntax in SQL:2003 and SQL:2011. In the SQL:2003 standard, a <simple when clause> can only have one <when operand>, whereas SQL:2011 allows the <simple when clause> to contain a <when operand list>. The example I gave in my 30/Apr/14 comment, used the 2011 syntax. I agree that it would be fine to only implement the SQL:1999 syntax in this issue, as that would be a useful improvement on its own, and that's the version of the standard specified in the original request.
          Hide
          Knut Anders Hatlen added a comment -

          It might be better to make the simple case the primary implementation, so that it can have bind and code generation logic that prevents the when operand from being evaluated multiple times. We could still have a shared implementation between the simple case and the searched case by having the parser transform

          CASE WHEN a THEN b WHEN c THEN d ELSE e

          into

          CASE TRUE WHEN a THEN b WHEN c THEN d ELSE e

          That would be a simpler rewrite than the other way around.

          Show
          Knut Anders Hatlen added a comment - It might be better to make the simple case the primary implementation, so that it can have bind and code generation logic that prevents the when operand from being evaluated multiple times. We could still have a shared implementation between the simple case and the searched case by having the parser transform CASE WHEN a THEN b WHEN c THEN d ELSE e into CASE TRUE WHEN a THEN b WHEN c THEN d ELSE e That would be a simpler rewrite than the other way around.
          Hide
          Knut Anders Hatlen added a comment -

          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:

          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
          
          Show
          Knut Anders Hatlen added a comment - 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: 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
          Hide
          Lukas Eder added a comment -

          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:

          -- 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
          

          The above wouldn't be equivalent to:

          CASE
              WHEN random(0, 2) = 0 THEN 'a'
              WHEN random(0, 2) = 1 THEN 'b'
              WHEN random(0, 2) = 2 THEN 'c'
          END
          

          On the other hand, the standard says:

          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.

          ... so calling random() would be illegal anyway

          Show
          Lukas Eder added a comment - 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: -- 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 The above wouldn't be equivalent to: CASE WHEN random(0, 2) = 0 THEN 'a' WHEN random(0, 2) = 1 THEN 'b' WHEN random(0, 2) = 2 THEN 'c' END On the other hand, the standard says: 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. ... so calling random() would be illegal anyway
          Hide
          Knut Anders Hatlen added a comment - - edited

          I don't think there's any technical reason why this hasn't been done, just that no one has felt strongly enough about it to actually do the work yet. You may be right that simple case can be implemented mostly in the parser. The simple case syntax in the SQL standard is somewhat more involved than what's in the issue description, though, and allows expressions such as:

            CASE 'test'
               WHEN IS NULL THEN 0
               WHEN BETWEEN('abc', 'def'), LIKE 'x%', 'zzz' THEN 1
               ELSE 2
          
          Show
          Knut Anders Hatlen added a comment - - edited I don't think there's any technical reason why this hasn't been done, just that no one has felt strongly enough about it to actually do the work yet. You may be right that simple case can be implemented mostly in the parser. The simple case syntax in the SQL standard is somewhat more involved than what's in the issue description, though, and allows expressions such as: CASE 'test' WHEN IS NULL THEN 0 WHEN BETWEEN('abc', 'def'), LIKE 'x%', 'zzz' THEN 1 ELSE 2
          Hide
          Lukas Eder added a comment - - edited

          Hmm, apart from MS Access (which doesn't have a CASE expression at all), Derby is the only database among the 16 databases supported by http://www.jooq.org, which doesn't support this syntax.

          Is there any particular reason why this was omitted so far? I suspect that a canonical implementation would be merely syntactic sugar, implemented in the parser...

          Show
          Lukas Eder added a comment - - edited Hmm, apart from MS Access (which doesn't have a CASE expression at all), Derby is the only database among the 16 databases supported by http://www.jooq.org , which doesn't support this syntax. Is there any particular reason why this was omitted so far? I suspect that a canonical implementation would be merely syntactic sugar, implemented in the parser...

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Christian d'Heureuse
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development