Complex cursor won't rewind to 0-th row - Mailing list pgsql-bugs

Please CC me off-list, if at all possible.

I don't think I'm being stupid.

I even read the FAQ and TODO list as well as the link from "cursor" in the
TODO list about potential cursors outside transactions.

archive=3D> select version();
                          version
-----------------------------------------------------------
 PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

BEGIN
declare foo cursor  for SELECT DISTINCT article.id, volume, number,
article.article, article.title , 0  + ( 0  + 1 * int4( (text ILIKE
'%destruction%') )  + 2 * int4(substring(text, 1, length(text)/10) ILIKE
'%destruction%')  + 1 * int4( (text ~* 'destruction.{0,20}Iraq') )  )  AS
points FROM article  , article_word, article_word_frequency   WHERE (TRUE=
=20
AND ( TRUE  AND  (text ILIKE '%destruction%')   AND  (text ~*
'destruction.{0,20}Iraq')   ) )  AND article_word.id =3D
article_word_frequency.word AND article_word_frequency.article =3D
article.id  AND (FALSE  OR article_word.word =3D 'iraq' )  ORDER BY  points
desc, volume, number, article.article  ;

DECLARE CURSOR
fetch 1 in foo;
  id   | volume | number | article |                    title=20=20=20=20=
=20=20=20=20=20=20=20=20=20
      | points
-------+--------+--------+---------+---------------------------------------=
------+--------
 10068 | 038    | 003    | 021     | International Agreements on Nuclear
Weapons |      4
(1 row)

fetch 9 in foo;

  id   | volume | number | article |=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
                     title=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20
              | points
-------+--------+--------+---------+---------------------------------------=
---------------------------------------------------------------------------=
------------+--------
 14422 | 048    | 001    | 018     | Iraq's Bomb:  Blueprints and
Artifacts=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
                      |      4
 14555 | 048    | 005    | 013     | It's All over at Al Atheer=20=20=20=20=
=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
             |      4
 15636 | 051    | 006    | 033     | Has Iraq Come Clean at Last?=20=20=20=
=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
             |      4
 11320 | 041    | 001    | 009     | 1985 Outlook: Nuclear Proliferation:
The Pace Quickens=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
              |      2
 14426 | 048    | 001    | 022     | The Vision Thing:  David Shorr
reviews The Future Belongs to Freedom by Eduard Shevardnadze=20=20=20=20=20=
=20=20=20=20=20=20=20=20
                    |      2
 14559 | 048    | 005    | 017     | U.N. Dues:  The Price of Peace=20=20=
=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
             |      2
 15081 | 049    | 009    | 026     | Too Many Crooks?:  Michael Krepon
reviews Arms Control by Committee:  Managing Negotiations with the
Russians by George Bunn |      2
 15773 | 052    | 003    | 035     | Nuclear Excuses=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
             |      2
(8 rows)

NOTE:  That would make 9 (not 8) rows total?

move backward 0 in foo;
MOVE 8

NOTE:  Arooo?  Shouldn't I have moved back 9 (not 8) rows?

move backward 1 in foo;
MOVE 0

I guess not...

fetch 1 in foo;
  id   | volume | number | article |                 title=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
 | points
-------+--------+--------+---------+---------------------------------------=
-+--------
 14422 | 048    | 001    | 018     | Iraq's Bomb:  Blueprints and
Artifacts |      4
(1 row)

Hey -- That wasn't my 0th row the first time around!

I simply CANNOT get back to the first article -- "International Agreements
on Nuclear Weapons" no matter what -- I can do all the "move" and "fetch"
I want, but after first going beyond the 0th row, PostgreSQL insists the
0th article is "Iraq's Bomb:  Blueprints and Artifacts" which just ain't
so.

This was repeatable with at least one, possibly two, other queries in this
data set.  (Though all involved 'Iraq' ...)

However, with a simpler data example, all goes well:

archive=3D> create sequence test_id;
CREATE SEQUENCE
archive=3D> create table test (test_id int4 default nextval('test_id'), test
text);
CREATE TABLE
archive=3D> insert into test(test) values( '1');
INSERT 25170548 1
archive=3D> insert into test(test) values( '2');
INSERT 25170549 1
archive=3D> insert into test(test) values( '3');
INSERT 25170550 1
archive=3D> insert into test(test) values( '4');
INSERT 25170551 1
archive=3D> insert into test(test) values( '5');
INSERT 25170552 1
archive=3D> insert into test(test) values( '6');
INSERT 25170553 1
archive=3D> insert into test(test) values( '7');
INSERT 25170554 1
archive=3D> insert into test(test) values( '8');
INSERT 25170555 1
archive=3D> insert into test(test) values( '9');
INSERT 25170556 1
archive=3D> insert into test(test) values( '10');
INSERT 25170557 1
archive=3D> begin;
BEGIN
archive=3D> declare foo cursor for select test_id, test from test where
test_id > 5 and test_id < 9 order by test_id;
DECLARE CURSOR
archive=3D> fetch 1 in foo;
 test_id | test
---------+------
       6 | 6
(1 row)

archive=3D> fetch 9 in foo;
 test_id | test
---------+------
       7 | 7
       8 | 8
(2 rows)

archive=3D> move backward 10 in foo;
MOVE 3
archive=3D> fetch 1 in foo;
 test_id | test
---------+------
       6 | 6
(1 row)

archive=3D> fetch 1 in foo;
 test_id | test
---------+------
       7 | 7
(1 row)

archive=3D> move backward 0 in foo;
MOVE 1
archive=3D> move backward 2 in foo;
MOVE 0
archive=3D> fetch 1 in foo;
 test_id | test
---------+------
       6 | 6
(1 row)

archive=3D> rollback;
ROLLBACK
archive=3D>

I can only guess that either something horribly funky in the data
(non-ASCII characters?) or the JOIN of the tables is at fault.

Schema and gzipped data (92 Meg) available at:
http://bulletinarchive.org/pg_dump/

Any Ideas?
Please CC me off-list, if at all possible.
I really need to be able to reliably get back to the 0th row...