Hi @pierremonico - it sounds like something is causing your queryset to become randomly ordered (hence the duplicate items across pages). Looking at the code for LimitOffsetPagination, there isn't anything that modifies the queryset beyond the application fo the limit/offset, so my assumption is that issue is caused elsewhere.

I would try logging the sql queries to see if the event query looks sensible.

Make sure your models have a default ordering field or pagination will return seemingly random ordering. This is documented more clearly for CursorPagination but applies to all pagination AFAIK.

class Something(models.Model):
    field_one = models.CharField(max_length=14)
    filed_two = models.TextField()

    class Meta:
        ordering = ['field_one']

If adding 'id' to the ordering then it likely means you have NULL event_date.
NULL values would not be considered identique by PostgresQL but might be returned in any order I think.
Unless we have a reproductible test case we can analyse, there isn't much we can do.

@rpkilby thanks for the logging tip, I'll try that.
@n2ygk thanks, but as stated in my example, I have ordering set. Adding id solves the problem (last paragraph of my description).
@xordoquy that is indeed correct and probably the cause of the problem!

I just also hit this issue, and it's not limited to NULL values. If you order by any set of fields that isn't unique for every record, it's not guaranteed that the results are always in the same order (see e.g. Django or MySQL documentation). This is particularly nasty when rows are moved across a page boundary, as this causes missing or duplicate records when moving between pages.

It's almost impossible to create a reproducible test case for this, as you need to hit exactly the right conditions for your database engine to shuffle rows around between queries. In my case the load on the server seems to influence this (probably related to whether the data remains in the cache between the queries), but there might be other factors as well.

Nevertheless, I think this is at least undesired behaviour and arguably a bug. Especially if you use OrderingFilter, there's no way to be sure that a unique set of fields. I can't (and don't want to) require all clients to always specify a unique field set, so this should be handled serverside.

Proposal to resolve this:

• Document this properly. Currently only CursorPagination has the requirement for a unique ordering field documented, while the order schemes require it as well.
• Add some way to add a "fallback" field(s) that is always added to order_by call (as last field), to ensure that the field set is unique. Most people will probably use the primary key for this, so maybe set that as default value as well.

For those also hitting this problem, you can workaround it by configuring this class in the DEFAULT_FILTER_BACKENDS configuration (instead of rest_framework.filters.OrderingFilter):

from rest_framework.filters import OrderingFilter


# Work around DRF issue #6886 by always adding the primary key as last order field.
class StableOrderingFilter(OrderingFilter):
    def get_ordering(self, request, queryset, view):
        ordering = super(StableOrderingFilter, self).get_ordering(request, queryset, view)
        pk = queryset.model._meta.pk.name

        if ordering is None:
            return ('-' + pk, )

        return list(ordering) + ['-' + pk]

@oxan I am getting null in ordering, shouldn't it always return null as we are overriding the ordering ? As we have a flow where we don't apply order_by in queryset and did it only in CursorPagination by using orderding field

class SomeClassPagination(CustomPagination):
    ordering = "-transacted_at"
    def get_paginated_response(self, data):
        return Response(
            data={
                "records": data,
                "next_cursor": self.get_next_cursor(),
                "previous_cursor": self.get_previous_cursor(),
                "count": len(self.page),
            }
        )

class CustomPagination(CursorPagination)