0

I had to restore table content yesterday since a application could not find the data needed. On examining what had happened we discovered a quite scary scenario. The development team was meaning to do a delete of specific posts in the table and executed the below query.

delete from table_3 where table2_id in (
  select table2_id from table2
  where table1_id in (500, 557, 256, 158, 598, 601) 
);

This resulted in a delete of all the posts in table3. There was an error in the query though the primary key in table2 is named id not table2_id. The correct query should look like this.

delete from table_3 where table2_id in (
  select id from table2
   where table1_id in (500, 557, 256, 158, 598, 601) 
);

To conclude. When trying to execute the faulty subquery separately it correctly throws an error that there is no column named table2_id in table2, but when executing the entire query no error is thrown and all the posts in table3 are deleted.

Shouldn't it throw an error when executing the entire query if there is something wrong with the subquery?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Rickard Esping
  • 71
  • 1
  • 7

1 Answers1

0

The only thing scary here is your developers.

Their query as a whole behaved exactly as it should. There is no error, nor should the server have raised one.

The subquery was returning a value from the outer table, because that's what was coded.

Perhaps they should have used the industry standard naming convention of id for the primary key column of every table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    I.e. "delete from table_3 where table2_id in (select table_3.table2_id from table2..." is executed! – jarlh Apr 02 '15 at 11:36
  • I realized the same thing when i had another look at the query. I didn't make the connection to the table in outer query at first. – Rickard Esping Apr 02 '15 at 11:53