Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3634

Cannot use row_number() in ORDER BY clause

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix

      Description

      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

        Attachments

        1. derby-3634-newimpl-4.stat
          3 kB
          Dag H. Wanvik
        2. derby-3634-newimpl-4.diff
          123 kB
          Dag H. Wanvik
        3. derby-3634-newimpl-3.stat
          3 kB
          Dag H. Wanvik
        4. derby-3634-newimpl-3.diff
          124 kB
          Dag H. Wanvik
        5. derby-3634-newimpl-2.stat
          2 kB
          Dag H. Wanvik
        6. derby-3634-newimpl-2.diff
          113 kB
          Dag H. Wanvik
        7. derby-3634-newimpl-1.txt
          38 kB
          Dag H. Wanvik
        8. derby-3634-newimpl-1.stat
          2 kB
          Dag H. Wanvik
        9. derby-3634-newimpl-1.diff
          110 kB
          Dag H. Wanvik
        10. derby-3634-remove-2.stat
          2 kB
          Dag H. Wanvik
        11. derby-3634-remove-2.diff
          59 kB
          Dag H. Wanvik
        12. derby-3634-c.stat
          2 kB
          Dag H. Wanvik
        13. derby-3634-c.diff
          102 kB
          Dag H. Wanvik
        14. derby-3634-remove.stat
          2 kB
          Dag H. Wanvik
        15. derby-3634-remove.diff
          74 kB
          Dag H. Wanvik
        16. derby-3634-b.stat
          3 kB
          Dag H. Wanvik
        17. derby-3634-b.diff
          113 kB
          Dag H. Wanvik
        18. derby-3634-a.txt
          37 kB
          Dag H. Wanvik
        19. derby-3634-a.stat
          3 kB
          Dag H. Wanvik
        20. derby-3634-a.diff
          100 kB
          Dag H. Wanvik

          Issue Links

            Activity

              People

              • Assignee:
                dagw Dag H. Wanvik
                Reporter:
                rhillegas Rick Hillegas
              • Votes:
                1 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: