Hi, John-

>So how do you know when to stop debugging?

Now _that_ is a helluva good question. ;-) IMO, it's 3 parts experience and 8 parts science, depending on how much coffee you have in the house. Seriously, this was a pretty concise example of an interesting debugging experience. Of course, it may be much worse than this. The more experience I have programming, the better feel I have for it. Ideally, you use the scientific method iteratively until you have one cause that explains all the observed behavior, and have disproven any alternatives. Experience helps with knowing what alternatives there are and how likely each are. You want to start with the most common and easiest to test.

So, in this example, the debugging loop went like this:

1. Narrow down the behavior (grid showed duplicate rows), investigating the details (happens in form and corresponding report) but not in other forms.
2. Create a hypothesis of what may be causing it (data is being doubled)
3. Test the hypothesis one step at a time (check the data),
4. Analyze the results (data is not doubled in the tables)
5. If disproven, discard the hypothesis, and loop back to Step 1

1. Line items are doubled for some orders but not others. Items are doubled for orders with certain vendors (turned out to be a huge clue I missed, btw)
2. Corrupted index can cause erratically, suddenly appearing phantom records
3. Reindex
4. The behavior is unchanged.
5. It's not the index

1. The data is built from a complex series of queries of a type that I typically have to be careful developing.
2a. The queries joins are incorrect. Reviewed background material.
3a. Reviewed the top-level query (but not one of the contributing queries, another mistake I made). Stripped the query from the view, and created a test PRG to investigate the view. Restructured the query to use WHERE clauses instead of JOINs. Ran the query.
4a. Same incorrect results, so loop to step 2.
2b. Hypothesize I have null results from my LEFT JOIN that are causing duplicated rows
3b. Simplified query to just join members to try and identify the null results
4b. Same incorrect results.

5. It's not the query. But...

2. Double check my assumptions. Very important in debugging esp. if you get stumped. I assumed the problem was in the main view (and actually I would only test one at a time anyway). But, there is an underlying query that I haven't checked.
3. Repeat 3a-4b above for the underlying query.
4. Same incorrect results, but have narrowed the problem down to the underlying query.
5. It's not the underlying query.

2. Double check assumptiosn again. I'm assuming the problem is in the query, but it's not. So, the problem may be in the data.
3. Reset the system to the baseline, restored data to an recent dataset from before the problem occured. Reran the view.
4. Results are correct--not doubled. The problem is in the data _somewhere_.

2. What data does the suspect view use (3 tables)? What data is left joined in the view (there was only one)? Are there duplicates in the table? Restored the "problem" data set, and queried the table for duplicates. There are. Hypothesize that if the duplcates are removed, the view will be correct.
3. Deleted the duplicates, ran the view.
4. The results of the view were correct.

2. Why are the duplicates causing the problem? At this point, actually as soon as I looked at what rows were duplicates (based in primary key, btw), I knew what the problem was. Since the key was blank, and there were two blank records. Only some vendors have blank reps (the problem table). Hypopthesize that only vendors with blank reps have "doubled" order line items.
3. Run through the original problem dataset
4. Confirm that's the case.

Once I get to this point, btw, I'm not in debugging mode any more. I step back, in this case, to design mode. I.e. What _should_ happen? So, the discussion we were having about n-tier and solutions and table triggers and so on is outside the scope of debugging. Why is it important to make that distinction? I don't know about you, but for a lot of us, there's a tendancy to do other things while debugging. Like, you notice something in some code as you're looking for problem A. So you change code, then you change what you originally meant to change for debugging, then you test your code, and suddenly, it's different. You unfortunately have tainted your investigation. You don't really know for sure what caused the change. Now, this can be trivial, or sometimes you escape without any damage, but it *can* cause a huge waste of time if your debugging something that turns out to be a very complicated and troublesome. So, I've found it's better to keep notes of the things I notice along the way. I've found I get less distracted from my priorities, too, if I'm methodical. It may seem silly on simple problems, but it's easier to develop as a habit and to practice techniques that work for you on simple problems. Besides, problems are only ever simple in hindsight. You shouldn't really make any assumptions before starting debugging on how difficult the solution will be.

If you don't make a line between debugging and coding, even, the tendancy is to patch an issue that really, if you take a moment to think about it, is a bigger issue. It doesn't mean that it has to be dealt with in it's entirety, but you at least want to document it so it can be fit into the project priorities.

Whew. Can you tell I spend way too much time thinking about debugging? One final note, though. Where this process is highly idiosyncratic is where you develop hypothesis. As I say in my book, we all are prone to make different mistakes habitually. So, it's important to think about what sorts of mistakes _you_ make and that will help you when you develop alternative explanations for a bug.