473,723 Members | 2,204 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #1
5 1240
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

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".

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
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 <kl*****@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.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFA7c86Y5T wig3Ge+YRArXuAK CLjC3AfsePhwlcU RTI8PfSNYghGwCf UPhS
MQ1F2ahmPecqo+o CVQ0pVzY=
=wuie
-----END PGP SIGNATURE-----

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2117
by: Peter Gorelczenko | last post by:
Good Morning, I'm running Alpha 4.1 on Linux. I'm new to MySql but familliar with othe= r=20 databases. I set up a user with "Create Temporary Tables" permissions. = That=20 user can create temp tables but can not alter or update the table. That= =20 user is getting "Update command denied to user..." for the temp table. = They=20 also get this error when an alter table is executed. Doesn't the user ha=
3
2468
by: Narine | last post by:
Hi All, I need to write one complicated update statement and I'm looking at maybe finding a simpler way to do it. I have 2 tables: 1.Photo Table PhotoID FileName 1 111.jpg
17
5023
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no)
33
4292
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and setting default value to a UDF that manages the auto-numbering. Access won't take a UDF as a default value. Okay, I'll use SQL WITHOUT any aggregate functions, for the default value. Access won't do that either. Okay, I create a second...
1
1461
by: ramonred | last post by:
Hi, I am following along an example from the book <b>Beginning Visual Web Programming in C#</b> My environment is this: IIS 5.1 on XP Pro, I have the checkbox 'use windows integrated authentication' unchecked, and I am allowing anonymous access. Prior to building the example that is giving the error, the app would ask you to login if you weren't already logged in by redirecting you to the login
9
2273
by: rhaazy | last post by:
Using MS SQL 2000 I have a stored procedure that processes an XML file generated from an Audit program. The XML looks somewhat like this: <ComputerScan> <scanheader> <ScanDate>somedate&time</ScanDate>
4
1624
by: duffdevice | last post by:
Hi, I came across this unexpected behavior while working on something else. I am attempting to return a custom type by value from a global function. I have a trace in the custom class's copy constructor, and I expected this code to tell me that the custom copy constructor had been called twice. Instead, it's only called once and the program executes correctly. Is this an optimization related to my compiler (g++ v4.0.1) or is this...
3
1781
by: meeraguna | last post by:
We have 20 -30 normalized tables in our dartabase . Also we have 4 tables where we store the calculated data fron those normalised tables. The Reason we have these 4 denormalised tables is when we try to do the calcultion on the fly, our site becomes very slow. So We have precalculated and stored it in 4 tables. The Process we use to do the precalcultion, will get do the calculation and and store it in a temp table. It will compare...
16
3507
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
0
8868
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9388
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9160
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9090
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8062
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6685
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5996
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4504
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2149
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.