Derby
  1. Derby
  2. DERBY-4069

Wrong behavior when ROW_NUMBER is combined with ORDER BY

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3, 10.4.2.0
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Deviation from standard, Wrong query result

      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 has been 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.

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          DERBY-3634 has been resolved with a patch that fixes this issues as well, so closing.

          Show
          Dag H. Wanvik added a comment - DERBY-3634 has been resolved with a patch that fixes this issues as well, so closing.
          Hide
          Dag H. Wanvik added a comment -

          There is a patch under DERBY-3634 that fixes this issue as well; marking patch available.

          Show
          Dag H. Wanvik added a comment - There is a patch under DERBY-3634 that fixes this issue as well; marking patch available.
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development