[
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:
---------------------------------
@Knut
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.
@Dag
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 following:
'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: 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-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 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.
