Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: 10.11.1.1
    • Component/s: Documentation
    • Labels:
      None

      Description

      We should document the new syntax added in DERBY-1576 in the CASE expression topic of the reference manual.

      The variant that is currently documented in the CASE expression topic, is called "searched case". It should still be documented. In addition, the topic should show this syntax for "simple case":

      CASE valueExpression
        WHEN whenOperand [, whenOperand ]* THEN thenExpression
        [ WHEN whenOperand [, whenOperand ]* THEN thenExpression ]*
        [ ELSE elseExpression ]
      END
      

      whenOperand could be defined like this:

      valueExpression |
      { < | = | > | <= | >= | <> } expression |
      IS [ NOT ] NULL |
      [ NOT ] LIKE characterExpression WithWildCard [ ESCAPE 'escapeCharacter'] |
      [ NOT ] BETWEEN expression AND expression |
      [ NOT ] IN tableSubquery |
      [ NOT ] IN ( expression [, expression ]* ) |
      comparisonOperator { ALL | ANY | SOME } tableSubquery
      

      Most of this syntax is copied from the table in the Boolean expressions topic, so we may try to find some way to refactor it to avoid repetition here.

      There are some inconsistencies in this definition of whenOperand. One is that I think all occurrences of "expression" could be replaced with "valueExpression". Another is that line 2 lists all comparison operators explicitly, whereas line 8 uses the shorthand "comparisonOperator". I suppose both should do the same. If we choose to use the shorthand, we should probably also define comparisonOperator somewhere.

      Examples:

      
      -- returns 'two'
      VALUES
        CASE 1+1
          WHEN 1 THEN 'one'
          WHEN 2 THEN 'two'
          ELSE 'many'
        END
      
      -- returns 'odd', 'even', 'big'
      SELECT
        CASE X
          WHEN 1, 3, 5, 7, 9 THEN 'odd'
          WHEN 2, 4, 6, 8, 10 THEN 'even'
          ELSE 'big'
        END
      FROM
        (VALUES 5, 8, 12) AS V(X)
      
      -- returns ('long', 182), ('medium', 340), ('short', 20)
      SELECT DISTANCE, COUNT(*)
      FROM (SELECT
              CASE MILES
                WHEN < 250 THEN 'short'
                WHEN BETWEEN 250 AND 2000 THEN 'medium'
                WHEN > 2000 THEN 'long'
              END
            FROM FLIGHTS) AS F(DISTANCE)
      GROUP BY DISTANCE
      
      1. DERBY-6581.diff
        5 kB
        Kim Haase
      2. DERBY-6581-2.diff
        5 kB
        Kim Haase
      3. rrefcase.html
        9 kB
        Kim Haase
      4. rrefcase.html
        9 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          Thanks for the very thorough description, Knut. A few questions ...

          1) Currently instead of "whenOperand" we use "booleanExpression", linking to the "Boolean expressions" topic. Is there some reason to provide a complete syntax of boolean expressions in the CASE expression topic? It might be simpler just to point out any Boolean expressions that are not permitted in a CASE expression (if any).

          2) Is the syntax of "whenOperand" entirely correct? It appears that one of the possibilities is a construction consisting of just a comparison operator followed by an expression, when there has to be an expression on either side of the operator, doesn't there? And are AND, OR, NOT expressions not permitted?

          3) In one paragraph you mention the definition of "caseOperand" – did you mean "whenOperand"?

          Show
          Kim Haase added a comment - Thanks for the very thorough description, Knut. A few questions ... 1) Currently instead of "whenOperand" we use "booleanExpression", linking to the "Boolean expressions" topic. Is there some reason to provide a complete syntax of boolean expressions in the CASE expression topic? It might be simpler just to point out any Boolean expressions that are not permitted in a CASE expression (if any). 2) Is the syntax of "whenOperand" entirely correct? It appears that one of the possibilities is a construction consisting of just a comparison operator followed by an expression, when there has to be an expression on either side of the operator, doesn't there? And are AND, OR, NOT expressions not permitted? 3) In one paragraph you mention the definition of "caseOperand" – did you mean "whenOperand"?
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for picking up this issue, Kim.

          Before answering your questions, I'd like to stress one point that didn't come out very clearly in the issue description: This new syntax ("simple case") comes in addition to the existing syntax ("searched case"). It doesn't replace it. Neither is a superset of the other, so we need both syntax descriptions.

          1) Currently instead of "whenOperand" we use "booleanExpression", linking to the "Boolean expressions" topic. Is there some reason to provide a complete syntax of boolean expressions in the CASE expression topic? It might be simpler just to point out any Boolean expressions that are not permitted in a CASE expression (if any).

          In "searched case", the expression that comes after WHEN is a <search condition>, which we typically call booleanExpression in the Derby docs.

          In "simple case", whenOperand could be one of the following:

          • A valueExpression. (This is the only kind of expression allowed in the original simple case syntax in SQL:1999, by the way.) In this case, a simple case expression such as CASE expr1 WHEN expr2 THEN .... END is equivalent to the searched case expression CASE WHEN expr1 = expr2 THEN .... END. (In fact, the parser rewrites the simple case expression to the equivalent searched case.)
          • A predicate without its left predicand. In this case, a simple case expression such as CASE expr1 WHEN < 20 THEN .... END is equivalent to the searched case expression CASE WHEN expr1 < 20 THEN .... END.

          Almost operators in the "Boolean expressions" topic can be used in the latter variant. The exceptions are:

          • AND, OR, NOT: These operators do not create predicates, they are used to combine predicates.
          • EXISTS: Although it is a predicate, it doesn't take a left predicand, so it doesn't make sense to talk about this predicate without its left predicand.

          All the other operators mentioned in the "Boolean expressions" topic can be used.

          We might be able to define whenOperand like this instead

          valueExpression |
          predicatePart2
          

          and then define predicatePart2 as any multi-legged predicate in the "Boolean expressions" topic without the left predicand. That saves some duplication, at least.

          2) Is the syntax of "whenOperand" entirely correct? It appears that one of the possibilities is a construction consisting of just a comparison operator followed by an expression, when there has to be an expression on either side of the operator, doesn't there?

          I believe the syntax is correct. See above for details.

          And are AND, OR, NOT expressions not permitted?

          Those would be permitted by the valueExpression branch of the whenOperand production rule. So you can say CASE a WHEN b AND c THEN .... END, which would be equivalent to CASE WHEN a = (b AND c) THEN .... END.

          For OR, you also have the option of using comma-separated whenOperand}}s. For example, {{CASE a WHEN b, c THEN .... END is equivalent to CASE WHEN a = b OR a = c THEN .... END.

          3) In one paragraph you mention the definition of "caseOperand" – did you mean "whenOperand"?

          Yes, that was a typo. Corrected now.

          Show
          Knut Anders Hatlen added a comment - Thanks for picking up this issue, Kim. Before answering your questions, I'd like to stress one point that didn't come out very clearly in the issue description: This new syntax ("simple case") comes in addition to the existing syntax ("searched case"). It doesn't replace it. Neither is a superset of the other, so we need both syntax descriptions. 1) Currently instead of "whenOperand" we use "booleanExpression", linking to the "Boolean expressions" topic. Is there some reason to provide a complete syntax of boolean expressions in the CASE expression topic? It might be simpler just to point out any Boolean expressions that are not permitted in a CASE expression (if any). In "searched case", the expression that comes after WHEN is a <search condition> , which we typically call booleanExpression in the Derby docs. In "simple case", whenOperand could be one of the following: A valueExpression . (This is the only kind of expression allowed in the original simple case syntax in SQL:1999, by the way.) In this case, a simple case expression such as CASE expr1 WHEN expr2 THEN .... END is equivalent to the searched case expression CASE WHEN expr1 = expr2 THEN .... END . (In fact, the parser rewrites the simple case expression to the equivalent searched case.) A predicate without its left predicand. In this case, a simple case expression such as CASE expr1 WHEN < 20 THEN .... END is equivalent to the searched case expression CASE WHEN expr1 < 20 THEN .... END . Almost operators in the "Boolean expressions" topic can be used in the latter variant. The exceptions are: AND, OR, NOT: These operators do not create predicates, they are used to combine predicates. EXISTS: Although it is a predicate, it doesn't take a left predicand, so it doesn't make sense to talk about this predicate without its left predicand. All the other operators mentioned in the "Boolean expressions" topic can be used. We might be able to define whenOperand like this instead valueExpression | predicatePart2 and then define predicatePart2 as any multi-legged predicate in the "Boolean expressions" topic without the left predicand. That saves some duplication, at least. 2) Is the syntax of "whenOperand" entirely correct? It appears that one of the possibilities is a construction consisting of just a comparison operator followed by an expression, when there has to be an expression on either side of the operator, doesn't there? I believe the syntax is correct. See above for details. And are AND, OR, NOT expressions not permitted? Those would be permitted by the valueExpression branch of the whenOperand production rule. So you can say CASE a WHEN b AND c THEN .... END , which would be equivalent to CASE WHEN a = (b AND c) THEN .... END . For OR, you also have the option of using comma-separated whenOperand}}s. For example, {{CASE a WHEN b, c THEN .... END is equivalent to CASE WHEN a = b OR a = c THEN .... END . 3) In one paragraph you mention the definition of "caseOperand" – did you mean "whenOperand"? Yes, that was a typo. Corrected now.
          Hide
          Kim Haase added a comment -

          Thanks so much, Knut, for the explanations. I think I get it now. Simple case doesn't seem very simple to my feeble brain ... I was (and am) definitely planning to provide two different syntaxes, though.

          Show
          Kim Haase added a comment - Thanks so much, Knut, for the explanations. I think I get it now. Simple case doesn't seem very simple to my feeble brain ... I was (and am) definitely planning to provide two different syntaxes, though.
          Hide
          Knut Anders Hatlen added a comment - - edited

          Yes, "simple" has become a bit misleading. We might also consider giving three different syntax descriptions:

          1) The "searched case" that we already have.

          2) The "simple case" in its simplest form from SQL:1999 plus the SQL:2011 addition F263, “Comma-separated predicates in simple CASE expression”. That syntax would look like:

          CASE valueExpression
            WHEN valueExpression [ , valueExpression ]* THEN thenExpression
            [ WHEN valueExpression [ , valueExpression ]* THEN thenExpression ]*
            [ ELSE elseExpression ]
          END
          

          3) The full "simple case" syntax including SQL:2003 addition F262, “Extended CASE expression”, which would be identical to the syntax in the issue description, and call it "extended case".

          Although 2 is a subset of 3, and therefore strictly speaking redundant, splitting it up this way might make it easier to grasp for the majority who don't need the "extended" features.

          Show
          Knut Anders Hatlen added a comment - - edited Yes, "simple" has become a bit misleading. We might also consider giving three different syntax descriptions: 1) The "searched case" that we already have. 2) The "simple case" in its simplest form from SQL:1999 plus the SQL:2011 addition F263, “Comma-separated predicates in simple CASE expression”. That syntax would look like: CASE valueExpression WHEN valueExpression [ , valueExpression ]* THEN thenExpression [ WHEN valueExpression [ , valueExpression ]* THEN thenExpression ]* [ ELSE elseExpression ] END 3) The full "simple case" syntax including SQL:2003 addition F262, “Extended CASE expression”, which would be identical to the syntax in the issue description, and call it "extended case". Although 2 is a subset of 3, and therefore strictly speaking redundant, splitting it up this way might make it easier to grasp for the majority who don't need the "extended" features.
          Hide
          Kim Haase added a comment -

          Thanks, that makes more sense, I think.

          Are more than 2 comma-separated predicates and WHEN lines allowed in the really simple case?

          CASE valueExpression
            WHEN valueExpression [ , valueExpression ]* THEN thenExpression
            [ WHEN valueExpression [ , valueExpression ]* THEN thenExpression ]*
            [ ELSE elseExpression ]
          END
          
          Show
          Kim Haase added a comment - Thanks, that makes more sense, I think. Are more than 2 comma-separated predicates and WHEN lines allowed in the really simple case? CASE valueExpression WHEN valueExpression [ , valueExpression ]* THEN thenExpression [ WHEN valueExpression [ , valueExpression ]* THEN thenExpression ]* [ ELSE elseExpression ] END
          Hide
          Knut Anders Hatlen added a comment -

          Yes, you're right. I was missing some asterisks in my previous comment. I've edited it now.

          (The really, really simple case that was in SQL:1999, didn't allow more than one valueExpression per WHEN clause. But I don't think it causes much extra confusion to have that extension in our simple case. Multiple WHEN clauses were allowed even in SQL:1999.)

          Show
          Knut Anders Hatlen added a comment - Yes, you're right. I was missing some asterisks in my previous comment. I've edited it now. (The really, really simple case that was in SQL:1999, didn't allow more than one valueExpression per WHEN clause. But I don't think it causes much extra confusion to have that extension in our simple case. Multiple WHEN clauses were allowed even in SQL:1999.)
          Hide
          Kim Haase added a comment - - edited

          Thanks for all your help, Knut. Attaching DERBY-6581.diff and rrefcase.html, with modifications to the "CASE expression" topic:

          M src/ref/rrefcase.dita

          Thanks for your feedback. I hope the patch is not too far off the mark in correctness.

          Show
          Kim Haase added a comment - - edited Thanks for all your help, Knut. Attaching DERBY-6581 .diff and rrefcase.html, with modifications to the "CASE expression" topic: M src/ref/rrefcase.dita Thanks for your feedback. I hope the patch is not too far off the mark in correctness.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Kim! The changes look very good to me. +1 to commit.

          In the syntax we've copied for the LIKE expression, should we make characterExpression WithWildCard a single token? That is, characterExpressionWithWildCard. I suspect the reason why it's two tokens in the "Boolean expressions" topic, is that it's too wide for the table.

          Show
          Knut Anders Hatlen added a comment - Thanks, Kim! The changes look very good to me. +1 to commit. In the syntax we've copied for the LIKE expression, should we make characterExpression WithWildCard a single token? That is, characterExpressionWithWildCard . I suspect the reason why it's two tokens in the "Boolean expressions" topic, is that it's too wide for the table.
          Hide
          Knut Anders Hatlen added a comment -

          And while we're at the nitpicking... Another inconsistency we've copied from the "Boolean expressions" topic, is the lack of a space between 'escapeCharacter' and the closing square bracket. Should probably add a blank there.

          Show
          Knut Anders Hatlen added a comment - And while we're at the nitpicking... Another inconsistency we've copied from the "Boolean expressions" topic, is the lack of a space between 'escapeCharacter' and the closing square bracket. Should probably add a blank there.
          Hide
          Kim Haase added a comment -

          Thanks so much, Knut – I am sure you are right, and those nits are worth fixing. Attaching DERBY-6581-2.diff and an updated version of rrefcase.html. No rush on reviewing it, since I won't get around to committing the patch till after our long weekend.

          Show
          Kim Haase added a comment - Thanks so much, Knut – I am sure you are right, and those nits are worth fixing. Attaching DERBY-6581 -2.diff and an updated version of rrefcase.html. No rush on reviewing it, since I won't get around to committing the patch till after our long weekend.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Kim. Looks perfect! +1

          Show
          Knut Anders Hatlen added a comment - Thanks, Kim. Looks perfect! +1
          Hide
          ASF subversion and git services added a comment -

          Commit 1597780 from Kim Haase in branch 'docs/trunk'
          [ https://svn.apache.org/r1597780 ]

          DERBY-6581 Document simple case syntax

          Modified a Reference Manual topic.

          Patches: DERBY-6581-2.diff

          Show
          ASF subversion and git services added a comment - Commit 1597780 from Kim Haase in branch 'docs/trunk' [ https://svn.apache.org/r1597780 ] DERBY-6581 Document simple case syntax Modified a Reference Manual topic. Patches: DERBY-6581 -2.diff
          Hide
          Kim Haase added a comment -

          Thanks so much, Knut!

          Committed patch DERBY-6581-2.diff to documentation trunk at revision 1597780.

          Show
          Kim Haase added a comment - Thanks so much, Knut! Committed patch DERBY-6581 -2.diff to documentation trunk at revision 1597780.

            People

            • Assignee:
              Kim Haase
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development