Thread: BUG #5972: Update with subquery: erroneous results for foreign key field

The following bug has been logged online:

Bug reference:      5972
Logged by:          Paul
Email address:      paul.cocei@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 = 'example@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

"Paul" <paul.cocei@punct.ro> wrote:

> update cart set status = 1 where userid = (select userid from
> users where email = 'example@example.com');
>
> As you can see, the subquery is broken (users table doesn't have
> the column userid).

By standard, if the identifier isn't defined within the most local
scope, each enclosing scope, from the inside out, will be checked.
I would expect the above to update each row where cart.userid was
not null.

I always use and recommend aliases where practical.  If you wrote it
this way, such a mistake would be clearly identified:

update cart set status = 1 where userid = (select u.userid from
users u where u.email = 'example@example.com');

Not a bug.

-Kevin

Thank you very much, i understand now :)

Paul

On Tue, Apr 12, 2011 at 4:42 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:

> "Paul" <paul.cocei@punct.ro> wrote:
>
> > update cart set status = 1 where userid = (select userid from
> > users where email = 'example@example.com');
> >
> > As you can see, the subquery is broken (users table doesn't have
> > the column userid).
>
> By standard, if the identifier isn't defined within the most local
> scope, each enclosing scope, from the inside out, will be checked.
> I would expect the above to update each row where cart.userid was
> not null.
>
> I always use and recommend aliases where practical.  If you wrote it
> this way, such a mistake would be clearly identified:
>
> update cart set status = 1 where userid = (select u.userid from
> users u where u.email = 'example@example.com');
>
> Not a bug.
>
> -Kevin
>