[DERBY-4398] Allow OFFSET/FETCH in subqueries

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.6.1.0
• Component/s: SQL
• Urgency: Normal
• Assignee: Dag H. Wanvik
• Reporter: Dag H. Wanvik
• Created: 06/Oct/09 00:05
• Updated: 10/May/10 23:43
• Resolved: 28/Jan/10 15:58

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)

Issue Links

• is related to: DERBY-4397 Allow ORDER BY in subqueries
• relates to: DERBY-4649 Make optimizer utilize any useful information in OFFSET/FETCH NEXT in subqueries
• relates to: DERBY-4518 Document ORDER BY and OFFSET/FETCH in subqueries