Our team have been working on a report. It’s a fairly simple report driven by a SQL stored procedure that collates a list of accounts, and the customer(s) that hold them. Every account will have at least one customer but could have up to four customers associated with it.

Our test approach was fairly light-weight; make sure the report contains the number of rows we expect and then dip-check some rows at random* to make sure the content written out is correct.

*random samples not entirely random, but from identifying different classifications of test data and checking a few of each.

Some years ago I attended a talk by Michael Bolton (no, not the famous singer) on testing ‘deep vs shallow‘. He described an approach to risk-based testing where you cast your test scope as broadly as possible but with shallow touches and then dive deeper on any areas of higher risk identified through error-guessing, specific areas of risk to the business/your stakeholder and customers or from spotting issues in your shallow testing that need more thorough investigation. It was an incredibly energising talk that amongst other things allowed me to put words to the risk based testing approach that I had been using for a good number of years knowing that software projects rarely have the time, budget or will for exhaustive testing.

Anyway our sample checks on the per-row content were fine, but our record counts were not. From what we had predicted we were expecting far more rows than were actually being written out in the report, which is a fairly fundamental problem to the user story.

So we took a deeper look, why weren’t the counts marrying up? First we checked the SQL query we were using to assert the number of rows to be returned, and had to check the query we were using against the requirements of the user story. Did we have our filtering criteria correct? Were there statuses or soft deletes that we hadn’t properly considered? Was there a type of account or customer that should have been included that we had excluded? We made a couple of subtle revisions to our test query and the discrepancy came down, but the difference still wasn’t 0. Someone asked me “is a <1% discrepancy OK? Can we sign this off?” but in our operating environment an unexplained discrepancy of any amount wasn’t acceptable.

Confident that the requirements driving our query matched those in the story, we looked at the stored procedure itself to see if that was filtering anything that we weren’t (correctly or not), but there was no observable difference.

The next step in our troubleshooting was to look at the data itself. Compare the accounts written out from our query to those from the SQL stored proc, and that’s when the penny dropped for me.

I kicked myself, hard, because I realised a PICNIC error and burnt at least an afternoon on it than I wish I hadn’t.

I’m sure some of you reading this will have already sussed it given clues from the title of the blog and the requirements I’ve explained.

But yes, when I compared the data in the report to the detail underneath my checking query I realised that some accounts were shown more than once. I had realised that my count(*) was missing the ‘distinct’ SQL operator. Thanks to the join in my query to return all the customer details where there was more than one customer per account then the account was being counted [customer] times in my SQL, whereas in the report the account was only being written out once as per requirements.

Or in simpler terms, my test assertion query was using

select count(accountNumber)

where it should have used

select count distinct(accountNumber)

and boy, did I feel like a dunce.

But why have I bothered writing out a long-ass blog for such a simple mistake? There are two major reflections that I want to share or reinforce:

  1. Not all ‘defects’ are software faults. If a defect is considered an unexpected outcome, this is a great example as we expected a certain count of rows but received a different one. The issue wasn’t due to the software under test (or the SQL stored procedure in our case) but due to the test expectation. A test defect is just as valid as a software or environmental defect and needs to be highlighted for future learning. After all we don’t raise defect work items for shits and giggles, but as a future reference and learning item.
  2. To err is human; mistakes are how we learn. I kicked myself for missing the ‘distinct’ condition but I won’t make that mistake again (any time soon, anyway). Following on from point 1, talking about that mistake and highlighting it helps others to learn so that if they’re facing similar inconsistencies it gives them an avenue to consider. I’m lucky that I feel a high level of psychological safety in my role and I hope that making my “silly mistakes” visible will help enable others to discuss their doubts or mistakes.

If you were in my shoes, would you have discussed this error among your team or publicly? When was the last time you kicked yourself for what you think was a silly mistake? Did you let anyone know, or keep it to yourself and carry on?

If you’ve taken anything from this blog or have any disagreements chuck them in the comments, or if you find yourself agreeing with the sentiment or nodding along feel free to share the post with your local QA or development community