db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Micky Li (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3634) Cannot use row_number() in ORDER BY clause
Date Tue, 29 Sep 2009 16:11:16 GMT

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

Micky Li commented on DERBY-3634:

Thank for your help.
I just used the wrong patch derby-3634-remove.diff as you said. After I apply derby-3634-b.diff,
SQL query works well but can't be used for "ORDER BY" query. It show error as later description.

Thank for your help.
When I use browser to open http://svn.apache.org/repos/asf/db/derby/code/trunk/, it shows
819978 revision. But I use the same link in Eclipse SVN Repository, the trunk revision always
shows 819006 from yesterday even I refresh it. I don't know why ..., and I also use TortoiseSVN
(Windows version) to explore that link, it still shows 819006 at trunk and 819547 at branch
in Repository Browser of TortoiseSVN.

After apply the derby-3634-c.diff patch, it compiles and jar OK. The SQL query string as I
mentioned at 28/Sep/09 09:02 PM works well.
My target is to use ROW_NUMBER() with ORDER BY in one SQL query, but there shows a error as
'Syntax error: Encountered "ORDER" at line 1, column 113'

the SQL query string is
SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE event_log.id<=2147483647
ORDER BY id DESC) AS TR WHERE rownum >= 2189 AND rownum <= 2288

It clearly that the syntax error is caused by the ORDER which is added. But I think the derby-3634-c.diff
patch is to solve this problem, am I right?

Is there something I missed?

> 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,
derby-3634-b.stat, derby-3634-c.diff, derby-3634-c.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
> 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