#34262: Queryset grouped by annotation with aggregates on another annotated
expression crashes on MySQL with sql_mode=only_full_group_by.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: Jonny
| Park
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
only_full_group_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jonny Park):
I think @David Wobrock's query is easier to implement and covers more
cases.
For example, if we have a followinfg queryset:
{{{
books_qs = (
Book.objects.annotate(greatest_pages=Greatest("pages",
Value(600)))
.values(
"greatest_pages",
)
.annotate(
min_pages=Min("pages"),
least=Least("min_pages", "greatest_pages"),
)
)
}}}
Creating the following query that @David Wobrock presented seems like more
sense to me and covers many other cases.
{{{
SELECT greatest_pages,
MIN(pages),
LEAST(MIN(pages), greatest_pages) AS least
FROM (SELECT GREATEST(pages, 600) greatest_pages,
pages
FROM aggregation_book2) AS t
GROUP BY greatest_pages
ORDER BY NULL;
}}}
If we were to take @Simon Charette's query, it could be like this:
{{{
SELECT geatest_pages, min_pages, LEAST(min_pages, greatest_pages) AS
`least` FROM (
SELECT
GREATEST(`aggregation_book`.`pages`, 600) greatest_pages,
MIN(`aggregation_book`.`pages`) min_pages
FROM `aggregation_book`
GROUP BY 1 ORDER BY NULL
) masked
}}}
I think the position of "MIN(`aggregation_book`.`pages`) min_pages" look
award for me.
with `.values_list("least", flat=True)` clause present, there was a
obvious reason for "MIN(`aggregation_book`.`pages`) min_pages" to be
pushed down because it is a dependency for `least`, but without
`.values_list("least", flat=True)` it loses it's reason to be pushed down.
I am a bit suspicious that choosing which additional item to be pushed
down by looking at `values_list` worth it's effort considering frequency
of this use case is thought to be small.
--
Ticket URL: <https://code.djangoproject.com/ticket/34262#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070189e90415e0-a1db624c-a83a-42dc-a16c-a5eb45239933-000000%40eu-central-1.amazonses.com.
