Derby
  1. Derby
  2. DERBY-3634

Cannot use row_number() in ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major 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

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

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12429369 ] Default workflow, editable Closed status [ 12801622 ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Dag H. Wanvik made changes -
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Issue & fix info [Patch Available, High Value Fix] [High Value Fix]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-4.diff [ 12422243 ]
          Attachment derby-3634-newimpl-4.stat [ 12422244 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-3.stat [ 12421699 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-3.diff [ 12421698 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-3.diff [ 12421744 ]
          Attachment derby-3634-newimpl-3.stat [ 12421745 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-3.diff [ 12421698 ]
          Attachment derby-3634-newimpl-3.stat [ 12421699 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-2.diff [ 12421301 ]
          Attachment derby-3634-newimpl-2.stat [ 12421302 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-newimpl-1.stat [ 12421177 ]
          Attachment derby-3634-newimpl-1.txt [ 12421178 ]
          Attachment derby-3634-newimpl-1.diff [ 12421176 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-remove-2.diff [ 12420958 ]
          Attachment derby-3634-remove-2.stat [ 12420959 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-c.diff [ 12420793 ]
          Attachment derby-3634-c.stat [ 12420794 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-remove.diff [ 12418564 ]
          Attachment derby-3634-remove.stat [ 12418565 ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-2998 [ DERBY-2998 ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-4069 [ DERBY-4069 ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-3635 [ DERBY-3635 ]
          Dag H. Wanvik made changes -
          Attachment derby-3634-b.diff [ 12417196 ]
          Attachment derby-3634-b.stat [ 12417197 ]
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix] [High Value Fix, Patch Available]
          Dag H. Wanvik made changes -
          Attachment derby-3634-a.diff [ 12416941 ]
          Attachment derby-3634-a.stat [ 12416942 ]
          Attachment derby-3634-a.txt [ 12416943 ]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Knut Anders Hatlen made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Urgency Normal
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Kathey Marsden made changes -
          Derby Categories [High Value Fix]
          Knut Anders Hatlen made changes -
          Summary Cannot use row_number() in ORDERY BY clause Cannot use row_number() in ORDER BY clause
          Thomas Nielsen made changes -
          Field Original Value New Value
          Component/s SQL [ 11408 ]
          Rick Hillegas created issue -

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development