GROUP BY clause incorrect with foreign key to self in MySQL

[Ben Buchwald]

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.

[Josh Smeaton]

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

[Simon Charette]

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.

[Anssi Kääriäinen]

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

[Tim Graham <timograham@…>]

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).

[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