[Django] #24748: GROUP BY clause incorrect with foreign key to self in MySQL
Django
----------------------------------------------+----------------------------
Reporter: sparkyb | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: annotate mysql
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24748>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: sparkyb | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: annotate mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* severity: Normal => Release blocker
* cc: josh.smeaton@… (added)
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
* stage: Unreviewed => Accepted
Comment:
Just a guess since I haven't tested yet, but perhaps commit
dc27f3ee0c3eb9bb17d6cb764788eeaf73a371d7 is a good place to start looking
for the regression.
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:1>
Django
Reporter: sparkyb | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: annotate mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
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
[https://github.com/django/django/pull/4397#issuecomment-86720671
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:2>
Django
Reporter: sparkyb | Owner: akaariai
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: annotate mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by akaariai):
* owner: nobody => akaariai
* status: new => assigned
Comment:
I think I can take blame for this one. I'll try to work on this today.
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:3>
Django
Reporter: sparkyb | Owner: akaariai
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Release blocker | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* has_patch: 0 => 1
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:4>
Django
Reporter: sparkyb | Owner: akaariai
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: annotate mysql | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"adc57632bc26cc8fe42bdb6aff463f883214980a" adc57632]:
{{{
#!CommitTicketReference repository=""
revision="adc57632bc26cc8fe42bdb6aff463f883214980a"
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).
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:5>
Django
Reporter: sparkyb | Owner: akaariai
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: annotate mysql | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"d5ce2dd7bc5b797d2e338c4bc6e6f3e339b748e5" d5ce2dd7]:
{{{
#!CommitTicketReference repository=""
revision="d5ce2dd7bc5b797d2e338c4bc6e6f3e339b748e5"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24748#comment:6>