[
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-1a.diff
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.
> 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, 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)
