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 28656,Strangely odd behaviour when ordering QuerySet by BooleanField.,Leonardo Arroyo,nobody,"I have recently encountered problems with duplicated results in pagination and trying to pin down the problem this seems to be a problem in the Django queryset. I'll attempt to explain the problem. Take a look at the following Queryset: {{{ >>> p = Project.objects.all().order_by(""closed"") >>> p , , , , , , , , , , , , , , , , , , , , '...(remaining elements truncated)...']> }}} The 'closed' field here is a Boolean. Everything seems to be correct except for this: {{{ >>> p[0] >>> p[1] }}} When I get items from the queryset, they don't match the queryset __repr__ order. What is even weirder is that neither the __repr__ order nor the __getitem__ order matches the PostgreSQL query. {{{ >>> print(p.query) SELECT ""ovp_projects_project"".""id"", ""ovp_projects_project"".""image_id"", ""ovp_projects_project"".""address_id"", ""ovp_projects_project"".""owner_id"", ""ovp_projects_project"".""organization_id"", ""ovp_projects_project"".""name"", ""ovp_projects_project"".""slug"", ""ovp_projects_project"".""published"", ""ovp_projects_project"".""highlighted"", ""ovp_projects_project"".""applied_count"", ""ovp_projects_project"".""max_applies"", ""ovp_projects_project"".""max_applies_from_roles"", ""ovp_projects_project"".""public_project"", ""ovp_projects_project"".""minimum_age"", ""ovp_projects_project"".""hidden_address"", ""ovp_projects_project"".""crowdfunding"", ""ovp_projects_project"".""published_date"", ""ovp_projects_project"".""closed"", ""ovp_projects_project"".""closed_date"", ""ovp_projects_project"".""deleted"", ""ovp_projects_project"".""deleted_date"", ""ovp_projects_project"".""created_date"", ""ovp_projects_project"".""modified_date"", ""ovp_projects_project"".""details"", ""ovp_projects_project"".""description"" FROM ""ovp_projects_project"" ORDER BY ""ovp_projects_project"".""closed"" ASC }}} When hitting the database with such query, only removing a couple of fields to make it easier to read, this is what I get: [[Image(blob:https://imgur.com/c888a1a0-cf27-44f4-b3d5-3a91174eb9ab)]] These are 3 different orders. Now, there's one little thing I can do to fix the QuerySet, which is iterating over it. {{{ >>> for x in p: ... print(x.pk) ... 81 74 75 91 83 61 (...) }}} Suddenly the queryset now matches the result from my PostgreSQL query and everything is now correct. {{{ >>> p , , , , , , , , , , , , , , , , , , , , '...(remaining elements truncated)...']> >>> p[0] >>> p[1] }}} I'm trying to find out where the problem is really happening but I have limited knowledge about django ORM internals, so I haven't gotten that far yet. Currently I'm hacking around it by iterating over the queryset before paginating it and passing it to my view. Things get even weirder if I try to order by 2 boolean fields, with issues such as: {{{ >>> p = Project.objects.all().order_by(""closed"", ""-highlighted"") >>> p , , , , , , , , , , , , , , , , , , , , '...(remaining elements truncated)...']> >>> p[0], p[0].pk (, 81) >>> p[1], p[1].pk (, 81) }}} ",Bug,closed,"Database layer (models, ORM)",1.11,Normal,duplicate,"queryset, boolean, order, ordering",,Unreviewed,0,0,0,0,0,0