django paging after ordering use slicing return wrong result

[wqz]

Background: I want to get the paging use slicing after ordering, if offset=20 and limit=10, code should be :

Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True)

But look:

In [27]: list(Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True))
Out[27]: [1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 3221]
 
In [28]: list(Computer.objects.select_related().order_by('-installed_agent')[10: 20].values_list('id', flat=True))
Out[28]: [1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 3221]

If array[20: 30] and array[10: 20] return the same result,the result of the paging is wrong, why this?

Now django automatically generated sql for "Computer.objects.select_related().order_by('-installed_agent')[20: 30].values_list('id', flat=True)" is:

SELECT *
  FROM (SELECT "_SUB".*, ROWNUM AS "_RN"
          FROM (SELECT "OM_COMPUTER"."ID" AS Col1
                  FROM "OM_COMPUTER"
                 ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB"
         WHERE ROWNUM <= 30)
WHERE "_RN" > 20

If the generate sql like this:

SELECT SUB2.*
  FROM (SELECT SUB1.*, ROWNUM AS "_RN"
          FROM (SELECT "OM_COMPUTER"."ID" AS COL1
                  FROM "OM_COMPUTER"
                 ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) SUB1) SUB2
WHERE "_RN" <= 30
   AND "_RN" > 20

Then the result of the paging is right.

test:

In [3]: list(Computer.objects.select_related().order_by('-installed_agent')[:10].values_list('id', flat=True))
Out[3]: [8148, 7042, 7666, 6027, 6029, 8160, 8161, 8164, 8165, 8171]
 
In [5]: list(Computer.objects.select_related().order_by('-installed_agent')[10 :20].values_list('id', flat=True))
Out[5]: [8173, 8174, 8175, 8176, 8177, 8180, 8181, 8186, 8187, 4397]
 
In [6]: list(Computer.objects.select_related().order_by('-installed_agent')[20 :30].values_list('id', flat=True))
Out[6]: [4408, 4419, 4430, 4441, 4458, 8087, 8090, 8091, 8093, 8096]

In [10]: list(Computer.objects.select_related().order_by('-installed_agent')[10 :].values_list('id', flat=True))
Out[10]: 
[8173,
 8174,
 8175,
...
]

In [7]: str(Computer.objects.select_related().order_by('-installed_agent')[:10].values_list('id', flat=True).query)
Out[7]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" <= 10 AND "_RN" > 0'
 
In [8]: str(Computer.objects.select_related().order_by('-installed_agent')[10 :].values_list('id', flat=True).query)
Out[8]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" > 10'
 
In [9]: str(Computer.objects.select_related().order_by('-installed_agent')[20 :30].values_list('id', flat=True).query)
Out[9]: 'SELECT "__SUB".* FROM (SELECT "_SUB".*,ROWNUM AS "_RN" FROM (SELECT "OM_COMPUTER"."ID" AS Col1 FROM "OM_COMPUTER" ORDER BY "OM_COMPUTER"."INSTALLED_AGENT" DESC) "_SUB") "__SUB" WHERE "_RN" <= 30 AND "_RN" > 20'

works good.
detail in PR with address ​https://github.com/django/django/pull/8580

[Jani Tiainen]

Closing issue due ordering by non-unique field.

Oracle output is non-deterministic in such a case.