id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28261	django paging after ordering use slicing return wrong result	wqz	nobody	"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"	Bug	closed	Database layer (models, ORM)	dev	Normal	invalid	oracle, slicing	287117900@…	Accepted	0	0	0	0	0	0