FETCH ABSOLUTE and RELATIVE beyond bounds of cursor should always position
immediately before-first or after-last
-----------------------------------------------------------------------------------------------------------------
Key: CORE-6487
URL: http://tracker.firebirdsql.org/browse/CORE-6487
Project: Firebird Core
Issue Type: Bug
Affects Versions: 4.0 RC 1, 3.0.7
Reporter: Mark Rotteveel
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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
