>One of the most important debugging aspects I believe in is the notion that it's critical when debugging to debug until one has found the root cause of the bug. There is a lot of pressure in our work places and sometimes in our egos, to patch a system together in order to move on to something else. Tonight I was reminded of the reason it's important to be stubborn when debugging, and how strong the inclination to kludge a solution is. Here's the scenario...
>
>A client reported that a form with a grid was suddenly showing doubled line items. However, when she looked at the tables directly, the data wasn't actually doubled. I first thought of a corrupt CDX. The client uses a UPS, but a clerk had kicked the UPS two days before and the served had rebooted. So, she reindexed. Unfortunately, she reported the line items were still doubling, but only in some instances. She'd found some cases where things were doubled, and some weren't. So, I called in, grabbed their data, and verified the problem. So far so good.
>
>The line items are a result set of a complicated, multi-table/view join. Since joins and I aren't really the best of pals, I assumed that I was having a brain fart over combining LEFT JOINs and INNER JOINs and a couple of WHERE's to hook up 4 tables and a view (itself a complicated one). So, I grabbed SQL_for_Smarties and re-read everthing on JOINs, UNIONs, and NULLs. Still couldn't see what might be wrong, so I stripped down the SQL statements to the minimum. For fun I added DISTINCT in the select and everything was fine.
>
>The mistake I *almost* made was to stuff the DISTINCT in since it fixed the problems, didn't break what wasn't broken (at least not obviously so) and was ready to chalk it up to the ever baffling mysteries of SQL syntax. Finally got the bright idea to look at the least important table in the sub-view my "problem" view used and discovered that there were two blank rows. These blank rows were percolating up to cause my doubling.
>
>Long story short, the problem wasn't corrupt CDX, tho' that was a simple thing to try and worth the effort. The problem wasn't that I didn't understand my SQL syntax (amazingly enough), and the solution was not to add DISTINCT. If I had done that, the symptom would have disappeared, but the underlying cause--a bad, little used data entry form that lets users put in blank records--would have gone merrily on its way causing havoc.
>
>So, ignorance isn't bliss, and neither is a kludge. In my humble opinion.

IMO you've still just fixed the symptom(s), not the cause.

I think the best solution would be to modify the "little used DE form" so blank entries are no longer allowed. (Or have you already done this? Or did you just remove the two blank rows? It's not clear)

The second best one would be to make your code more fault-tolerant so it would not matter if blank entries were present.

The third best one would be a check before you run your SQL, warning the user that blank records exist in the table in question and that the SQL results may not be as expected.