#24748 closed Bug (fixed)
GROUP BY clause incorrect with foreign key to self in MySQL
Reported by: | Ben Buchwald | Owned by: | Anssi Kääriäinen |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.8 |
Severity: | Release blocker | Keywords: | annotate mysql |
Cc: | josh.smeaton@… | 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
A query I had that previously worked in Django 1.7 is quite broken in 1.8. When annotating a query of a model that has a a foreign key to itself, the wrong field is grouped by. This only occurs in MySQL due a bug to the group by primary key optimization.
Using this example model:
class Item(Model): name = CharField(max_length=50) parent = ForeignKey('self',null=True,blank=True,related_name='children')
I used to be able to get a list of items with their number of children like this:
Item.objects.all().annotate(num_children=Count('children'))
But in Django 1.8, this groups by parent_id instead of by id. This is because both are selected, and the output field for both are the same, the primary key of Item.
Change History (6)
comment:1 by , 9 years ago
Cc: | added |
---|---|
Severity: | Normal → Release blocker |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 9 years ago
I think this commit isn't part of 1.8 and it shouldn't affect the MySQL backend since it has connection.features.allows_group_by_pk = True
anyway.
I remember commenting about a possible issue with the existing logic however. I'm just curious about which change introduced the regression since it looks like it was always broken to me.
comment:3 by , 9 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I think I can take blame for this one. I'll try to work on this today.
comment:4 by , 9 years ago
Has patch: | set |
---|---|
Triage Stage: | Accepted → Ready for checkin |
Just a guess since I haven't tested yet, but perhaps commit dc27f3ee0c3eb9bb17d6cb764788eeaf73a371d7 is a good place to start looking for the regression.