Unexpected results using order_by with multiple fields

[Mauro Crociara]

I'm facing unexpected results when I switch fields -planning_rsc and task_type_two in order_by clause.

Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('-planning_rsc', 'task_type_two')

produces

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:

Task.objects.filter(candidate=task.candidate, planning_rsc__isnull=False).only('pk').order_by('task_type_two', '-planning_rsc')

I get:

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:

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

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:

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?

Using base_manager works to:

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')

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

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)

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

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

Task.objects.filter(candidate=candidate, planning_rsc__isnull=False).values_list('pk').order_by('-planning_rsc', 'task_type_two')

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

[Simon Charette]

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.