[
https://issues.apache.org/jira/browse/DERBY-6581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14007062#comment-14007062
]
Knut Anders Hatlen commented on DERBY-6581:
-------------------------------------------
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.
{quote}
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).
{quote}
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
{noformat}
valueExpression |
predicatePart2
{noformat}
and then define {{predicatePart2}} as any multi-legged predicate in the
"Boolean expressions" topic without the left predicand. That saves some
duplication, at least.
{quote}
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?
{quote}
I believe the syntax is correct. See above for details.
{quote}
And are AND, OR, NOT expressions not permitted?
{quote}
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}}.
{quote}
3) In one paragraph you mention the definition of "caseOperand" – did you mean
"whenOperand"?
{quote}
Yes, that was a typo. Corrected now.
> Document simple case syntax
> ---------------------------
>
> Key: DERBY-6581
> URL: https://issues.apache.org/jira/browse/DERBY-6581
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.11.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Kim Haase
>
> 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":
> {noformat}
> CASE valueExpression
> WHEN whenOperand [, whenOperand ]* THEN thenExpression
> [ WHEN whenOperand [, whenOperand ]* THEN thenExpression ]*
> [ ELSE elseExpression ]
> END
> {noformat}
> {{whenOperand}} could be defined like this:
> {noformat}
> 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
> {noformat}
> 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:
> {code:sql}
> -- 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
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
