db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3634) Cannot use row_number() in ORDER BY clause
Date Thu, 27 Aug 2009 23:28:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12748616#action_12748616

Bryan Pendleton commented on DERBY-3634:

I read through your writeup in more detail -- it looks great!

Am I correct in understanding that the "window function node" classes would be the place
where we could add additional window functions in the future, such as RANK, NTILE, or
maybe moving averages and things like that?

Regarding leaving the complete syntax present in the parser, but with parts commented out,
what is the tradeoff between doing that, versus having the unsupported syntax variants be
present and recognized by the parser, but throw some sort of "sorry, not yet implemented"

Regarding keeping the patch simpler for review, perhaps you could post two patches:
1) the first patch would delete the old code that we aren't going to keep around any more
2) the second patch would be your proposed new code, with all the function names, etc.
as you actually intend them to be.

That way, patch #2 would still be a manageable size (wouldn't include large diffs for removal
of old code) but it would also be possible to apply both patches in sequence and see the
proposed codeline as you intend it to really look ("window" rather than 'wdw", etc.)

Anyway, now that I've read through your design notes, I'll try to find some time to read
through the actual code soon.

> 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:
>            Reporter: Rick Hillegas
>            Assignee: Dag H. Wanvik
>         Attachments: derby-3634-a.diff, derby-3634-a.stat, derby-3634-a.txt, derby-3634-b.diff,
> 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
> 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.

View raw message