[Django] #31217: QuerySet.values()/values_list() with ordering by annotations with related fields and aggregations crashes.

Django

unread,

Jan 30, 2020, 1:29:39 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
`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(
max_age=Count('authors__age'),
).annotate(
max_related_age=Coalesce('max_age', 'contact__age'),
).order_by('max_related_age').values_list('pk', flat=True)),
[self.b6.pk, self.b3.pk, self.b2.pk, self.b1.pk, self.b5.pk,
self.b4.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.

Thanks Jon Dufresne for the report.

--
Ticket URL: <https://code.djangoproject.com/ticket/31217>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 30, 2020, 1:35:23 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* owner: nobody => felixxm
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:1>

Django

unread,

Jan 30, 2020, 1:43:15 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> `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(
> max_age=Count('authors__age'),
> ).annotate(
> max_related_age=Coalesce('max_age', 'contact__age'),
> ).order_by('max_related_age').values_list('pk', flat=True)),
> [self.b6.pk, self.b3.pk, self.b2.pk, self.b1.pk, self.b5.pk,
> self.b4.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.
>
> Thanks Jon Dufresne for the report.

New description:

`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.

Thanks Jon Dufresne for the report.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:2>

Django

unread,

Jan 30, 2020, 1:51:02 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by felixxm:

Old description:

> `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.
>
> Thanks Jon Dufresne for the report.

New description:

`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.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:3>

Django

unread,

Jan 30, 2020, 2:53:11 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/12388 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:4>

Django

unread,

Jan 30, 2020, 6:16:19 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:5>

Django

unread,

Jan 30, 2020, 8:36:17 AM1/30/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:6>

Django

unread,

Feb 3, 2020, 1:48:54 AM2/3/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Reporter: felixxm | Owner: felixxm
Type: Bug | Status: closed

Component: Database layer | Version: 3.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"6b178a3e930f72069f3cda2e6a09d1b320fc09ec" 6b178a3e]:
{{{
#!CommitTicketReference repository=""
revision="6b178a3e930f72069f3cda2e6a09d1b320fc09ec"
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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:7>

Django

unread,

Feb 3, 2020, 1:49:26 AM2/3/20

to django-...@googlegroups.com

#31217: QuerySet.values()/values_list() with ordering by annotations with related
fields and aggregations crashes.
-------------------------------------+-------------------------------------

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"673444da5e721c0d23809076c5d5d74224457d23" 673444da]:
{{{
#!CommitTicketReference repository=""
revision="673444da5e721c0d23809076c5d5d74224457d23"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31217#comment:8>

Reply all

Reply to author

Forward