[Django] #33382: Different count and len result for a distinct QuerySet

Django

unread,

Dec 22, 2021, 10:44:37 PM12/22/21

to django-...@googlegroups.com

#33382: Different count and len result for a distinct QuerySet
-----------------------------------------+------------------------
Reporter: 826541814 | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
The problem arises when I use the len method on a distinct queryset. The
object obtained by the len method seems to be not distinct , But the query
did not change during this time.

I found what looks like the same Ticket ，But I'm not sure it's the same
reason [https://code.djangoproject.com/ticket/30655]

Here is a simple example that shows the contrary.

Models:

{{{
class Exam(models.Model):
paper = models.ForeignKey(ExamPaper, related_name='paper_exam',
on_delete=models.CASCADE, blank=True, null=True)

class StudentPaper(models.Model):
user = models.ForeignKey(User, related_name='user_exam_paper',
on_delete=models.CASCADE)
exam = models.ForeignKey(Exam, related_name='exam_student_paper',
on_delete=models.CASCADE)
}}}

Shell Output:

{{{
>>> from ExamManage.models import*
>>> exam = Exam.objects.first()
>>> exam.exam_student_paper.filter(is_pass=True).values('user_id').count()
521
>>> support_pass_userids =
exam.exam_student_paper.filter(is_pass=True).values('user_id').distinct()
>>> support_pass_userids.count()
484
>>> print(support_pass_userids.query)
SELECT DISTINCT `ExamManage_studentpaper`.`user_id`,
`ExamManage_studentpaper`.`id` FROM `ExamManage_studentpaper` WHERE
(`ExamManage_studentpaper`.`exam_id` = 5 AND
`ExamManage_studentpaper`.`is_pass`) ORDER BY
`ExamManage_studentpaper`.`id` DESC
>>> len(support_pass_userids)
521
>>> support_pass_userids.count()
521
>>> print(support_pass_userids.query)
SELECT DISTINCT `ExamManage_studentpaper`.`user_id`,
`ExamManage_studentpaper`.`id` FROM `ExamManage_studentpaper` WHERE
(`ExamManage_studentpaper`.`exam_id` = 5 AND
`ExamManage_studentpaper`.`is_pass`) ORDER BY
`ExamManage_studentpaper`.`id` DESC
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33382>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Dec 23, 2021, 4:57:54 AM12/23/21

to django-...@googlegroups.com

#33382: Different count and len result for a distinct QuerySet

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => duplicate
* component: Uncategorized => Database layer (models, ORM)

Comment:

Duplicate of #30655. Please see my
[https://code.djangoproject.com/ticket/30655#comment:2 comment]:

''"`count()` calls `SELECT COUNT(*)` (as described in
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#count
docs]) **without taking ordering into account**,..."''

see also a
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#distinct
note] about using `distinct()` on an ordered queryset.

--
Ticket URL: <https://code.djangoproject.com/ticket/33382#comment:1>

Django

unread,

Dec 23, 2021, 10:08:11 PM12/23/21

to django-...@googlegroups.com

#33382: Different count and len result for a distinct QuerySet

-------------------------------------+-------------------------------------

Comment (by crazy):

Thanks for your patience, now I understand why there difference, because
the `id` field is used for sorting, it interfered with the `distinct`
results. So if count records, should use `count`.

--
Ticket URL: <https://code.djangoproject.com/ticket/33382#comment:2>

Reply all

Reply to author

Forward