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

Allow ORDER BY and FETCH/OFFSET in set operands


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


      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



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


              • Created: