The following bug has been logged online:
Bug reference: 5972
Logged by: Paul
Email address: paul.co...@punct.ro
PostgreSQL version: 9.0.3
Operating system: CentOS 5.5
Description: Update with subquery: erroneous results for foreign key
field
Details:
Hello.
We found what we think is a bug while running an update with a subquery in
the condition.
The relevant database layout is as follows:
CREATE TABLE users (
id integer serial PRIMARY KEY,
username character varying(32),
email character varying(200),
password character varying(32),
status smallint DEFAULT 1 NOT NULL,
rdate timestamp without time zone DEFAULT now() NOT NULL,
last_action timestamp without time zone DEFAULT now() NOT NULL,
);
CREATE TABLE cart (
id integer serial PRIMARY KEY,
userid integer,
dt timestamp without time zone DEFAULT now(),
status integer DEFAULT 0,
optional_firstname character varying(100),
optional_lastname character varying(100),
optional_email character varying(254)
);
ALTER TABLE ONLY cart
ADD CONSTRAINT cart_userid_fkey FOREIGN KEY (userid) REFERENCES
users(id);
We issued the following query, directly through psql:
update cart set status = 1 where userid = (select userid from users where
email = 'exam...@example.com');
As you can see, the subquery is broken (users table doesn't have the column
userid). We missed that when we ran it, and we were stunned to see that the
query updated 1573 rows, when we expected it to updated only 1 (even though
this one should have failed).
We further investigated the problem, and were able to replicate it on other
databases as well.
It seems that the folowing query has the same result:
update cart set status = 1 where userid = (select userid);
So it seems that PostgreSQL uses the foreign key as some kind of "shortcut",
even though the following query fails (more than one row returned by a
subquery)
update cart set status = 1 where userid=(select cart.userid from cart, users
where cart.userid = users.id);
We are still not sure if this is a bug or the desired behaviour, but it
seems strange (because the subquery, issued separately, fails).
Thank you very much,
Paul
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
