Submitted by: @mrotteveel

With FETCH ABSOLUTE n and FETCH RELATIVE n, it is possible to position the cursor beyond the bounds of the result set. Currently the actual position is remembered, and this has unexpected effects on subsequent use of FETCH RELATIVE n: The offset (n) used must be large enough the position the cursor back within bounds.

For example

execute block
returns (rowval integer, rowcount integer)
as
declare c scroll cursor for (select 1 as rowval from rdb$database union all select 2 as rowval from rdb$database union all select 3 as rowval from rdb$database);
begin
open c;
fetch absolute 5 from c;
-- fetch relative -1 from c;
-- fetch prior from c;
fetch relative -2 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

The FETCH ABSOLUTE 5 positions the cursor two rows beyond the last row. Using FETCH RELATIVE -2 or FETCH PRIOR will return the last row, while FETCH RELATIVE -1 is not sufficient.

For consistent behaviour (i.e. FETCH NEXT and FETCH RELATIVE 1, and FETCH PRIOR and FETCH RELATIVE -1 behaving identical), it would be better that any move out of bounds of the result set, will position the cursor immediately before the first row (position = 0) or immediately after the last row (position = last + 1). This would also match the behaviour specified in ODBC for SQLFetchScroll(), and in JDBC for ResultSet.absolute(int) and ResultSet.relative(int)

Commits: b7b2bed f2fc97a