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

          Dag H. Wanvik created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Description 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.


          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.


          Derby Categories [Wrong query result, Deviation from standard] [Deviation from standard, Wrong query result]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Mike Matrigali made changes -
          Component/s SQL [ 11408 ]
          Knut Anders Hatlen made changes -
          Urgency Normal
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-3634 [ DERBY-3634 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          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.


          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.


          Dag H. Wanvik made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          Issue & fix info [Patch Available]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Gavin made changes -
          Workflow jira [ 12453250 ] Default workflow, editable Closed status [ 12800934 ]

            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