Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group