Opened 9 days ago

Last modified 26 hours ago

#29416 assigned Bug

Undesired subquery added to the GROUP BY clause

Reported by: Antoine Pinsard Owned by: felixxm
Component: Database layer (models, ORM) Version: 2.0
Severity: Release blocker Keywords: groupby, subquery
Cc: felixxm Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Antoine Pinsard)

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"

Attachments (1)

test_regression_29416.py (1.2 KB) - added by Antoine Pinsard 6 days ago.
tests/annotations/test_regression_29416.py

Download all attachments as: .zip

Change History (12)

comment:1 Changed 9 days ago by Antoine Pinsard

Description: modified (diff)

comment:2 Changed 9 days ago by Antoine Pinsard

Description: modified (diff)

comment:3 Changed 9 days ago by Tim Graham

Can you bisect to find where the behavior changed?

comment:4 Changed 6 days ago by Antoine Pinsard

So, here is the commit that introduced the change of behavior: https://github.com/django/django/commit/1d070d027c218285b66c0bde8079034b33a87f11

Changed 6 days ago by Antoine Pinsard

Attachment: test_regression_29416.py added

tests/annotations/test_regression_29416.py

comment:5 Changed 6 days ago by Tim Graham

Cc: felixxm added

comment:6 Changed 5 days ago by Antoine Pinsard

I think the issue is the getattr(expr, 'alias', None) not in pk_aliases. Maybe it should be getattr(expr, 'alias', None) not in pk_aliases and not isinstance(expr, RawSQL) or something like that?

comment:7 Changed 5 days ago by felixxm

Owner: changed from nobody to felixxm
Status: newassigned

Probably this condition should be more complex. I will dig into it on DjangoConEU sprints.

comment:8 Changed 5 days ago by Antoine Pinsard

OK, FYI I was able to workaround the issue:

>>> nb_reports = RawSQL("SELECT COUNT(*) FROM pro_moderation WHERE objType='sponsor' AND objId=ala_sponsor.sponId AND state=2", [])
>>> nb_reports.alias = 'nb_reports'
>>> nb_reports.target = type('bare', (object,), {'primary_key': True})

So I will be able to upgrade to 2.0 meanwhile.

comment:9 Changed 5 days ago by Tim Graham

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

comment:10 Changed 33 hours ago by felixxm

Has patch: set

comment:11 Changed 26 hours ago by Tim Graham

Triage Stage: AcceptedReady for checkin
Note: See TracTickets for help on using tickets.
Back to Top