I started working on a trouble ticket today. My team lead called me and wanted to know how long it would take to resolve the problem. I estimated a couple weeks since I did not know what the issue was. That was a worst case estimate.Luckily I was able to duplicate the problem. The user interface called a Pro*C function to fill up a structure. The UI code looked like it was extracting the results correctly. So I went into the Pro*C. It was a bit confusing. A bunch of records were being summed up. Then the code tried to transfer the result of the SQL query into the structure required by the caller.
The code to copy results in the structure was convoluted. That was a sign that the error might be in there. There were multiple loops going on, coupled with a number of counters. The loops went through the query results. The counters identified where we were in filling up the output structure. That's when I spotted some disturbing logic. If the two did not match, the output got set to zeros. This was the exact behavior that was being reported in the bug.
It was then that I realized what was going on. The code assumed a specific order in the query results. But the SQL did not have an ORDER BY clause. I decided against adding the ORDER BY to the SQL. We might be querying a whole lot of code. I did not want to add extra overhead for the database to do the sort. I just wrote a quick routine to scan through the results while putting the answers in the output format. Problem solved. Now I need to fix all the other places where the code assumes a specific order coming back from the query. This was quite an exciting problem to solve. I even worked some extra hours to get this thing fixed today.
