Thread: unexpected update behavior with temp tables
This bug? feature? caused a bit of havoc for us yesterday...A
reproducible example follows. Essentially, if you have a table with a
primary key called "id", and you create a temp table (via a "select
into") containing a subset of the data from the table but where the
primary key field is renamed (in the example below, it is called
"not_id"), the where clause of the following update statement (which I
would expect to generate an error saying that the temp table has no
column named "id") matches _all_ the rows in your table, updating them
all! Why does this statement work? Shouldn't it result in an error?
OPT=# create table foo (id serial, b varchar, constraint foo_pkey
primary key(id));
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
"serial" column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
OPT=# insert into foo (b) values ('Tim');
INSERT 1178158 1
OPT=# insert into foo (b) values ('Ben');
INSERT 1178159 1
OPT=# insert into foo (b) values ('Erin');
INSERT 1178160 1
OPT=# insert into foo (b) values ('Bob');
INSERT 1178161 1
OPT=# select * from foo;
id | b
----+------
1 | Tim
2 | Ben
3 | Erin
4 | Bob
(4 rows)
OPT=# select id as not_id, b into temp temp_foo from foo where b =
'Tim';
SELECT
OPT=# select * from temp_foo;
not_id | b
--------+-----
1 | Tim
(1 row)
OPT=# update foo set b = 'Timothy' where id in (select id from
temp_foo);
UPDATE 4
OPT=# select * from foo;
id | b
----+---------
1 | Timothy
2 | Timothy
3 | Timothy
4 | Timothy
(4 rows)
The following update, which attempt to use a non-existent column named
"bogus", demonstrates the behavior I would expect to see:
OPT=# update foo set b = 'Sam' where id in (select bogus from temp_foo);
ERROR: column "bogus" does not exist
Timothy Perrigo wrote: > OPT=# select id as not_id, b into temp temp_foo from foo where b = 'Tim'; > SELECT > OPT=# select * from temp_foo; > not_id | b > --------+----- > 1 | Tim > (1 row) > > OPT=# update foo set b = 'Timothy' where id in (select id from temp_foo); > UPDATE 4 > OPT=# select * from foo; > id | b > ----+--------- > 1 | Timothy > 2 | Timothy > 3 | Timothy > 4 | Timothy > (4 rows) I think I can see what's happening, but don't know enough internals to say why. The "id" in the subselect must be binding to the outer query. I could see how that might be desirable in some circumstances, but could easily cause trouble in many cases. -- Richard Huxton Archonet Ltd
On Jul 8, 2004, at 8:57 AM, Richard Huxton wrote: > Timothy Perrigo wrote: > >> OPT=# select id as not_id, b into temp temp_foo from foo where b = >> 'Tim'; >> SELECT >> OPT=# select * from temp_foo; >> not_id | b >> --------+----- >> 1 | Tim >> (1 row) >> OPT=# update foo set b = 'Timothy' where id in (select id from >> temp_foo); >> UPDATE 4 >> OPT=# select * from foo; >> id | b >> ----+--------- >> 1 | Timothy >> 2 | Timothy >> 3 | Timothy >> 4 | Timothy >> (4 rows) > > I think I can see what's happening, but don't know enough internals to > say why. > > The "id" in the subselect must be binding to the outer query. I could > see how that might be desirable in some circumstances, but could > easily cause trouble in many cases. > > -- > Richard Huxton > Archonet Ltd > > Richard, I think you're probably right...I modified the temp table, renaming both fields: select id as not_id, b as name into temp temp_foo from foo where b = 'Tim'; Then ran the following update: OPT=# update foo set b = 'Timothy' where b in (select b from temp_foo); UPDATE 4 OPT=# select * from foo; id | b ----+--------- 1 | Timothy 2 | Timothy 3 | Timothy 4 | Timothy (4 rows) So it does look like the column in the subselect must be binding to the outer query, though this is _not_ what I would expect to happen in this situation. Thanks for the response. Tim
On Thu, 8 Jul 2004, Timothy Perrigo wrote: > OPT=# select id as not_id, b into temp temp_foo from foo where b = > 'Tim'; > SELECT > OPT=# select * from temp_foo; > not_id | b > --------+----- > 1 | Tim > (1 row) > > OPT=# update foo set b = 'Timothy' where id in (select id from > temp_foo); Subselects like that are AFAIK allowed to see outer columns according to the SQL spec. Thus, the id inside the subselect is effectively foo.id. This behavior is useful when you want to do something like a function or operator on an inner column and an outer column inside the subselect and painful in cases like this where effectively the clause becomes "id is not null" which for a primary key is itself a long way of saying "true".
On Jul 8, 2004, at 9:14 AM, Stephan Szabo wrote: > > On Thu, 8 Jul 2004, Timothy Perrigo wrote: > >> OPT=# select id as not_id, b into temp temp_foo from foo where b = >> 'Tim'; >> SELECT >> OPT=# select * from temp_foo; >> not_id | b >> --------+----- >> 1 | Tim >> (1 row) >> >> OPT=# update foo set b = 'Timothy' where id in (select id from >> temp_foo); > > Subselects like that are AFAIK allowed to see outer columns according > to > the SQL spec. Thus, the id inside the subselect is effectively foo.id. > > This behavior is useful when you want to do something like a function > or > operator on an inner column and an outer column inside the subselect > and > painful in cases like this where effectively the clause becomes "id is > not > null" which for a primary key is itself a long way of saying "true". > > Thanks for the reply, Stephan. I guess I can see the rationale for this, though it is quite easy to cause yourself quite a bit of grief. It would certainly make things safer if columns in the subselect which refer to columns in the table from the outer query where required to be fully specified (i.e. "foo.id", instead of just "id"), but if this behavior is part of the standard, I imagine there's little chance of changing it... I appreciate the assistance! Tim
On Thu, Jul 08, 2004 at 09:28:16AM -0500, Timothy Perrigo wrote: > Thanks for the reply, Stephan. I guess I can see the rationale for > this, though it is quite easy to cause yourself quite a bit of grief. > It would certainly make things safer if columns in the subselect which > refer to columns in the table from the outer query where required to be > fully specified (i.e. "foo.id", instead of just "id"), but if this > behavior is part of the standard, I imagine there's little chance of > changing it... Not to mention the amount of SQL code out there it would break! We use this feature a lot. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.