Derby
  1. Derby
  2. DERBY-6008

Allow ORDER BY and FETCH/OFFSET in set operands

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.10.1.1
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Deviation from standard

      Description

      Currently, Derby doesn't allow ORDER BY nested in a set operand, e.g. in the following construct:

      (select i from t1 order by j offset 1 row) union
      (select i from t2 order by j desc offset 2 rows)

      This is allowed by the standard, as far as I can understand, cf. this quote from section 7.12 in SQL 2011:

      <query expression body> ::=
      <query term>

      <query expression body> UNION [ ALL DISTINCT ]
      [ <corresponding spec> ] <query term>
      <query expression body> EXCEPT [ ALL DISTINCT ]
      [ <corresponding spec> ] <query term>

      <query term> ::=
      <query primary>

      <query term> INTERSECT [ ALL DISTINCT ]
      [ <corresponding spec> ] <query primary>

      <query primary> ::=
      <simple table>

      <left paren> <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>

      I.e. the left paren chooses the second alternative in the production for <query primary>.

      1. derby-6008-a.diff
        8 kB
        Dag H. Wanvik
      2. derby-6008-a.stat
        0.4 kB
        Dag H. Wanvik
      3. derby-6008-b.diff
        9 kB
        Dag H. Wanvik
      4. derby-6008-b.stat
        0.4 kB
        Dag H. Wanvik
      5. derby-6008-c.diff
        15 kB
        Dag H. Wanvik
      6. derby-6008-c.stat
        0.6 kB
        Dag H. Wanvik
      7. derby-6008-d.diff
        46 kB
        Dag H. Wanvik
      8. derby-6008-d.stat
        0.8 kB
        Dag H. Wanvik
      9. derby-6008-e.diff
        46 kB
        Dag H. Wanvik
      10. derby-6008-e.stat
        0.8 kB
        Dag H. Wanvik

        Activity

        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12737078 ] Default workflow, editable Closed status [ 12802176 ]
        Dag H. Wanvik made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Issue & fix info Patch Available [ 10102 ]
        Bug behavior facts Deviation from standard [ 10367 ]
        Fix Version/s 10.10.0.0 [ 12321550 ]
        Resolution Fixed [ 1 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-e.diff [ 12561068 ]
        Attachment derby-6008-e.stat [ 12561069 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-d.diff [ 12560193 ]
        Attachment derby-6008-d.stat [ 12560194 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-c.diff [ 12556491 ]
        Attachment derby-6008-c.stat [ 12556492 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-b.diff [ 12556267 ]
        Attachment derby-6008-b.stat [ 12556268 ]
        Dag H. Wanvik made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Dag H. Wanvik made changes -
        Assignee Dag H. Wanvik [ dagw ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-a.diff [ 12556190 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-a.diff [ 12556186 ]
        Dag H. Wanvik made changes -
        Issue & fix info Patch Available [ 10102 ]
        Dag H. Wanvik made changes -
        Attachment derby-6008-a.diff [ 12556186 ]
        Attachment derby-6008-a.stat [ 12556187 ]
        Dag H. Wanvik made changes -
        Field Original Value New Value
        Summary Allow ORDER BY and FETCH/OFFSET in set operators Allow ORDER BY and FETCH/OFFSET in set operands
        Dag H. Wanvik created issue -

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development