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, 23 Feb 2009 23:56:01 GMT

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

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

         Description: 
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.




  was:
The regression tes 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.




    Derby Categories: [Deviation from standard, Wrong query result]  (was: [Wrong query result,
Deviation from standard])

> 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
>    Affects Versions: 10.4.1.3, 10.4.2.0
>            Reporter: 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