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?