Fucking SQL

[sarettah]

ok, i thought i was going to find out that this was an issue in just SQLServer but I just tested and it is an issue in MYSql too. Probably all known about but I just discovered it so I am going to vent a bit.

Ok, you have two tables.

Table1:
Trans_id key
all the other fields

Table2:
Rec_id key
all the other fields

If you say "select trans_id from table2" you get an error, as you should because the column trans_id is not in table2.

However, if you run that as a sub query in a where clause it does not return an error. I am not sure what it returns (i suspect null) but whatever the fuck it returns matches fucking everything.

So a query like "delete from table1 where trans_id in (select trans_id from table2)" where you would expect it to throw an error instead matches every fucking record in table1 and hence deletes them all.

i did this originally, by accident, in SQLServer yesterday, wiping out 18,000 records when I expected to delete about 10.

I tested in MYSql just a minute ago on my server and it worked the same way.

i also tested in FoxPro on my desk top. It threw an error telling me that the trans_id column was missing. i love my FoxPro, i don't give a shit what anyone else says about it.




Dammit