Обсуждение: Bug #664: Strange cursor behaviour with particular database schema

Geert-Jan Van den Bogaerde (gvdbogae@vub.ac.be) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Strange cursor behaviour with particular database schema

Long Description

With the (stripped-down) database schema attached in the example code, i get the following result on the interative
console(psql): 

template1=> begin work;
BEGIN
template1=> declare foo cursor for select * from nodes_view where node_id in (select id from nodes where parent in
(2));
DECLARE
template1=> fetch 1 from foo;
 resource_id |     resource_creationDate     |   resource_modificationDate   | node_id | node_parent | node_name
-------------+-------------------------------+-------------------------------+---------+-------------+-----------
           4 | 2002-05-11 23:00:24.344979+02 | 2002-05-11 23:00:24.344979+02 |       4 |           2 | foo4
(1 row)

template1=> move -1 from foo;
MOVE 0
template1=> fetch 1 from foo;
 resource_id | resource_creationDate | resource_modificationDate | node_id | node_parent | node_name
-------------+-----------------------+---------------------------+---------+-------------+-----------
(0 rows)

template1=>

Which even though the SQL query is less than optimal seems to me to be a bug. The query works fine, the only weirdness
seemsto occur after a MOVE on the cursor. It only seems to occur with this rather particular database schema. I ran
accrossthis because some of my SQL is auto-generated from some scripts and thus not always very well optimized. 


Sample Code

CREATE TABLE "resources" (
        "id" integer DEFAULT nextval('resourceSequence'::text) NOT NULL,
        "creationDate" timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT
NULL,
        "modificationDate" timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone
NOT NULL,
        Constraint "resources_id_pk" Primary Key ("id")
);

CREATE TABLE "nodes" (
        "id" integer NOT NULL,
        "parent" integer,
        "name" character varying(250) NOT NULL,
        Constraint "nodes_id_pk" Primary Key ("id")
);

CREATE VIEW "resources_view" as SELECT resources.id AS resource_id, resources."creationDate" AS "resour
ce_creationDate", resources."modificationDate" AS "resource_modificationDate" FROM resources;

CREATE VIEW "nodes_view" as SELECT resources_view.resource_id, resources_view."resource_creationDate",
resources_view."resource_modificationDate", nodes.id AS node_id, nodes.parent AS node_parent, nodes.nam
e AS node_name FROM (nodes LEFT JOIN resources_view ON ((resources_view.resource_id = nodes.id)));

INSERT INTO resources (id) VALUES (1);
INSERT INTO resources (id) VALUES (2);
INSERT INTO resources (id) VALUES (3);
INSERT INTO resources (id) VALUES (4);
INSERT INTO resources (id) VALUES (5);

INSERT INTO nodes (id, parent, name) VALUES (1, NULL, 'foo1');
INSERT INTO nodes (id, parent, name) VALUES (2, 1, 'foo2');
INSERT INTO nodes (id, parent, name) VALUES (3, 1, 'foo3');
INSERT INTO nodes (id, parent, name) VALUES (4, 2, 'foo4');
INSERT INTO nodes (id, parent, name) VALUES (5, 2, 'foo5');


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> template1=> move -1 from foo;
> MOVE 0

Not sure what you expected this to do, but the response should have
clued you that it didn't do anything.  I suspect you are looking
for "MOVE BACKWARD 1 FROM foo" ...

            regards, tom lane

On Sun, 2002-05-12 at 17:58, Tom Lane wrote:
> pgsql-bugs@postgresql.org writes:
> > template1=> move -1 from foo;
> > MOVE 0
>
> Not sure what you expected this to do, but the response should have
> clued you that it didn't do anything.  I suspect you are looking
> for "MOVE BACKWARD 1 FROM foo" ...
>
>             regards, tom lane
>

Really? Though I agree the documentation doesn't seem to mention it, in
my experience, "move -<number> from <cursor>" is equivalent to "move
backward <number> from <cursor>", and the "MOVE 0" merely means that the
new cursor position is 0 (the first row).

Certainly, replacing "move -1 from foo" with "move backward 1 from foo"
here changes nothing:

-----------------------------
template1=> begin work;
BEGIN
template1=> declare foo cursor for select * from nodes_view where
node_id in (select id from nodes where parent in (2));
DECLARE
template1=> fetch 1 from foo;
 resource_id |     resource_creationDate     |
resource_modificationDate   | node_id | node_parent | node_name
-------------+-------------------------------+-------------------------------+---------+-------------+-----------
           4 | 2002-05-11 23:00:24.344979+02 | 2002-05-11
23:00:24.344979+02 |       4 |           2 | foo4
(1 row)

template1=> move backward 1 from foo;
MOVE 0
template1=> fetch 1 from foo;
 resource_id | resource_creationDate | resource_modificationDate |
node_id | node_parent | node_name
-------------+-----------------------+---------------------------+---------+-------------+-----------
(0 rows)

template1=>
--------------------------------------

It seems to me that the second fetch operation should retrieve the same
row as the first fetch.

I apologize if it is my understanding of cursors which is at fault here,
but this seems like a bug to me.

Using a different query on the same database schema things work fine,
though:

------------------------------------
template1=> declare foo cursor for select * from nodes where parent in
(2);
NOTICE:  Closing pre-existing portal "foo"
DECLARE
template1=> fetch 1 from foo;
 id | parent | name
----+--------+------
  4 |      2 | foo4
(1 row)

template1=> move backward 1 from foo;
MOVE 0
template1=> fetch 1 from foo;
 id | parent | name
----+--------+------
  4 |      2 | foo4
(1 row)

template1=>
--------------------------------------------------------------