A scary subquery behavior
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 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?
Or at the very least, since nothing was returned from the sub-select, no
rows should have been deleted, not all rows!
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Apr 2, 2015 at 7:17 AM, RICKARD ESPING <
rickard.esping@migrationsverket.se> wrote:
> 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 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?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bd76f7cf87df10512bc333c
That was my thought at first, that i shouldn't delete anything.
I then had another look at the query and realized what happens.
The query is interpreted as below.
delete from table3 where table3.table2_id in (
select table3.table2_id from table2
where table2.table1_id in (500, 557, 256, 158, 598, 601)
);
OF COURSE! The reference to table2_id is seen as a correlation copy from
the outer query! And that's also why no error was thrown.
Art
On Apr 2, 2015 7:57 AM, "RICKARD ESPING" <
rickard.esping@migrationsverket.se> wrote:
> That was my thought at first, that i shouldn't delete anything.
> I then had another look at the query and realized what happens.
>
> The query is interpreted as below.
>
> delete from table3 where table3.table2_id in (
> select table3.table2_id from table2
> where table2.table1_id in (500, 557, 256, 158, 598, 601)
> );>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013a1118ad7f9f0512bc9d47
This was already stated, but It seems this s no error. All columns from all
tables are visible within the query.
In order to be sre we would need to see the schemas of all the tables
involved.
Regards.
On Thu, Apr 2, 2015 at 12:17 PM, RICKARD ESPING <
rickard.esping@migrationsverket.se> wrote:
> 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 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?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7b5d4bc2c55d010512bd8c9f