A developer dropped some tables in their schema. They did this manually outside of our application suite. The applications expect the tables to exist. The result is that our applications blow up when you select certain data. I traced this problem back to the developer who actually dropped the table. I decided to write a script to find all occurrences of the problem. It was supposed to correct the application data based on the absence of the database table. I did a couple quick tests and all looked good. However I am getting into the practice of writing unit test scripts to fully verify my code. I was surprised to find that my script was actually deleting a lot more data than it should be doing.I stared at the script for a long time. And I kept running through my unit tests again and again. But the script kept eliminating all the application data, good and bad alike. This was troubling. As a software engineer, logic problems like this should be trivial for me. It did not help that we had a lot of distractions at the office. However I am trained to be able to work in any conditions and produce correct code. So I just added some restrictions to the WHERE clause in my script. Then the script did not delete all the application data. However I still did not understand what was going on. The only thing I could do was go over the code line by line until I saw the light.
This was the pseudo code for the script:
Cursor to loop through app application records
Check if table exists
If table does not exist
Delete application data
It does not get much simpler than this. Then I finally did spot the error. The where clause in my check was like WHERE column = table.column. It should have been WHERE column = cursor.column. At that point I felt pretty stupid. I spent the whole afternoon looking for that one. I write about it here to warn you in the rare case that you write or stumble upon such a problem in your own journey.
