Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal

      Description

      SQL 2008 specifies that OFFSET/FETCH (<result offset clause>, <fetch
      first clause>) may be used in subqueries. Currently, Derby only
      allows this at the outer (cursor) level. Cf. also DERBY-4397 on
      allowing ORDER BY in subqueries for this extension to be portable and
      useful.

      SQL features:

      F856 Nested <fetch first clause> in <query expression>
      F857 Top-level <fetch first clause> in <query expression> (already present in 10.5)
      F858 <fetch first clause> in subqueries
      F859 Top-level <fetch first clause> in views
      F860 dynamic <fetch first row count> in <fetch first clause> (already present in 10.5)

      F861 Top-level <result offset clause> in <query expression> (already present in 10.5)
      F862 <result offset clause> in subqueries
      F863 Nested <result offset clause> in <query expression>
      F864 Top-level <result offset clause> in views
      F865 dynamic <offset row count> in <result offset clause> (already present in 10.5)

      1. derby-4398-3.stat
        2 kB
        Dag H. Wanvik
      2. derby-4398-3.diff
        89 kB
        Dag H. Wanvik
      3. derby-4398-2.stat
        2 kB
        Dag H. Wanvik
      4. derby-4398-2.diff
        86 kB
        Dag H. Wanvik
      5. derby-4398-insert.stat
        1 kB
        Dag H. Wanvik
      6. derby-4398-insert.diff
        67 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          Uploading a first patch (derby-4398-insert) which enables offset/fetch
          first in INSERT subquery context. This modifies the previous
          implementation for offset fetch which only had to consider a top level
          cursor context. Instead of wrapping the row count result set node from
          CursorNode#optimizeStatement, the offset/fetch info is now pushed to
          the underlying result set, similar to what is done for order by.

          Added test cases to the now renamed test OrderByAndOffsetFetchInSubqueries.
          The patch is not yet for commit, needs some cleanups still.

          Running regressions.

          Show
          Dag H. Wanvik added a comment - Uploading a first patch (derby-4398-insert) which enables offset/fetch first in INSERT subquery context. This modifies the previous implementation for offset fetch which only had to consider a top level cursor context. Instead of wrapping the row count result set node from CursorNode#optimizeStatement, the offset/fetch info is now pushed to the underlying result set, similar to what is done for order by. Added test cases to the now renamed test OrderByAndOffsetFetchInSubqueries. The patch is not yet for commit, needs some cleanups still. Running regressions.
          Hide
          Dag H. Wanvik added a comment -

          Regressions passed.

          Show
          Dag H. Wanvik added a comment - Regressions passed.
          Hide
          Dag H. Wanvik added a comment -

          Uploading version 2 of this patch; which extends the support for offset/fetch to
          all subqueries, and adds test cases.

          Missing is still informing the optimizer with hints on cardinality if offset/fetch is used.
          I also need to investigate more if sort avoidance still works in presence of offset/fetch.
          Flattening is prohibited in the presence of offset/fetch, similarly to what is done for order by in subqueries.

          Running regressions.

          Show
          Dag H. Wanvik added a comment - Uploading version 2 of this patch; which extends the support for offset/fetch to all subqueries, and adds test cases. Missing is still informing the optimizer with hints on cardinality if offset/fetch is used. I also need to investigate more if sort avoidance still works in presence of offset/fetch. Flattening is prohibited in the presence of offset/fetch, similarly to what is done for order by in subqueries. Running regressions.
          Hide
          Dag H. Wanvik added a comment -

          Regressions passed.

          Show
          Dag H. Wanvik added a comment - Regressions passed.
          Hide
          Dag H. Wanvik added a comment -

          Uploading derby-4398-3, similar to rev 2, but cleaned up a little, including moving bindOffsetFetch to QueryTreeNode from CursorNode. Will commit this version if regression spass.

          Show
          Dag H. Wanvik added a comment - Uploading derby-4398-3, similar to rev 2, but cleaned up a little, including moving bindOffsetFetch to QueryTreeNode from CursorNode. Will commit this version if regression spass.
          Hide
          Dag H. Wanvik added a comment -

          Committed rev 3 as svn 897934, and a followup fix for a javadoc error svn 897937.

          Show
          Dag H. Wanvik added a comment - Committed rev 3 as svn 897934, and a followup fix for a javadoc error svn 897937.
          Hide
          Dag H. Wanvik added a comment - - edited

          Resolving this issue. Optimizer tie-in will be handled in a separate issue if/when we get to that.

          Show
          Dag H. Wanvik added a comment - - edited Resolving this issue. Optimizer tie-in will be handled in a separate issue if/when we get to that.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development