Re: !!! Bad Subquery == Delete ALL !!!

Is this realy correct SQL behaviour? > amiller@tasc.com (Andrew Miller) writes: > >> I recently executed the following query on my Informix database: > >> delete from BLOBtable where PageNumber in > >> (select PageNumber from DocumentTable where DocumentID=66272)> >> Now as it turns out, there is no PageNumber field in my DocumentTable table, so > >> the subquery failed. Instead of returning an error, the Informix engine DELETED > >>ALL THE BLOBS IN MY BLOB TABLE (imaging my surprise when instead of seeing "11 > >> rows deleted" I saw "1700 rows deleted")!!!!! > > Here's the explanation given to me by Informix tech. support. Unfortunately, > the query performed appropriately: > > ================================================================================ > i explained the query is operating properly. the uidnpage column isn't in > tbdocument table in the subquery. but since the table it is in, tbmagpage, is > bein SELECTed from in the DELETE query, uidnpage will be recognized. now, when > the query runs the pointer in the tbdocument will be on the row where uidndoc = > 66272, but the pointer in the tbmagpage table will go trhu all the rows, thereby > grabbing all the possible values for uidnpage in tbmagpage. those values will > then be a part of the IN clause of the > DELETE, and since all values of uidnpage were found, all rows in tbmagpage will > be deleted. > ================================================================================ > > UGH! This definately goes on my top ten not-so-cool-things that happened to me > this week. I am not sure I understand the answer from Informix fully. They seem to have made up another example with other table and field names. What it seems to say is that the PageNumber in the select statement in this case is actually read from the BLOBtable table. As I have understood sql any select anywhere should only select fields from tables mentioned in the from-clause. Am I wrong in the case of a subselect like this? In any case if you refrase the statement to: delete from BLOBtable where PageNumber in (select DocumentTable.PageNumber from DocumentTable where DocumentTable.DocumentID=66272) it should return an error. I know this is redundant, but it is correct sql. I allways use this form in this type of sub select statements so I can easily see what tables the fields come frome. This is of course more important when fields in the where clause are compared to fields in the table you delete from (or update if it is an update statement which probably would have the same problem). Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company