#28261: django paging after ordering use slicing return wrong result
-------------------------------------+-------------------------------------
Reporter: wqz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle, slicing | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by wqz):
* has_patch: 1 => 0
Old description:
> detail in PR with address https://github.com/django/django/pull/8580
New description:
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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28261#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.7ee8a06b3495e60d67c711c5c48866bd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
