db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4069) Wrong behavior when ROW_NUMBER is combined with ORDER BY
Date Mon, 24 Aug 2009 16:14:59 GMT

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

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

    Issue & fix info: [Patch Available]

> Wrong behavior when ROW_NUMBER is combined with ORDER BY
> --------------------------------------------------------
>
>                 Key: DERBY-4069
>                 URL: https://issues.apache.org/jira/browse/DERBY-4069
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3, 10.4.2.0
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>
> The regression test OLAPTest.java contains this query:
>    create table t1 (a int, b int)
>    insert into t1 values (10,100),(20,200),(30,300),(40,400),(50,500)
>    select row_number() over () as r, t1.* from t1 order by b desc
> and the result is asserted to be
>    expectedRows = new String[][]{{"1", "50", "500"}, 
>                                  {"2", "40", "400"}, 
>                                  {"3", "30", "300"},
>                                  {"4", "20", "200"}, 
>                                  {"5", "10", "100"}};
> The test succeeds, but I believe the canon is wrong here.
> ORDER BY should be applied at the cursor level, that is _after_ a
> windowing clause in the select expression sbeen applied, so we would
> expect to see:
>    {"5", "50", "500"}, {"4", "40", "400"} ...
> Note: It should be added that since the window does not contain any
> <window order clause>, cf. SQL:2003 section 7.11, the actual ordering
> of the rows in the window is implementation dependent.  In Derby,
> without the query's ORDER BY, the rows are ordered as in the INSERT
> statement above, so I think this reflects a bug in the
> implementation.

-- 
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