Bug #866: Cursor scrolling broken in 7.3.1 (works in 7.2.1)

Florian Wunderlich (fwunderlich(at)devbrain(dot)de) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Cursor scrolling broken in 7.3.1 (works in 7.2.1)

Long Description
A MOVE BACKWARD after a FETCH in a cursor declared for a SELECT with a WHERE clause does not work. The cursor stays positioned where it is.

In the example code, it is expected that both FETCH statements return the same tuples (one tuple with id=1).

This is the case with postgresql-7.2.1, started with exactly the same options as 7.3.1, which does not return any tuples for the second FETCH statement. 7.3.1 does however work correctly if the WHERE clause is dropped.

There seems to be some correlation with bug 664 / 665, which does not seem to have been fixed.

Here is the example code pasted to psql connected to 7.3.1:

items=# begin;
BEGIN
items=# create table test (id int4 primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
CREATE TABLE
items=# insert into test values (1);
INSERT 31047 1
items=# insert into test values (2);
INSERT 31048 1
items=# declare c scroll cursor for select * from test where (id = 1);
DECLARE CURSOR
items=# fetch all from c;
id
----
1
(1 row)

items=# move backward all in c;
MOVE 0
items=# fetch all from c;
id
----
(0 rows)

items=# rollback;
ROLLBACK
items=# \q

Here is the correct 7.2.1 behavior:

items=# begin;
BEGIN
items=# create table test (id int4 primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
CREATE
items=# insert into test values (1);
INSERT 197564 1
items=# insert into test values (2);
INSERT 197565 1
items=# declare c scroll cursor for select * from test where (id = 1);
DECLARE
items=# fetch all from c;
id
----
1
(1 row)

items=# move backward all in c;
MOVE 1
items=# fetch all from c;
id
----
1
(1 row)

items=# rollback;
ROLLBACK
items=# \q

Sample Code
begin;
create table test (id int4 primary key);
insert into test values (1);
insert into test values (2);
declare c scroll cursor for select * from test where (id = 1);
fetch all from c;
move backward all in c;
fetch all from c;
rollback;

No file was uploaded with this report