[Django] #33176: Unexpected results using order_by with multiple fields
Django
-------------------------------------+-------------------------------------
Reporter: Mauro | Owner: nobody
Crociara |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: order, order_by,
Severity: Normal | OrderBy, ordering
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I'm facing unexpected results when I switch fields `-planning_rsc` and
`task_type_two` in **order_by** clause.
{{{#!python
Task.objects.filter(candidate=task.candidate,
planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
'-task_type_two')
}}}
produces
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id"
ORDER BY "candidates_tasks"."planning_rsc" DESC
}}}
But doing this:
{{{#!python
Task.objects.filter(candidate=task.candidate,
planning_rsc__isnull=False).only('pk').order_by('task_type_two',
'-planning_rsc')
}}}
I get:
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id",
CASE
WHEN "candidates_tasks"."task_type_two" =
01_site_implementation THEN 01 - Site Implementation
WHEN "candidates_tasks"."task_type_two" =
02_technology_upgrade THEN 02 - Technology Upgrade
WHEN "candidates_tasks"."task_type_two" =
03_configuration_change THEN 03 - Configuration Change
WHEN "candidates_tasks"."task_type_two" = 98_relocation THEN
98 - Relocation
WHEN "candidates_tasks"."task_type_two" = 99_dismission THEN
99 - Dismission
ELSE NULL END
ORDER BY CASE
WHEN "candidates_tasks"."task_type_two" =
01_site_implementation THEN 01 - Site Implementation
WHEN "candidates_tasks"."task_type_two" =
02_technology_upgrade THEN 02 - Technology Upgrade
WHEN "candidates_tasks"."task_type_two" =
03_configuration_change THEN 03 - Configuration Change
WHEN "candidates_tasks"."task_type_two" = 98_relocation THEN
98 - Relocation
WHEN "candidates_tasks"."task_type_two" = 99_dismission THEN
99 - Dismission
ELSE NULL END ASC, "candidates_tasks"."planning_rsc" DESC
}}}
As you can see, both `task_type_two` and `planning_rsc` have been included
in **order_by** clause. To solve the problem, I had to use **OrderBy**
expression this way:
{{{#!python
Task.objects.filter(candidate=task.candidate,
planning_rsc__isnull=False).only('pk').order_by(OrderBy(F('planning_rsc'),
descending=True), OrderBy(F('task_type_two')))
}}}
Which produces the desired result
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."planning_rsc" DESC,
"candidates_tasks"."task_type_two" ASC
}}}
The fields `planning_rsc` and `task_type_two` are defined in model like
here:
{{{#!python
task_type_two = models.CharField(
verbose_name=_("Task type"), choices=choices.TASK_TYPE_CHOICES,
max_length=64, blank=True,
help_text=_("The type of the task"))
planning_rsc = models.DateField(
_('Planning RSC Date'), null=True, blank=True, help_text='The
Planning RSC Date of the task')
}}}
I'm doing something wrong?
--
Ticket URL: <https://code.djangoproject.com/ticket/33176>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: order, order_by, | Triage Stage:
OrderBy, ordering | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
I'm facing unexpected results when I switch fields `-planning_rsc` and
`task_type_two` in **order_by** clause.
{{{#!python
Task.objects.filter(candidate=task.candidate,
planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
'task_type_two')
}}}
produces
But doing this:
I get:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33176#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: order, order_by, | Triage Stage:
OrderBy, ordering | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> I'm facing unexpected results when I switch fields `-planning_rsc` and
> `task_type_two` in **order_by** clause.
>
> {{{#!python
> Task.objects.filter(candidate=task.candidate,
> planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
> 'task_type_two')
> }}}
>
> produces
>
New description:
I'm facing unexpected results when I switch fields `-planning_rsc` and
`task_type_two` in **order_by** clause.
{{{#!python
Task.objects.filter(candidate=task.candidate,
planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
'task_type_two')
}}}
produces
But doing this:
I get:
Using base_manager works to:
{{{#!python
Task._meta.base_manager.select_related(None).prefetch_related(None).filter(candidate=candidate,
planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
'task_type_two')
}}}
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."planning_rsc" DESC,
"candidates_tasks"."task_type_two" ASC
}}}
And
{{{#!python
Task._meta.base_manager.select_related(None).prefetch_related(None).filter(candidate=candidate,
planning_rsc__isnull=False).only('pk').order_by('task_type_two',
'-planning_rsc').query)
}}}
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
ORDER BY "candidates_tasks"."task_type_two" ASC,
"candidates_tasks"."planning_rsc" DESC
}}}
I cannot use **only** with **object**
{{{#!python
Task.objects.filter(candidate=candidate,
planning_rsc__isnull=False).only('pk').order_by('-planning_rsc',
'task_type_two')
}}}
>django.core.exceptions.FieldError: Field Task.candidate cannot be both
deferred and traversed using select_related at the same time.
Then try with **values_list**
{{{#!python
Task.objects.filter(candidate=candidate,
planning_rsc__isnull=False).values_list('pk').order_by('-planning_rsc',
'task_type_two')
}}}
{{{#!sql
SELECT "candidates_tasks"."id"
FROM "candidates_tasks"
LEFT OUTER JOIN "candidates_technologiestwo"
ON ("candidates_tasks"."id" =
"candidates_technologiestwo"."task_id")
INNER JOIN "candidates_candidates"
ON ("candidates_tasks"."candidate_id" =
"candidates_candidates"."location_ptr_id")
LEFT OUTER JOIN "iliad_operators" ON
("candidates_candidates"."landlord_operator_id" = "iliad_operators"."id")
LEFT OUTER JOIN "candidates_tower_companies"
ON ("candidates_candidates"."tower_company_id" =
"candidates_tower_companies"."id")
INNER JOIN "iliad_locations" ON
("candidates_candidates"."location_ptr_id" = "iliad_locations"."id")
WHERE ("candidates_tasks"."candidate_id" = 77677 AND
"candidates_tasks"."planning_rsc" IS NOT NULL)
GROUP BY "candidates_tasks"."id"
ORDER BY "candidates_tasks"."planning_rsc" DESC
}}}
`task_type_two` is missing in **order_by** clause
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33176#comment:2>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: order, order_by, | Triage Stage:
OrderBy, ordering | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Thank you for your report but it's impossible for triagers to determine
whether or not Django or your project is at fault here since you didn't
provide your models or managers code.
From a cursory look at your ORM interactions and provided generated SQL
there's clearly a few missing points (`GROUP BY` clause without any
aggregation, `ORDER BY` missing field) and the fact things ''work'' when
using `OrderBy` instead of field references makes me believe you have a
base manager doing some form `annotate` or `alias` that are not
represented here.
Please provide a simpified sample project demonstrating the unexpected
behavior where Django might be at fault and re-open this ticket with a
reference to it.
--
Ticket URL: <https://code.djangoproject.com/ticket/33176#comment:3>