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/
Show quoted text
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.