JPA duplicates lessons learnt TBD

The bug

Some time ago, I had to analyse some weird behaviour: The paginated entries in the table of the frontend had duplicates. There were no duplicates in the database, but the query contained joins, which might result in the duplicated results. So, my first attempt was to fix the query, by splitting it up into two separate queries: Neither the query for the uuids of the entities, nor the query for their data contained any duplicates.

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

After talking to a colleague about the issue, I took another look at the query - the sort order in particular. All results were ordered by two timestamps: approvalDateTime and creationDateTime

I queried the table again for those two columns and realised all those duplicates share some characteristics: the first timestamp was null and the other one had the exact same value. This happened because all those entities were migrated from the old application/database.

The non-deterministic behaviour made this especially tricky to figure out. Also, the duplicates only started showing up after the first page.

The bugfix

To resolve this issue, I needed to make the sort order deterministic. One way to do that, was by giving all migrated entities a unique creationDateTime, but a better way to achieve this was sorting the results by another column that always contained a unique result, e.g. the primary key.

Lessons learnt?

• Make sure sorted columns are always unique or contain at least one column with always unique value
• Bug did not feel like a frontend or a backend or a database bug. It was the query that connected the backend and the database. “Look between layers”.