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

          Dag H. Wanvik created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Link This issue is related to DERBY-4397 [ DERBY-4397 ]
          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.
          Dag H. Wanvik made changes -
          Attachment derby-4398-insert.diff [ 12428521 ]
          Attachment derby-4398-insert.stat [ 12428522 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Hide
          Dag H. Wanvik added a comment -

          Regressions passed.

          Show
          Dag H. Wanvik added a comment - Regressions passed.
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Dag H. Wanvik made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          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.
          Dag H. Wanvik made changes -
          Attachment derby-4398-2.diff [ 12429635 ]
          Attachment derby-4398-2.stat [ 12429636 ]
          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.
          Dag H. Wanvik made changes -
          Attachment derby-4398-3.diff [ 12429882 ]
          Attachment derby-4398-3.stat [ 12429883 ]
          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.
          Dag H. Wanvik made changes -
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          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 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)
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-4518 [ DERBY-4518 ]
          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.
          Dag H. Wanvik made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Dag H. Wanvik made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-4649 [ DERBY-4649 ]
          Gavin made changes -
          Workflow jira [ 12478688 ] Default workflow, editable Closed status [ 12799627 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          74d 22h 22m 1 Dag H. Wanvik 19/Dec/09 22:28
          In Progress In Progress Resolved Resolved
          39d 17h 30m 1 Dag H. Wanvik 28/Jan/10 15:58
          Resolved Resolved Closed Closed
          102d 7h 40m 1 Dag H. Wanvik 11/May/10 00:39

            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