#29416: Undesired subquery added to the GROUP BY clause
-------------------------------------+-------------------------------------
Reporter: Antoine Pinsard | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: groupby, subquery | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Antoine Pinsard:
Old description:
> I am facing an issue while upgrading from Django 1.11 to Django 2.0.
>
> I have a complex query interacting with a legacy database, which I
> simplified below to hilight the issue:
>
> {{{
> >>> from user.models import Sponsor
> >>> from django.db.models import ExpressionWrapper, Count, DecimalField
> >>> from django.db.models.expressions import RawSQL
> >>> nb_reports = RawSQL("SELECT COUNT(*) FROM pro_moderation WHERE
> objType='sponsor' AND objId=ala_sponsor.sponId AND state=2", [])
> >>>
> str(Sponsor.objects.all().annotate(report_rate=ExpressionWrapper(nb_reports
> / Count('deliveries'),
> output_field=DecimalField())).order_by('-report_rate').query)
> }}}
>
> This code, in **Django 1.11.9,** gives me the following query:
>
> {{{
> SELECT `ala_sponsor`.`sponId`, [...], ((SELECT COUNT(*) FROM
> pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
> state=2) / COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
> FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
> (`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
> GROUP BY `ala_sponsor`.`sponId` ORDER BY `report_rate` DESC
> }}}
>
> This is the expected behavior and it works well.
>
> However, in **Django 2.0.5**, the same code gives me this query:
>
> {{{
> SELECT `ala_sponsor`.`sponId`, [...], ((SELECT COUNT(*) FROM
> pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
> state=2) / COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
> FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
> (`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
> GROUP BY `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM pro_moderation
> WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2)
> ORDER BY `report_rate` DESC
> }}}
>
> As you can see, the ORM appended the subquery `(SELECT COUNT(*) FROM
> pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
> state=2)` to the GROUP BY clause. Which is wrong, and takes forever to
> execute.
>
> -----
> -----
>
> I tried to play with `.values('id')` or such as I usually do when I get
> unexpected GROUP BY. I spent an afternoon on it but there's no way I
> could get rid of this undesired group by clause. The `order_by` is not to
> blame either. Here is another example of what I tried:
>
> {{{
> str(Sponsor.objects.all().values('id').annotate(report_rate=ExpressionWrapper(nb_reports
> / Count('deliveries'), output_field=DecimalField())).order_by().query)
> }}}
>
> Which gives:
>
> {{{
> SELECT `ala_sponsor`.`sponId`, ((SELECT COUNT(*) FROM pro_moderation
> WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2) /
> COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
> FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
> (`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
> GROUP BY `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM pro_moderation
> WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2)
> ORDER BY NULL
> }}}
>
> Also note that this is the
> `annotate(report_rate=ExpressionWrapper(nb_reports / Count('deliveries'),
> output_field=DecimalField()))` that causes this issue. If I only do
> `annotate(nb_reports=nb_reports)` or
> `annotate(nb_deliveries=COUNT('deliveries'))` there is no additional
> GROUP BY clause generated.
>
> {{{
> In [40]:
> str(Sponsor.objects.all().values('id').annotate(nb_reports=nb_reports).order_by().query)
> Out[40]: "SELECT `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM
> pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
> state=2) AS `nb_reports` FROM `ala_sponsor`"
>
> In [41]:
> str(Sponsor.objects.all().values('id').annotate(nb_deliveries=Count('deliveries')).order_by().query)
> Out[41]: 'SELECT `ala_sponsor`.`sponId`,
> COUNT(`ala_sponsor_need`.`asnId`) AS `nb_deliveries` FROM `ala_sponsor`
> LEFT OUTER JOIN `ala_sponsor_need` ON (`ala_sponsor`.`sponId` =
> `ala_sponsor_need`.`asnSponId`) GROUP BY `ala_sponsor`.`sponId` ORDER BY
> NULL'
>
> In [42]:
> str(Sponsor.objects.all().values('id').annotate(nb_reports=nb_reports,
> nb_deliveries=Count('deliveries')).order_by().query)
> Out[42]: "SELECT `ala_sponsor`.`sponId`,
> COUNT(`ala_sponsor_need`.`asnId`) AS `nb_deliveries`, (SELECT COUNT(*)
> FROM pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId
> AND state=2) AS `nb_reports` FROM `ala_sponsor` LEFT OUTER JOIN
> `ala_sponsor_need` ON (`ala_sponsor`.`sponId` =
> `ala_sponsor_need`.`asnSponId`) GROUP BY `ala_sponsor`.`sponId`, (SELECT
> COUNT(*) FROM pro_moderation WHERE objType='sponsor' AND
> objId=ala_sponsor.sponId AND state=2) ORDER BY NULL"
> }}}
New description:
I am facing an issue while upgrading from Django 1.11 to Django 2.0.
I have a complex query interacting with a legacy MySQL database, which I
simplified below to highlight the issue:
{{{
>>> from user.models import Sponsor
>>> from django.db.models import ExpressionWrapper, Count, DecimalField
>>> from django.db.models.expressions import RawSQL
>>> nb_reports = RawSQL("SELECT COUNT(*) FROM pro_moderation WHERE
objType='sponsor' AND objId=ala_sponsor.sponId AND state=2", [])
>>>
str(Sponsor.objects.all().annotate(report_rate=ExpressionWrapper(nb_reports
/ Count('deliveries'),
output_field=DecimalField())).order_by('-report_rate').query)
}}}
This code, in **Django 1.11.9,** gives me the following query:
{{{
SELECT `ala_sponsor`.`sponId`, [...], ((SELECT COUNT(*) FROM
pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
state=2) / COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
(`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
GROUP BY `ala_sponsor`.`sponId` ORDER BY `report_rate` DESC
}}}
This is the expected behavior and it works well.
However, in **Django 2.0.5**, the same code gives me this query:
{{{
SELECT `ala_sponsor`.`sponId`, [...], ((SELECT COUNT(*) FROM
pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
state=2) / COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
(`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
GROUP BY `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM pro_moderation
WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2)
ORDER BY `report_rate` DESC
}}}
As you can see, the ORM appended the subquery `(SELECT COUNT(*) FROM
pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
state=2)` to the GROUP BY clause. Which is wrong, and takes forever to
execute.
-----
-----
I tried to play with `.values('id')` or such as I usually do when I get
unexpected GROUP BY. I spent an afternoon on it but there's no way I could
get rid of this undesired group by clause. The `order_by` is not to blame
either. Here is another example of what I tried:
{{{
str(Sponsor.objects.all().values('id').annotate(report_rate=ExpressionWrapper(nb_reports
/ Count('deliveries'), output_field=DecimalField())).order_by().query)
}}}
Which gives:
{{{
SELECT `ala_sponsor`.`sponId`, ((SELECT COUNT(*) FROM pro_moderation WHERE
objType='sponsor' AND objId=ala_sponsor.sponId AND state=2) /
COUNT(`ala_sponsor_need`.`asnId`)) AS `report_rate`
FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
(`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`)
GROUP BY `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM pro_moderation
WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2)
ORDER BY NULL
}}}
Also note that this is the
`annotate(report_rate=ExpressionWrapper(nb_reports / Count('deliveries'),
output_field=DecimalField()))` that causes this issue. If I only do
`annotate(nb_reports=nb_reports)` or
`annotate(nb_deliveries=COUNT('deliveries'))` there is no additional GROUP
BY clause generated.
{{{
In [40]:
str(Sponsor.objects.all().values('id').annotate(nb_reports=nb_reports).order_by().query)
Out[40]: "SELECT `ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM
pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND
state=2) AS `nb_reports` FROM `ala_sponsor`"
In [41]:
str(Sponsor.objects.all().values('id').annotate(nb_deliveries=Count('deliveries')).order_by().query)
Out[41]: 'SELECT `ala_sponsor`.`sponId`, COUNT(`ala_sponsor_need`.`asnId`)
AS `nb_deliveries` FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need`
ON (`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`) GROUP BY
`ala_sponsor`.`sponId` ORDER BY NULL'
In [42]:
str(Sponsor.objects.all().values('id').annotate(nb_reports=nb_reports,
nb_deliveries=Count('deliveries')).order_by().query)
Out[42]: "SELECT `ala_sponsor`.`sponId`, COUNT(`ala_sponsor_need`.`asnId`)
AS `nb_deliveries`, (SELECT COUNT(*) FROM pro_moderation WHERE
objType='sponsor' AND objId=ala_sponsor.sponId AND state=2) AS
`nb_reports` FROM `ala_sponsor` LEFT OUTER JOIN `ala_sponsor_need` ON
(`ala_sponsor`.`sponId` = `ala_sponsor_need`.`asnSponId`) GROUP BY
`ala_sponsor`.`sponId`, (SELECT COUNT(*) FROM pro_moderation WHERE
objType='sponsor' AND objId=ala_sponsor.sponId AND state=2) ORDER BY NULL"
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29416#comment:2>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.1b33f71b9af4ed58c72d2d5e5f41d495%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
