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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-bugs(at)postgresql(dot)org>,"Paul" <paul(dot)cocei(at)punct(dot)ro>
Subject: Re: BUG #5972: Update with subquery: erroneous results for foreign key field
Date: 2011-04-12 13:42:00
Message-ID: 4DA41058020000250003C6C9@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Paul" <paul(dot)cocei(at)punct(dot)ro> wrote:

> update cart set status = 1 where userid = (select userid from
> users where email = 'example(at)example(dot)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(at)example(dot)com');

Not a bug.

-Kevin

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Merlin Moncure 2011-04-12 13:57:45 Re: PostgreSQL backend process high memory usage issue
Previous Message poiretlise 2011-04-12 12:57:04 BUG #5973: erreur SERIAL 4