[jira] Updated: (DERBY-3634) Cannot use row_number() in ORDER BY clause

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3634?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-3634:
---------------------------------

    Attachment: derby-3634-remove.stat
                derby-3634-remove.diff

Uploading patch derby-3634-remove, which removes the old windowing
code so as to make the new patch able to use legible "window"
identifiers wihtout risk of crashing with old code as well as make
that patch smaller, as suggested by Bryan.

This patch is essentially a "backwards merge" of DERBY-2998 mods, cf.
http://svnbook.red-bean.com/en/1.5/svn-book.html#svn.branchmerge.basicmerging.undo

svn merge -c -634057 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -633251 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -633067 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -633058 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -632992 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -632988 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -632494 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -601311 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -601309 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk
svn merge -c -601021 https://svn.eu.apache.org/repos/asf/db/derby/code/trunk

plus some conflict resolutions and final svn deletes. Running
regressions.

Btw, if the new patch doesn't make the cut, it should be easy to "undo"
this one again, reviving the old functionality :)


> Cannot use row_number() in ORDER BY clause
> ------------------------------------------
>
>                 Key: DERBY-3634
>                 URL: https://issues.apache.org/jira/browse/DERBY-3634
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Rick Hillegas
>            Assignee: Dag H. Wanvik
>         Attachments: derby-3634-a.diff, derby-3634-a.stat, derby-3634-a.txt, derby-3634-b.diff, derby-3634-b.stat, derby-3634-remove.diff, derby-3634-remove.stat
>
>
> The following query works correctly:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by abs(a)
> I expected the following query to also work, but it raised an exception:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by row_number() over ()
> This is the error I saw: "ERROR 42X01: Syntax error: Encountered "over" at line 5, column 23".
> Here are the reasons why I think that this syntax is supposed to be supported:
> According to my reading of the 2003 SQL spec, the ORDER BY clause should be able to sort on any expression in the SELECT list. That includes OLAP expressions. I believe this is so because, according to part 2, section 10.10 (<sort specification>), a <sort key> can be any <value expression> and if you follow the grammar for <value expression>, it can resolve to be a <value expression primary> (see section 6.3), which can in turn resolve to be a <window function>. This reasoning is supported by tracing the hotlinks on the following page which lays out the SQL 2003 BNF: http://savage.net.au/SQL/sql-2003-2.bnf.html This interpretation is further supported by the example of an ORDER BY clause referencing an OLAP expression which is provided on page 23 of the introduction to OLAP written by Fred Zemke, Krishna Kulkarni, Andy Witkowski, and Bob Lyle: www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/OLAP-99-154r2.pdf

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.