You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Christian d'Heureuse (JIRA)" <de...@db.apache.org> on 2006/07/23 14:34:14 UTC
[jira] Created: (DERBY-1575) document full syntax of CASE
expression
document full syntax of CASE expression
---------------------------------------
Key: DERBY-1575
URL: http://issues.apache.org/jira/browse/DERBY-1575
Project: Derby
Issue Type: Improvement
Components: Documentation
Reporter: Christian d'Heureuse
The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
But the CASE expression supports more than one WHEN/THEN clauses.
I suggest to change the syntax description to:
CASE
WHEN BooleanExpression THEN Expression
[ WHEN BooleanExpression THEN Expression ]
...
ELSE elseExpression
END
Example:
VALUES
CASE
WHEN 1 = 2 THEN 3
WHEN 4 = 5 THEN 6
ELSE 7
END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1575) document full syntax of CASE
expression
Posted by "Kim Haase (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Kim Haase updated DERBY-1575:
-----------------------------
Derby Info: [Patch Available]
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Attachments: DERBY-1575.diff
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1575) document full syntax of CASE expression
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Jean T. Anderson closed DERBY-1575.
-----------------------------------
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1575) document full syntax of CASE
expression
Posted by "Christian d'Heureuse (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=comments#action_12431389 ]
Christian d'Heureuse commented on DERBY-1575:
---------------------------------------------
The new patch looks fine. Thanks for your work.
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1575) document full syntax of CASE
expression
Posted by "Kim Haase (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Kim Haase updated DERBY-1575:
-----------------------------
Attachment: DERBY-1575.diff
Here is a patch that I hope fixes the CASE syntax.
I noticed that the formatting in general was inconsistent with the formatting for the other built-in functions. So I also changed the file to use bold, parentheses, and capitalization similarly to the way the others do (though the others are not totally consistent either).
I also removed the semicolon from the SQL statement in the first CASE example, since it is not in an ij example (per the Documentation Style Issue thread on derby-dev on 8/21 and 8/22).
Please let me know if more changes are needed. Thanks!
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Attachments: DERBY-1575.diff
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1575) document full syntax of CASE
expression
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Jean T. Anderson updated DERBY-1575:
------------------------------------
Fix Version/s: 10.2.1.0
(was: 10.2.2.0)
Merged to 10.2 branch on Fri Sep 01.
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1575) document full syntax of CASE
expression
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Jean T. Anderson resolved DERBY-1575.
-------------------------------------
Fix Version/s: 10.3.0.0
Resolution: Fixed
Derby Info: (was: [Patch Available])
Committed patch DERBY-1575_fix.diff to trunk, revision 438290 (not merged to 10.2 branch yet). Thanks Kim.
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Fix For: 10.3.0.0
>
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1575) document full syntax of CASE
expression
Posted by "Kim Haase (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Kim Haase updated DERBY-1575:
-----------------------------
Attachment: DERBY-1575_fix.diff
rrefcasenullif.html
I'm really sorry I garbled the syntax. I've attached a corrected patch (DERBY-1575_fix.diff) and the HTML output file, rrefcasenullif.html. Hope I got it right this time.
I think you might want to file a separate issue for the functionality question you raise, since it's not really a documentation issue.
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1575) document full syntax of CASE
expression
Posted by "Kim Haase (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Kim Haase reassigned DERBY-1575:
--------------------------------
Assignee: Kim Haase
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1575) document full syntax of CASE
expression
Posted by "Kim Haase (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=comments#action_12431067 ]
Kim Haase commented on DERBY-1575:
----------------------------------
This issue is marked as major, and it looks like a relatively simple fix. I can take it on.
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1575) document full syntax of CASE
expression
Posted by "Christian d'Heureuse (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=comments#action_12431350 ]
Christian d'Heureuse commented on DERBY-1575:
---------------------------------------------
The patch contains the following lines that are not correct:
<codeblock><b>CASE
WHEN <i>booleanExpression</i>
[ WHEN <i>booleanExpression</i> ]...
THEN <i>thenExpression</i>
ELSE <i>elseExpression</i>
END</b></codeblock>
I suggest to change that to:
<codeblock><b>CASE
WHEN <i>booleanExpression</i> THEN <i>thenExpression</i>
[ WHEN <i>booleanExpression</i> THEN <i>thenExpression</i> ]...
ELSE <i>elseExpression</i>
END</b></codeblock>
In the SQL Standard ANSI/ISO/IEC 9075-2-1999, the ELSE clause is optional. So in order to be in conformance with the SQL standard, the syntax should be:
<codeblock><b>CASE
WHEN <i>booleanExpression</i> THEN <i>thenExpression</i>
[ WHEN <i>booleanExpression</i> THEN <i>thenExpression</i> ]...
[ ELSE <i>elseExpression</i> ]
END</b></codeblock>
But with Derby, omitting the ELSE clause seems to work only if the value type is Character.
Examples:
VALUES
CASE
WHEN 1 = 1 THEN 'a'
END
==> OK, returns 'a'
VALUES
CASE
WHEN 1 = 1 THEN 2
END
==> Error: Types 'INTEGER' and 'CHAR' are not type compatible.
I assume that this effect is because Derby uses cast(null as char) as the default value for elseExpression. Maybe this could be changed by using an untyped Null value as the default value for elseExpression?
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Attachments: DERBY-1575.diff
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1575) document full syntax of CASE
expression
Posted by "Jean T. Anderson (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1575?page=all ]
Jean T. Anderson updated DERBY-1575:
------------------------------------
Fix Version/s: 10.2.2.0
> document full syntax of CASE expression
> ---------------------------------------
>
> Key: DERBY-1575
> URL: http://issues.apache.org/jira/browse/DERBY-1575
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Christian d'Heureuse
> Assigned To: Kim Haase
> Fix For: 10.3.0.0, 10.2.2.0
>
> Attachments: DERBY-1575.diff, DERBY-1575_fix.diff, rrefcasenullif.html
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes the CASE expression syntax as:
> CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
> CASE
> WHEN BooleanExpression THEN Expression
> [ WHEN BooleanExpression THEN Expression ]
> ...
> ELSE elseExpression
> END
> Example:
> VALUES
> CASE
> WHEN 1 = 2 THEN 3
> WHEN 4 = 5 THEN 6
> ELSE 7
> END
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira