Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#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 Changed 3 years ago by Josh Smeaton

Cc: josh.smeaton@… added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Just a guess since I haven't tested yet, but perhaps commit dc27f3ee0c3eb9bb17d6cb764788eeaf73a371d7 is a good place to start looking for the regression.

comment:2 Changed 3 years ago by Simon Charette

I think this commit isn't part of 1.8 and it shouldn't affect the MySQL backend since it has 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.

Last edited 3 years ago by Simon Charette (previous) (diff)

comment:3 Changed 3 years ago by Anssi Kääriäinen

Owner: changed from nobody to Anssi Kääriäinen
Status: newassigned

I think I can take blame for this one. I'll try to work on this today.

comment:4 Changed 3 years ago by Simon Charette

Has patch: set
Triage Stage: AcceptedReady for checkin

comment:5 Changed 3 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In adc57632:

Fixed #24748 -- Fixed incorrect GROUP BY on MySQL in some queries

When the query's model had a self-referential foreign key, the
compiler.get_group_by() code incorrectly used the self-referential
foreign key's column (for example parent_id) as GROUP BY clause
when it should have used the model's primary key column (id).

comment:6 Changed 3 years ago by Tim Graham <timograham@…>

In d5ce2dd7:

[1.8.x] Fixed #24748 -- Fixed incorrect GROUP BY on MySQL in some queries

When the query's model had a self-referential foreign key, the
compiler.get_group_by() code incorrectly used the self-referential
foreign key's column (for example parent_id) as GROUP BY clause
when it should have used the model's primary key column (id).

Backport of adc57632bc26cc8fe42bdb6aff463f883214980a from master

Note: See TracTickets for help on using tickets.
Back to Top