skip to content
blog.metters.dev
Table of Contents

Chasing the bug

Some time ago, I had to analyse a bug, which manifested in weird non-deterministic behaviour: There were duplicates in the paginated entries of a table in the frontend - but not always. They only started showing up after the first page and never on the same page! This unpredictability made the root cause especially tricky to figure out.

Frontend

The bug ticket was marked as a frontend-bug, but a look in the developer tools showed that the frontend was not to blame for this. It simply displayed what was handed over after each query.

Time to inspect the other suspects:

Is it in the database?

No. I could not find any issues with the data in the database. The query contained joins, which I suspected. Splitting the query into separate queries did not provide any insights, so this suspicion proved false.

With a looming deadline (Go-Live of the application) on the horizon, pressure to resolve the bug slightly increased.

*slightly sweating*
Backend/JPA

After talking with a colleague about my analysis, I took another look at the query - the sort order in particular. The code seemed correct. However, the sort order caught my eye: all results were ordered by two timestamps: approvalDateTime and creationDateTime.

Back to the database

So, I queried the table for those two columns again and realised all those duplicates share some characteristics:

  1. approvalDateTime was null for all entries
  2. creationDateTime was exactly same value for all entries of the table

Why would the creation date time be equal for so many entries? A while ago, there was a migration from an old database and back then new entries were stored without creation timestamp. The other timestamp also did not exist back then. So, those entries received arbitrary values: the date and time of the migration for the creation timestamp and null for the other one, because it was not a mandatory field.

When querying the database for a page of 10 entries, too many entities competed for the same spot, because they were identical with regard to the requested sort order. They could have shown up on page one up to page 10.

Fixing the bug

A deterministic sort order would solve all of this. One way would be to give all migrated entities a unique creationDateTime. A much better solution was additionally sorting the results by another column that always contained a unique result, e.g. the primary key (which was a uuid).

Lessons learnt?

  • Make sure sorted columns are always unique; or contain at least one column with a value guaranteed to be unique
  • The bug wasn’t clearly a frontend, backend, or database issue. It originated from two root causes:
    1. The query bridging the backend and database ➡️ Look between layers!
    2. Assumptions about data uniqueness: I was so certain it was unique that I never verified it.