Friday, September 26, 2008

Oracle Subquery bug - Test each subquery individually

Here arises the importance of unit testing each subquery individually. Consider the scenario below:

I have this code:

 DELETE FROM cfgview
        WHERE cfgviewid NOT IN (SELECT
                                FROM vw_ext_acc_configview);

The inner subquery if executed alone, returns an error because the columnname cfgviewid doesn't exist.

But if you execute the full query, it returns successfully and deletes 0 rows.

So, this implies that you need to unit test each query before adding it as a subquery.