Skip to content
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

Closed
firebird-issue-importer opened this issue Feb 15, 2021 · 10 comments

Comments

@firebird-issue-importer

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 16, 2021

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 16, 2021

Commented by: @dyemanov

It should be fixed now in FB4, please test the next snapshot build. I will backport to v3 after your confirmation.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 16, 2021

Modified by: @dyemanov

Fix Version: 4.0.0 [ 10931 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @pavel-zotov

status: Open [ 1 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Commented by: @mrotteveel

I'm reopening the issue as it also needs to be backported.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @mrotteveel

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Commented by: @mrotteveel

@dmitry I can confirm it works as expected in 4.0.0.2369.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.8 [ 10960 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

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 ).
It works OK, so this ticket can be closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants