#16165: Annotations get crazy when .values has many arguments (MySQL)
-------------------------------------+-------------------------------------
Reporter: dev@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: SVN | (models, ORM)
Resolution: | Severity: Normal
Triage Stage: | Keywords: aggregate, annotate
Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
-------------------------------------+-------------------------------------
Comment (by anonymous):
Simplified:
Models:
{{{
#!python
class Account(models.Model):
username = models.CharField(max_length=200)
password = models.CharField(max_length=200)
engine = models.IntegerField(choices=ENGINE_CHOICES)
class AdGroup(models.Model):
ad_group = models.CharField(max_length=500)
engine_id = models.BigIntegerField()
status = models.IntegerField(choices=STATUS_CHOICES, default=1)
max_cpc = models.DecimalField(max_digits=7, decimal_places=4,
default=Decimal('0'))
content_max_cpc = models.DecimalField(max_digits=7, decimal_places=4,
default=Decimal('0'))
class Meta:
ordering = ['ad_group']
class AdGroupStat(models.Model):
date = models.DateField()
account = models.ForeignKey(Account)
ad_group = models.ForeignKey(AdGroup)
impressions = models.IntegerField(default=0)
}}}
And here is the crazy annotation issue I am running into:
{{{
>>> from mars.models import *
>>> from django.db.models import Sum
>>> ad_group_values = [
'account__engine',
'account__username',
'account__password',
'ad_group__id',
'ad_group__engine_id',
'ad_group__ad_group',
'ad_group__max_cpc',
'ad_group__content_max_cpc',
'ad_group__status'
]
>>> s = AdGroupStat.objects.values(*ad_group_values)
>>> s = s.annotate(Sum('impressions'))
>>> print(s.query)
SELECT
`mars_account`.`engine`,
`mars_account`.`username`,
`mars_account`.`password`,
`mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`,
`mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`,
SUM(`mars_adgroupstat`.`impressions`) AS `impressions__sum`
FROM
`mars_adgroupstat`
INNER JOIN
`mars_account` ON (`mars_adgroupstat`.`account_id` =
`mars_account`.`id`)
INNER JOIN
`mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` =
`mars_adgroup`.`id`)
GROUP BY
`mars_account`.`engine`,
`mars_account`.`username`,
`mars_account`.`password`,
`mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`,
`mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`
ORDER BY NULL
>>> ad_group_values.append('account__id')
>>> s = AdGroupStat.objects.values(*ad_group_values)
>>> s = s.annotate(Sum('impressions'))
>>> print(s.query)
SELECT
`mars_account`.`engine`,
`mars_account`.`username`,
`mars_account`.`password`,
`mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`,
`mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`,
`mars_adgroupstat`.`account_id`,
SUM(`mars_adgroupstat`.`impressions`) AS `impressions__sum`
FROM
`mars_adgroupstat`
INNER JOIN
`mars_account` ON (`mars_adgroupstat`.`account_id` =
`mars_account`.`id`)
INNER JOIN
`mars_adgroup` ON (`mars_adgroupstat`.`ad_group_id` =
`mars_adgroup`.`id`)
GROUP BY
`mars_adgroupstat`.`id`,
`mars_account`.`engine`,
`mars_account`.`username`,
`mars_account`.`password`,
`mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`,
`mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`,
`mars_adgroupstat`.`account_id`
ORDER BY NULL
}}}
Note the extra mars_adgroupstat.id in the GROUP BY of the second query.
--
Ticket URL: <https://code.djangoproject.com/ticket/16165#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
