New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
FETCH ABSOLUTE and RELATIVE beyond bounds of cursor should always position immediately before-first or after-last [CORE6487] #6717
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov It should be fixed now in FB4, please test the next snapshot build. I will backport to v3 after your confirmation. |
Modified by: @dyemanovFix Version: 4.0.0 [ 10931 ] |
Commented by: @mrotteveel I'm reopening the issue as it also needs to be backported. |
Commented by: @mrotteveel @dmitry I can confirm it works as expected in 4.0.0.2369. |
Modified by: @dyemanovstatus: Reopened [ 4 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0.8 [ 10960 ] |
Commented by: @pavel-zotov > I'm reopening the issue as it also needs to be backported. Mark, I've checked intermediate build 3.0.8.33416 with backport (it can be found in git artifacts: https://github.com/FirebirdSQL/firebird/actions/runs/575401318#artifacts ). |
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
The text was updated successfully, but these errors were encountered: