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

Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 10.5.1.1
    • SQL
    • None

    Description

      SQL 2008 has added new syntax to support a direct way to limit the
      returned set of rows in a result set. This allows an application to
      retrieve only some rows of an otherwise larger result set, similar to
      the popular LIMIT clauses use in some databases.

      Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER()
      function in a nested subquery to achieve the effect of the <fetch
      first clause>, cf. DERBY-2998, a method which is rather more indirect
      and still not efficient (DERBY-3505), and primarily intended for OLAP
      functionality, perhaps.

      There has been no direct way to achieve the effect of the <result
      offset clause> via SQL.

      Syntax (cf. SQL 2008, section 7.13):
      <result offset clause> ::= OFFSET <n>

      {ROW | ROWS}
      <fetch first clause> ::= FETCH {FIRST | NEXT} [<n>] {ROW | ROWS}

      ONLY

      where <n> is an integer. The two clauses syntactically follow the ORDER BY
      clause in the grammar.

      Note that both ORDER BY and the new clauses above are allowed also in
      subqueries in the new version of the SQL standard (section 7.13). I
      only propose to include this at the top level in DERBY for now. (ORDER
      BY is presently also not allowed in subqueries in Derby since SQL
      didn't allow for this until SQL 2008 either).

      Attachments

        1. rrefsqljoffsetfetch.html
          4 kB
          Camilla Haase
        2. DERBY-4079-docs-2.diff
          0.6 kB
          Camilla Haase
        3. derby-4079-fixRtStatsTest.stat
          0.1 kB
          Dag H. Wanvik
        4. derby-4079-fixRtStatsTest.diff
          1.0 kB
          Dag H. Wanvik
        5. derby-4079-3.stat
          1 kB
          Dag H. Wanvik
        6. derby-4079-3.diff
          64 kB
          Dag H. Wanvik
        7. derby-4079-docs-2.zip
          6 kB
          Dag H. Wanvik
        8. derby-4079-docs-2.stat
          0.1 kB
          Dag H. Wanvik
        9. derby-4079-docs-2.diff
          6 kB
          Dag H. Wanvik
        10. derby-4079-2.stat
          1 kB
          Dag H. Wanvik
        11. derby-4079-2.diff
          80 kB
          Dag H. Wanvik
        12. derby-4079-docs-1.zip
          6 kB
          Dag H. Wanvik
        13. derby-4079-docs-1.stat
          0.1 kB
          Dag H. Wanvik
        14. derby-4079-docs-1.diff
          6 kB
          Dag H. Wanvik
        15. derby-4079-1.stat
          1 kB
          Dag H. Wanvik
        16. derby-4079-1.diff
          60 kB
          Dag H. Wanvik
        17. ref.zip
          5 kB
          Dag H. Wanvik

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            dagw Dag H. Wanvik
            dagw Dag H. Wanvik
            Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment