Re: SQL Anomaly?

Richard, What you had was actually a horrible correlated sub-query... DELETE FROM tmp_kontakt WHERE s_doknr IN (SELECT UNIQUE(tmp_kontakt.s_doknr) FROM kontakt) Note the extra table specifier. For each row in the tmp_kontakt table with s_doknr = D, it was doing something like a complete scan of the kontakt table, generating a set of 1514 rows with the value D, then doing the UNIQUE on it (generating a single row with D in it), then deleting the row in tmp_kontakt because the s_doknr value D it was using was present in the table. You assert: } The subquery was not correctly formulated. It was a correctly formatted subquery, but not the one you thought it was. It was doing what you told it to do, and no error message was possible because the operation was valid. It would have worked faster if you'd omitted the UNIQUE, but it would have had the same result. Hands up if you haven't been bitten by something similar at some time... My hand is firmly on the keyboard! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: spitz@GANS2X.ana.med.uni-muenchen.de (Richard Spitz) }Date: 30 Jan 1996 15:20:53 GMT }X-Informix-List-Id: <news.20714> } }Dear Informix or SQL Gurus, } }I ran into a problem with the following simple statement: } } delete from tmp_kontakt } where s_doknr in (select unique(s_doknr) from kontakt) } ^^^^^^^ } }Table "kontakt" has 1514 rows, while "tmp_kontakt" had ~6500 rows. }The statement took over one hour to complete, and resulted in all }but 14 rows from "tmp_kontakt" being deleted. These rows had a }"NULL" entry for "tmp_kontakt.s_doknr". } }This was not what I wanted, only 1514 rows had to be deleted. It turned }out that I had made a typing mistake and there was no such column }"s_doknr" in table "kontakt". The correct column name is "ko_s_doknr". }After restoring the table "tmp_kontakt" with the original data and }correcting the sql statement, it took only very few seconds and yielded }the correct result. } }What troubles me is that no error message was returned with the wrong }sql statement? Shouldn't dbaccess have returned error number -217, }"Column (s_doknr) not found in any table in the query."? Maybe it was }confused by the fact that the column name "s_doknr" indeed exists }in table "tmp_kontakt", but still that is not correct behaviour. The }subquery was not correctly formulated.