|
[
Permlink
| « Hide
]
Andrew McIntyre added a comment - 30/Jun/05 06:01 AM
Moving to 10.2 as it appears no one is currently working on this.
Can someone please explain what needs to be documented?
I cannot find the thread that is mentioned in Derby134 and there isn't any explanation of what was changed. Laura Stewart writes:
> Can someone please explain what needs to be documented? > I cannot find the thread that is mentioned in Derby134 and there isn't any explanation of what was changed. I am not sure if it is of much help, but the thread being referred to can be found at: http://thread.gmane.org/gmane.comp.apache.db.derby.devel/1897 I think this is what needs to be documented:
Pre 10.1, the 'ORDER BY' clause of a select statement could only contain column references, by name or number. E.g, SELECT i FROM t ORDER BY i; SELECT i FROM t ORDER BY 1; Now, expressions are also allowed in the ORDER BY clause. E.g., SELECT i, j FROM t ORDER BY i+j; SELECT i, j FROM t ORDER BY f(i); The documentation for the ORDER BY clause (http://db.apache.org/derby/docs/dev/ref/rrefsqlj13658.html) needs to be updated to reflect this change. Unsetting Fix Version for unassigned issues.
Need more information to update the ORDER BY clause topic - http://db.apache.org/derby/docs/dev/ref/
I glanced through the thread that Oystein pointed me to, but did not find what I need. 1. The type of expressions that are allowed. My impression is that they are the Boolean expressions (as opposed to TableExpressions or SelectExpressions)? 2. I need to understand how the Syntax for ORDER BY should appear. Should it just be "Expressions" added immediately after ColumnPosition (in both places in the Syntax? Are there limitations as to the types of expressions allowed? Boolean, concatenation? 3. Description of the expressions allowed (will appear below the syntax) This will depend on what type of expressions are allowed, but one thing that the other parameters mention is if the column needss to be in the select list. 4. Whatever type of expressions are allowed, that topic in the Derby Ref Guide needs to be updated too. Are there any limitations for ORDER BY? For example, the Boolean expressions topic mentions that there are limitations with check constraints. Thanks! Posted a separate request on derby-dev with the questions I needed help with.
Yip Ng responded with: Yip Ng <yipng168@gmail.com> to derby-dev On 2/16/07, Laura Stewart <scotsmatrix@gmail.com> wrote: > 1. The type of expressions that are allowed. My impression is that > they are the Boolean expressions (as opposed to TableExpressions or > SelectExpressions)? ORDER BY expression is not limited to boolean expression only. It can contain common value expression such as numeric, string, datetime, etc. It can also have row value expression such as a scalar subquery, case expression, etc. > 2. I need to understand how the Syntax for ORDER BY should appear. > Should it just be "Expressions" added immediately after ColumnPosition > (in both places in the Syntax? Or should it be specific type of > expression (TableExpression, SelectExpression, Boolean Expression). > Are there limitations as to the types of expressions allowed? Boolean, > concatenation? Perhaps the name sort-key expression would be more appropriate? Adding it after Column Position is fine. > 3. Description of the expressions allowed (will appear below the > syntax) This will depend on what type of expressions are allowed, but > one thing that the other parameters mention is if the column needs to > be in the select list. I need help describing the expressions > parameter. > 4. Whatever type of expressions are allowed, that topic in the Derby > Ref Guide needs to be updated too. Are there any limitations for ORDER > BY? For example, the Boolean expressions topic mentions that there are > limitations with check constraints. > Yes, there are restrictions. If SELECT DISTINCT is specified or the SELECT statement contains a GROUP BY clause, the ORDER BY columns need to appear in the SELECT list. I think there is a bug in Derby in the DISTINCT with ORDER BY case and I have filed a jira for this. See Hope this helps. Regards, Yip Ng Attaching a draft of the patch for reivew.
There are 2 files updated with this patch, the "ORDER BY clause" topic and the "SQL expressions" topic. There is an open question in the examples in the file rrefsqlj13658.html (ORDER BY clause) which I need help with. Hi Laura:
I think the following statement can be separated out from the "expression" description and moved it before the Example section since it applies to column-name as well. "If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list." For the examples, perhaps the following may suffice? You can sort the result set by a numeric expression, for example: SELECT name, salary, bonus FROM employee ORDER BY salary+bonus where salary and bonus are of decimal type. You can sort the result set by invoking a function, for example: SELECT i, len FROM measures ORDER BY sin(i) Attaching a patch and a zip file with the html output.
3 files were updated. Filename Title Improvements rrefsqlj13658 ORDER BY clause Added expressions to syntax and examples rrefsqlj19433 SQL Expressions Added ORDER BY clause, improved index entries, reformatted tables rrefsqlj23075 Boolean expressions Corrected link The diff file for rrefsqlj19433 will be hard to read. I really rearranged the tables in this file to make the info easier to find. Please see the html output. Hi Laura:
I think in the numeric example of the ORDER BY clause ref page, the following statement: "In this example, the salary and bonus columns must contain DECIMAL data types." may be better stated with the following: "In this example, the salary and bonus columns are of DECIMAL data types." Attaching the 3rd version of this patch and html files based on the comments from Yip.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Sub-task
Closed
Minor
