1. Derby
  2. DERBY-6008

Allow ORDER BY and FETCH/OFFSET in set operands


    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major 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-e.stat
        0.8 kB
        Dag H. Wanvik
      2. derby-6008-e.diff
        46 kB
        Dag H. Wanvik
      3. derby-6008-d.stat
        0.8 kB
        Dag H. Wanvik
      4. derby-6008-d.diff
        46 kB
        Dag H. Wanvik
      5. derby-6008-c.stat
        0.6 kB
        Dag H. Wanvik
      6. derby-6008-c.diff
        15 kB
        Dag H. Wanvik
      7. derby-6008-b.stat
        0.4 kB
        Dag H. Wanvik
      8. derby-6008-b.diff
        9 kB
        Dag H. Wanvik
      9. derby-6008-a.diff
        8 kB
        Dag H. Wanvik
      10. derby-6008-a.stat
        0.4 kB
        Dag H. Wanvik
      There are no Sub-Tasks for this issue.


        No work has yet been logged on this issue.


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


            • Created: