Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#31217 closed Bug (fixed)

QuerySet.values()/values_list() with ordering by annotations with related fields and aggregations crashes.

Reported by: Mariusz Felisiak Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mariusz Felisiak)

QuerySet.values()/values_list() with ordering by annotations with related fields and aggregations crashes, e.g.

    def test_aggregation_ordered_by_related_annotation_values(self):
        from django.db.models.functions import Coalesce
        self.assertEqual(
            list(Book.objects.annotate(
                min_age=Min('authors__age'),
            ).annotate(
                min_related_age=Coalesce('min_age', 'contact__age'),
            ).order_by('min_related_age').values_list('pk', flat=True)),
            [self.b4.pk, self.b3.pk, self.b1.pk, self.b2.pk, self.b5.pk, self.b6.pk],
        )

crashes with:

django.db.utils.ProgrammingError: column "t4.age" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ER BY COALESCE(COUNT("aggregation_author"."age"), T4."age") ...

Regression in 59b4e99dd00b9c36d56055b889f96885995e4240.

The query before:

....
GROUP BY "aggregation_book"."id", T4."age" ORDER BY COALESCE(MIN("aggregation_author"."age"), T4."age") ASC

and after 59b4e99dd00b9c36d56055b889f96885995e4240

...
GROUP BY "aggregation_book"."id" ORDER BY COALESCE(MIN("aggregation_author"."age"), T4."age") ASC

Thanks Jon Dufresne for the report.

Change History (8)

comment:1 by Mariusz Felisiak, 4 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: new → assigned

comment:2 by Mariusz Felisiak, 4 years ago

Description: modified (diff)

comment:3 by Mariusz Felisiak, 4 years ago

Description: modified (diff)

comment:4 by Mariusz Felisiak, 4 years ago

Has patch: set

​PR

comment:5 by Carlton Gibson, 4 years ago

Triage Stage: Accepted → Ready for checkin

comment:6 by Carlton Gibson, 4 years ago

Triage Stage: Ready for checkin → Accepted

comment:7 by GitHub <noreply@…>, 4 years ago

Resolution: → fixed
Status: assigned → closed

In 6b178a3e:

Fixed #31217 -- Made QuerySet.values()/values_list() group by not selected annotations with aggregations used in order_by().

Regression in 59b4e99dd00b9c36d56055b889f96885995e4240.

Thanks Jon Dufresne for the report and Simon Charette for the review.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 673444da:

[3.0.x] Fixed #31217 -- Made QuerySet.values()/values_list() group by not selected annotations with aggregations used in order_by().

Regression in 59b4e99dd00b9c36d56055b889f96885995e4240.

Thanks Jon Dufresne for the report and Simon Charette for the review.
Backport of 6b178a3e930f72069f3cda2e6a09d1b320fc09ec from master

Note: See TracTickets for help on using tickets.
Back to Top