The Most Important Debugging Skill You Can Have

We've discussed everything you'd expect from this theme of developing queries. As I began this week, I realized that there was one more skill you need to have when developing SQL queries or any programming language, one that is simple to do but highly seductive to avoid. I wanted to talk about that one today.

It is one of the best debugging techniques I've ever found for solving some of the toughest bugs, yet I completely understand why you may not want to do it. You are probably familiar with these tricky bugs. Let's take a simple query, for example:

SELECT
    T0.CardCode
    T0.CardName,
    SUM(T1.LineTotal)
FROM
   ORDR T0
   INNER JOIN INV1 T1 ON T0.DocEntry = T0.DocEntry
GROUP BY 
    T0.CardCode,
    T0.CardName
        

There are three errors here. The first is easy to fix if you can figure out which T0 has the error -- namely, I forgot a comma after CardCode in the SELECT. The Style Guide I did in the last newsletter helps with that one.

If you are not watching, the other two can get you the incorrect results. Run this, and you will get

Huli Pizza Company shows a result of $283,106,212.00 There are several ways to determine if this is an error. One would be to run a sales analysis on a specific customer and compare the values. While it is nice to find a report that gives you correlating numbers, you may not always get one. Here, a lifetime sales analysis of Huli Pizza provides us with a total of $29,553.73. Often, you can guess the number because you know the customers. If you've taken any of the SAP Business One courses, you know there's no way Huli Pizza Company is big enough to spend 283 million dollars on anything. This is a logical bug. We find the ON is wrong:

ON T0.DocEntry = T0.DocEntry
        

looks at the same table and column. It Should be.

ON T0.DocEntry = T1.DocEntry 
        

Rerunning that, we get another set of numbers.

However, if you check those against a report that might give you similar data, you still get numbers that make no sense -- Huli Pizza Company doesn't show up on the list.

Many of you have found the error already. I matched INV1 with ORDR. This should be

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
        

I try that:

Huli Pizza Company returns with a reasonable $30,330.60 value. It does not reconcile against the sales analysis since I needed to account for all the circumstances the sales analysis does, such as returns and credit memos, but it is close.

Confusing RDR1 for OINV1 is a silly mistake, but most developers have made them and will make them again. I have on occasion made this mistake one day and taken a few days to find it. I would Stare at my code for hours and never catch that error.

Sometimes, the best debugging tool is to stop. It seems to be counter-productive; doing nothing won't solve the problem. However, taking a rest or working on something else has two powerful effects: First, you lose your investment in the code. Coding is personal: We, as developers, put our souls into soulless machines. That level of care can blind us at times. Stepping away breaks that level of passionate programming and lets us look at the code objectively. Secondly, there's the "In the shower" phenomenon. You'll think of the solution while in the shower or doing something unrelated to the query or application. Your brain needs to absorb what you did and stop thinking superficially. Instead of the shower, answers appear on a walk around the neighborhood, along the beach, or on vacation.

That's why many of you saw the answer to this rather quickly. You didn't have the investment and quickly figured INV1 and ORDR don't match.

So here are two things you need to know: some bugs change your results, and some of these come from mistakes in your logic, often silly. Silly mistakes often become apparent when you step away from the work and return to it. I can't recommend stopping enough to help understand the problems with projects.