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 RELATIVE has an off by one error for the first row [CORE6486] #6716

Closed
firebird-issue-importer opened this issue Feb 14, 2021 · 8 comments

Comments

@firebird-issue-importer

Submitted by: @mrotteveel

The FETCH RELATIVE 1 FROM cursor_name statement should be equivalent to FETCH NEXT FROM cursor_name (or FETCH cursor_name), however it skips the first row;

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 next from c;
fetch relative 1 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

This returns (2, 1) for (rowval, rowcount), instead of expected (1, 1). For subsequent fetches, it behaves as expected (fetching the next record), so it seems the problem is with the FETCH RELATIVE only when the cursor is newly opened. Using FETCH RELATIVE 2 FROM c will return (3,1) instead of (2,1).

It seems to behave as if the first row was already fetched, but using FETCH RELATIVE 0 FROM c will produce error "Cursor C is not positioned in a valid record".

Commits: b7b2bed f2fc97a

====== Test Details ======

Currently works OK only for 4.0.0.2369. Intermediate build 3.0.8.33416 with stamp 17-feb-2021 16:35 still issues wrong result.
Sent letter to dimitr, 17.02.2021 20:46.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 2021

Modified by: @mrotteveel

description: The FETCH RELATIVE 1 FROM cursor_name statement should be equivalent to FETCH NEXT FROM cursor_name (or FETCH cursor_name), however it skips the first row;

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 next from c;
fetch relative 1 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

This returns (2, 1) for (rowval, rowcount), instead of expected (1, 1). For subsequent fetches, it behaves as expected, so it seems to problem is with the first FETCH RELATIVE only. Using FETCH RELATIVE 2 FROM c will return (3,1) instead of (2,1).

It seems to behave as if the first row was already fetched, but using FETCH RELATIVE 0 FROM c will produce error "Cursor C is not positioned in a valid record".

=>

The FETCH RELATIVE 1 FROM cursor_name statement should be equivalent to FETCH NEXT FROM cursor_name (or FETCH cursor_name), however it skips the first row;

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 next from c;
fetch relative 1 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

This returns (2, 1) for (rowval, rowcount), instead of expected (1, 1). For subsequent fetches, it behaves as expected, so it seems the problem is with the first FETCH RELATIVE only. Using FETCH RELATIVE 2 FROM c will return (3,1) instead of (2,1).

It seems to behave as if the first row was already fetched, but using FETCH RELATIVE 0 FROM c will produce error "Cursor C is not positioned in a valid record".

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 2021

Modified by: @mrotteveel

description: The FETCH RELATIVE 1 FROM cursor_name statement should be equivalent to FETCH NEXT FROM cursor_name (or FETCH cursor_name), however it skips the first row;

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 next from c;
fetch relative 1 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

This returns (2, 1) for (rowval, rowcount), instead of expected (1, 1). For subsequent fetches, it behaves as expected, so it seems the problem is with the first FETCH RELATIVE only. Using FETCH RELATIVE 2 FROM c will return (3,1) instead of (2,1).

It seems to behave as if the first row was already fetched, but using FETCH RELATIVE 0 FROM c will produce error "Cursor C is not positioned in a valid record".

=>

The FETCH RELATIVE 1 FROM cursor_name statement should be equivalent to FETCH NEXT FROM cursor_name (or FETCH cursor_name), however it skips the first row;

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 next from c;
fetch relative 1 from c;
rowval = c.rowval;
rowcount = row_count;
suspend;
end

This returns (2, 1) for (rowval, rowcount), instead of expected (1, 1). For subsequent fetches, it behaves as expected (fetching the next record), so it seems the problem is with the FETCH RELATIVE only when the cursor is newly opened. Using FETCH RELATIVE 2 FROM c will return (3,1) instead of (2,1).

It seems to behave as if the first row was already fetched, but using FETCH RELATIVE 0 FROM c will produce error "Cursor C is not positioned in a valid record".

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 14, 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

Commented by: @mrotteveel

@dmitry, I can confirm it works correctly in 4.0.0.2369

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.8 [ 10960 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 17, 2021

Modified by: @pavel-zotov

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

QA Status: No test => Done with caveats

Test Details: Currently works OK only for 4.0.0.2369. Intermediate build 3.0.8.33416 with stamp 17-feb-2021 16:35 still issues wrong result.
Sent letter to dimitr, 17.02.2021 20:46.

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