Geert-Jan Van den Bogaerde ([EMAIL PROTECTED]) 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 seems to occur after a MOVE on the cursor. It
only seems to occur with this rather particular database schema. I ran accross this
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
