db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3634) Cannot use row_number() in ORDER BY clause
Date Wed, 07 Oct 2009 10:03:31 GMT

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

Knut Anders Hatlen commented on DERBY-3634:
-------------------------------------------

Hi Dag,
I applied the patch and tried some random queries. Most of them worked as expected, but some
of them caused assert failures:

ij> values row_number() over ();
ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

ij> select * from t where exists (select row_number() over () from t);
ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null.  replaceCallsWithColumnReferences()
has not been called on this WindowFunctionNode.  Make surethe node is under a ResultColumn
as expected.: org.apache.derby.shared.common.sanity.AssertFailure'.

ij> select * from t where row_number() over () > 3;
ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

ij> select row_number() over (), count(*) from t group by row_number() over ();
ERROR XJ001: Java exception: 'ASSERT FAILED generatedRef is null.  replaceCallsWithColumnReferences()
has not been called on this WindowFunctionNode.  Make surethe node is under a ResultColumn
as expected.: org.apache.derby.shared.common.sanity.AssertFailure'.

ij> select count(*) from t group by row_number() over ();
ERROR XJ001: Java exception: 'ASSERT FAILED cannot get interface without operand: org.apache.derby.shared.common.sanity.AssertFailure'.

> 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-newimpl-1.diff, derby-3634-newimpl-1.stat,
derby-3634-newimpl-1.txt, derby-3634-newimpl-2.diff, derby-3634-newimpl-2.stat, derby-3634-remove-2.diff,
derby-3634-remove-2.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.


Mime
View raw message