Potential issues with pagination using datetime fields.
alovak opened this issue · 6 comments
Customers Version: v0.5.0-dev
What were you trying to do?
I created a bunch of customer accounts for testing a function that gets the data in pages with following query:
query := `select account_id, encrypted_account_number, created_at from accounts where created_at > ? order by created_at limit 10;`
for each next iteration I use the created_at value of the last row from the previous iteration.
What did you expect to see?
I expected to iterate over all records in the database.
What did you see?
Only one iteration was performed.
Explanation
Currently in MySQL we use created_at without specifying fractional seconds part (e.g. created_at DATETIME(fsp) it uses default value 0 for fsp. Which means following format of date is used: 1000-01-01 00:00:00. It leads to a situation where we may have different rows with the same created_at value (in my test they were created in time less than a second) and so if we sort by date and limit records, we may face issue similar to what I faced.
GORM, by default uses fsp 3: datetime(3). Max is 6.
Also, using precise time still makes it possible (not sure how to measure the possibility) that two records will have equal timestamps.
I faced the issue with pagination when I ordered rows by created_at. Using "xx_id" column solved the issue for me.
Due to support of two databases I was not able to make it work. MySQL requires us to specify fractional seconds part (fsp). At the same time if we specify fsp in SQLite it returns column value as string type not time.Time.
I faced the issue with pagination when I ordered rows by created_at. Using "xx_id" column solved the issue for me.
I've had bugs doing this before. ddf can be inserted unexpectedly when you wanted def
Sorry, what's ddf and def (in a datetime context)?
Oh. They were examples of *_id values. ddf could be inserted between pagination calls and be inserted unexpectedly. created_at values are only ever increasing, so grabbing old results always gives a consistent ordering.