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 "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2010/01/19 15:03:54 UTC
[jira] Created: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Document ORDER BY and OFFSET/FETCH in subqueries
------------------------------------------------
Key: DERBY-4518
URL: https://issues.apache.org/jira/browse/DERBY-4518
Project: Derby
Issue Type: Improvement
Components: Documentation
Reporter: Dag H. Wanvik
DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Tiago R. Espinha (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12863412#action_12863412 ]
Tiago R. Espinha commented on DERBY-4518:
-----------------------------------------
Sorry to be revisiting this issue but I was doing buddy-testing for this feature and I was a bit stumped with the documentation. I couldn't find an example under "The result offset and fetch first clauses" that demonstrates the use of OFFSET within sub-queries. There is an example under "Duplicates in UNION, INTERSECT, and EXCEPT ALL results" but at least to me, that wouldn't be the first logical place I'd visit to find examples of OFFSET usage on sub-queries.
In my opinion this is something small and not worth a respin but maybe we can refine it for further releases?
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Fix For: 10.6.1.0
>
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4518:
-----------------------------
Attachment: DERBY-4518-3.zip
DERBY-4518-3.diff
Thanks, Dag! I've incorporated that change into DERBY-4518-3.diff and DERBY-4518-3.zip. I will commit this patch.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4518:
-----------------------------
Issue & fix info: [Patch Available]
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803537#action_12803537 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
The last statement's syntax is wrong: it should be:
values (3,21),(1,22),(2,23) order by 1 offset 2 rows;
Thats is, the "rows" keyword is needed.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12804682#action_12804682 ]
Kim Haase commented on DERBY-4518:
----------------------------------
Thanks, Dag! I am incorporating your suggestions.
I do wonder if I should say more in rrefselectexpression.html. I can add descriptions of each of the added clauses, but I am not sure where to put them in the list that follows "Derby processes a SelectExpression in the following order:".
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase reassigned DERBY-4518:
--------------------------------
Assignee: Kim Haase
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase resolved DERBY-4518.
------------------------------
Resolution: Fixed
Fix Version/s: 10.6.0.0
Issue & fix info: (was: [Patch Available])
Committed patch DERBY-4518-3.diff to documentation trunk at revision 904155.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Fix For: 10.6.0.0
>
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803487#action_12803487 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
Thanks for picking up this one, Kim! Answers to your questions:
I think the SELECT statement section is OK as it stands. As you say,
the requirements for an updatable cursor do not change.
SelectExpression topic: Yes, I suggest you put it there too for good
measure, and in the VALUES expression topic as well. The documentation
structure here is not good in that it deviates significantly from the
language, probably to make explanations "more intuitive", so it's hard
to make this non-redundant and also correct..
"Would it be okay to combine these into "a single-row or multiple-row
values expression"? I think so, yes.
"The Documentation section doesn't mention CREATE VIEW, but the "Derby
syntax changes" section does", so I'm modifying that topic too."
That's an omission, thanks for catching that.
ORDER BY clause topic: I suggest this wording (or some such, feel
free to improve!):
"The ORDER BY clause is an optional element of a SELECT statement (link), the
SELECT expression (link), the VALUES expression (link) and in table and scalar subqueries (links). It can
also be used with an INSERT statement (link) and the CREATE VIEW statement (link).
An ORDER BY clause allows you to specify the order in which rows
appear in the result set. In subqueries, the ORDER BY clause is
meaningless unless accompanied by one or both of the result offset and
fetch first clauses (link) or in conjunction with ROW_NUMBER (link)
since there is no guarantee the order is retained in the outer result
set. It is allowed to combine ORDER BY on the outer query with ORDER
by in subqueries."
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12802266#action_12802266 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
The specification attachment to DERBY-4397 contains updated syntax descriptions for the Derby documentation
and notes on where the docs need updating.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4518:
-----------------------------
Attachment: DERBY-4518.zip
DERBY-4518.stat
DERBY-4518.diff
Thanks for straightening me out on the offset syntax, Dag.
Attaching DERBY-4518.diff, DERBY-4518.stat, and DERBY-4518.zip, a preliminary patch, with changes to the following 11 topics:
Reference Manual:
CREATE VIEW statement: rrefsqlj15446.dita
INSERT statement: rrefsqlj40774.dita
ORDER BY clause: rrefsqlj13658.dita
SelectExpression: rrefselectexpression.dita
VALUES expression: rrefsqlj11277.dita
Query: rrefsqlj21571.dita
ScalarSubquery: rrefscalarsubquery.dita
TableSubquery: rreftablesubquery.dita
I took advantage of the opportunity for some formatting cleanup in some of the topics. I added examples for some. I believe some topics need more information, particularly SelectExpression, since it describes what all the clauses do. I'd like to know if what I've done so far is on the right track, though. Thanks!
Tuning Guide:
View flattening: ctuntransform22576.dita
Flattening a subquery into a normal join: ctuntransform36368.dita
Flattening a subquery into an EXISTS join: ctuntransform25868.dita
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kim Haase updated DERBY-4518:
-----------------------------
Attachment: DERBY-4518-2.zip
DERBY-4518-2.diff
Thanks, Dag! I'm attaching DERBY-4518-2.diff and DERBY-4518-2.zip. I hope they address all your comments -- please let me know if further changes are needed.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803515#action_12803515 ]
Kim Haase commented on DERBY-4518:
----------------------------------
BTW, I am using a version of derbyrun.jar that I built today from the code trunk.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Tiago R. Espinha (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12866719#action_12866719 ]
Tiago R. Espinha commented on DERBY-4518:
-----------------------------------------
I seem to have run into another problem with my buddy testing. On DERBY-4398 the following is mentioned:
"F860 dynamic <fetch first row count> in <fetch first clause> (already present in 10.5) "
How does this dynamic fetching work? I've tried:
select * from t1 order by f1 desc fetch first count(*) only;
and
select f1, count(*) as cnt from t1 fetch first cnt only;
But I always got an error. Am I misunderstanding that feature?
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Fix For: 10.6.1.0
>
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12867292#action_12867292 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
Yes, dynamic here means "?" markers in prepared statements in stead of integer literals, e.g. "offset ? rows fetch first ? rows only".
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Fix For: 10.6.1.0
>
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12804613#action_12804613 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
One more thing:
In this example:
-- Create an AIRPORTS table and insert into it
-- some of the fields from the CITIES table, with the airport
-- codes sorted alphabetically
CREATE TABLE AIRPORTS (
AIRPORT VARCHAR(3),
CITY VARCHAR(24) NOT NULL,
COUNTRY VARCHAR(26) NOT NULL
);
INSERT INTO AIRPORTS
SELECT AIRPORT, CITY_NAME, COUNTRY FROM CITIES
ORDER BY AIRPORT;
it would probably be prudent to include an auto-generated key (cf. DERBY-4), since having rows physically sorted doesn't really help much after the insert unless it's somehow related to some other property that Derby knows about: one would still need to specify ordering when selecting to be assured of the correct order, at least for portability. I think it some verbiage on this is in order as well, e.g. "When you want insertion to happen with a specific ordering, e.g. in conjunction with auto-generated keys, it can be useful to specify an ORDER BY on the result set to be inserted."
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803430#action_12803430 ]
Kim Haase commented on DERBY-4518:
----------------------------------
The spec is very clear! I do have a couple of questions about the Documentation section.
The "SELECT statement" topic already includes the ORDER BY clause. Are other changes needed? I would guess that the "Requirements for updatable cursors and updatable ResultSets" section would not change (that is, ORDER BY is still not allowed).
What changes are needed for the "SelectExpression" topic? Can the FROM clause now be followed by ORDER BY, result offset, and fetch first clauses?
The "INSERT statement" topic refers to both a "VALUES list" and a "multiple-row VALUES expression". I think that after some discussion a few years ago it was agreed to use the term "VALUES expression" generally rather than "VALUES clause" or "VALUES list". Would it be okay to combine these into "a single-row or multiple-row values expression"?
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-4518) Document ORDER BY and OFFSET/FETCH in
subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik reopened DERBY-4518:
----------------------------------
Assignee: (was: Kim Haase)
I agree this can be improved, Tiago! Thanks for spotting this.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Fix For: 10.6.1.0
>
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518-3.diff, DERBY-4518-3.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803514#action_12803514 ]
Kim Haase commented on DERBY-4518:
----------------------------------
Thanks very much, Dag, I'm starting on these.
After some experimenting I am finding that I get a syntax error when I use the offset clause in a VALUES expression, although the order by and fetch first clauses work fine. Is this expected?
ij> values (3,21),(1,22),(2,23);
1 |2
-----------------------
3 |21
1 |22
2 |23
3 rows selected
ij> values (3,21),(1,22),(2,23) order by 1;
1 |2
-----------------------
1 |22
2 |23
3 |21
3 rows selected
ij> values (3,21),(1,22),(2,23) order by 2;
1 |2
-----------------------
3 |21
1 |22
2 |23
3 rows selected
ij> values (3,21),(1,22),(2,23) fetch first 2 rows only;
1 |2
-----------------------
3 |21
1 |22
2 rows selected
ij> values (3,21),(1,22),(2,23) order by 1 fetch first 2 rows only;
1 |2
-----------------------
1 |22
2 |23
2 rows selected
ij> values (3,21),(1,22),(2,23) order by 1 offset 2;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 47.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12804610#action_12804610 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
Thanks for the patch, Kim! It all looks good to me.
I have one comment, which was not introducd by this patch but it relevant all the same:
rrefsqlj11277.html
"A VALUES expression that is used in an INSERT statement cannot use an
ORDER BY, result offset, or fetch first clause."
While correct, this statement isn't very helpful, I'd like it to say
something like this:
"A VALUES expression that is used in an INSERT statement cannot use an
ORDER BY, result offset, or fetch first clause."
"However, if the VALUES expression does not contain the DEFAULT keyword the
VALUES clause can be put in a subquery and ordered, e.g. like this:
INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;
"
Ditto i rrefsqlj40774.html (insert statement)
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805906#action_12805906 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
Thanks so much for the quick action of this and the latest rev of this patch, Kim! It looks great now, just one small thing:
In the INSERT topic, I think I'd want the sentence starting "When you want insertion to happen with a specific ordering.." moved up before the paragraph starting "If the Query is a VALUES expression..",
since the former is really a use case/rationale for using ORDER BY in the first place with INSERT, whereas the other paragraph describes a corner case of its usage.
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518-2.diff, DERBY-4518-2.zip, DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803459#action_12803459 ]
Kim Haase commented on DERBY-4518:
----------------------------------
A couple of other items --
The Documentation section doesn't mention CREATE VIEW, but the "Derby syntax changes" section does, so I'm modifying that topic too. Hope that's okay.
For the "ORDER BY clause" topic, I am guessing that the definition at the beginning needs some expansion? Currently it says, "The ORDER BY clause is an optional element of a SELECT statement." Would it be correct to say the following?
"The ORDER BY clause is an optional element of a SELECT statement or a query expression."
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4518) Document ORDER BY and OFFSET/FETCH
in subqueries
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805068#action_12805068 ]
Dag H. Wanvik commented on DERBY-4518:
--------------------------------------
The right order would be:
Derby processes a SelectExpression in the following order:
* FROM clause
* WHERE clause
* GROUP BY (or implicit GROUP BY)
* HAVING clause
* ORDER BY clause
* result offset clause
* fetch first clause
* SELECT clause
> Document ORDER BY and OFFSET/FETCH in subqueries
> ------------------------------------------------
>
> Key: DERBY-4518
> URL: https://issues.apache.org/jira/browse/DERBY-4518
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Reporter: Dag H. Wanvik
> Assignee: Kim Haase
> Attachments: DERBY-4518.diff, DERBY-4518.stat, DERBY-4518.zip
>
>
> DERBY-4397 and DERBY-4398 extends the usability of ORDER BY and OFFSET/FETCH to subqueries and view. This should be documented.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.