#16165: Annotations get crazy when .values has many arguments (MySQL)
----------------------------+----------------------------------------------
Reporter: dev@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer (models, ORM)
Version: SVN | Severity: Normal
Keywords: aggregate, | Triage Stage: Unreviewed
annotate | Easy pickings: 0
Has patch: 0 |
----------------------------+----------------------------------------------
Models in "mars" project:
{{{
class BiddingRule(models.Model):
name = models.CharField(max_length=80)
...
class TagList(models.Model):
name = models.CharField(max_length=50)
...
def __unicode__(self):
return self.name
class Property(models.Model):
property = models.CharField(max_length=100)
def __unicode__(self):
return self.property
class Meta:
ordering = ['property']
verbose_name_plural = 'properties'
class Account(models.Model):
"""A property has 1..n accounts"""
username = models.CharField(max_length=200)
password = models.CharField(max_length=200)
ENGINE_CHOICES = (
(1, 'Google'),
(2, 'Yahoo'),
(3, 'Bing'),
)
property = models.ForeignKey(Property)
engine = models.IntegerField(choices=ENGINE_CHOICES)
class Campaign(models.Model):
"""An account has 1..n campaigns"""
campaign = models.CharField(max_length=500)
engine_id = models.BigIntegerField()
account = models.ForeignKey(Account)
STATUS_CHOICES = (
(1, 'Active'),
(2, 'Paused'),
(3, 'Deleted'),
)
status = models.IntegerField(choices=STATUS_CHOICES, default=1)
budget = models.FloatField(default=0.0)
tags = models.ManyToManyField('TagList')
visible_tags = models.CharField(max_length=200, blank=True, null=True)
bidding_rule = models.ForeignKey(BiddingRule, null=True, blank=True)
class Meta:
ordering = ['campaign']
def __unicode__(self):
return self.campaign
class AdGroup(models.Model):
"""A Campaign has 1..n AdGroups"""
ad_group = models.CharField(max_length=500)
engine_id = models.BigIntegerField()
campaign = models.ForeignKey(Campaign)
STATUS_CHOICES = (
(1, 'Active'),
(2, 'Paused'),
(3, 'Deleted'),
(4, 'Draft'),
)
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'))
tags = models.ManyToManyField('TagList')
visible_tags = models.CharField(max_length=200, blank=True, null=True)
bidding_rule = models.ForeignKey(BiddingRule, null=True, blank=True)
class Meta:
ordering = ['ad_group']
class CampaignStat(models.Model):
"""Stats by campaign"""
date = models.DateField()
account = models.ForeignKey(Account)
campaign = models.ForeignKey(Campaign)
impressions = models.IntegerField(default=0)
clicks = models.IntegerField(default=0)
cost = models.DecimalField(max_digits=13, decimal_places=4,
default=Decimal('0'))
conversions = models.IntegerField(default=0)
revenue = models.DecimalField(max_digits=13, decimal_places=4,
default=Decimal('0'))
average_position = models.FloatField(default=0.0)
class AdGroupStat(models.Model):
"""Stats by ad group"""
date = models.DateField()
account = models.ForeignKey(Account)
ad_group = models.ForeignKey(AdGroup)
impressions = models.IntegerField(default=0)
clicks = models.IntegerField(default=0)
cost = models.DecimalField(max_digits=13, decimal_places=4,
default=Decimal('0'))
conversions = models.IntegerField(default=0)
revenue = models.DecimalField(max_digits=13, decimal_places=4,
default=Decimal('0'))
average_position = models.FloatField(default=0.0)
}}}
And here is the crazy annotation issue I am running into. Note the GROUP
BY section of each query.
{{{
>>> from mars.models import *
>>> from django.db.models import Sum
>>> annotations = {
'_impressions': Sum('impressions'),
'_clicks': Sum('clicks'),
'_cost': Sum('cost'),
'_average_position': Avg('average_position'),
'_conversions': Sum('conversions'),
'_revenue': Sum('revenue')
}
>>> ad_group_values = ['ad_group__id',
'ad_group__engine_id',
'ad_group__ad_group',
'ad_group__campaign__account__engine',
'ad_group__campaign__campaign',
'ad_group__max_cpc',
'ad_group__content_max_cpc',
'ad_group__status',
'ad_group__bidding_rule__name',
'ad_group__visible_tags']
>>> stats = AdGroupStat.objects.values(*ad_group_values)
>>> stats = stats.annotate(**annotations)
>>> print(stats.query)
SELECT `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`,
AVG(`mars_adgroupstat`.`average_position`) AS `_average_position`,
SUM(`mars_adgroupstat`.`conversions`) AS `_conversions`,
SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM
`mars_adgroupstat` INNER JOIN `mars_adgroup` ON
(`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN
`mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` =
`mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON
(`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
GROUP BY `mars_adgroupstat`.`id`, `mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`,
`mars_adgroup`.`visible_tags` ORDER BY NULL
>>> stats = AdGroupStat.objects.values(*ad_group_values[:-1])
>>> stats = stats.annotate(**annotations)
>>> print(stats.query)
SELECT `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, AVG(`mars_adgroupstat`.`average_position`) AS
`_average_position`, SUM(`mars_adgroupstat`.`conversions`) AS
`_conversions`, SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM
`mars_adgroupstat` INNER JOIN `mars_adgroup` ON
(`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN
`mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` =
`mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON
(`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
GROUP BY `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, `mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name` ORDER BY NULL
>>> stats = AdGroupStat.objects.values(*ad_group_values[1:])
>>> stats = stats.annotate(**annotations)
>>> print(stats.query)
SELECT `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`,
`mars_adgroup`.`visible_tags`, AVG(`mars_adgroupstat`.`average_position`)
AS `_average_position`, SUM(`mars_adgroupstat`.`conversions`) AS
`_conversions`, SUM(`mars_adgroupstat`.`clicks`) AS `_clicks`,
SUM(`mars_adgroupstat`.`cost`) AS `_cost`,
SUM(`mars_adgroupstat`.`revenue`) AS `_revenue`,
SUM(`mars_adgroupstat`.`impressions`) AS `_impressions` FROM
`mars_adgroupstat` INNER JOIN `mars_adgroup` ON
(`mars_adgroupstat`.`ad_group_id` = `mars_adgroup`.`id`) INNER JOIN
`mars_campaign` ON (`mars_adgroup`.`campaign_id` = `mars_campaign`.`id`)
INNER JOIN `mars_account` ON (`mars_campaign`.`account_id` =
`mars_account`.`id`) LEFT OUTER JOIN `mars_biddingrule` ON
(`mars_adgroup`.`bidding_rule_id` = `mars_biddingrule`.`id`)
GROUP BY `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`,
`mars_adgroup`.`visible_tags`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags` ORDER BY NULL
>>>
}}}
In the first query, using all 10 arguments in .values, the GROUP BY has 11
items as it seems to be adding the local model id:
{{{
GROUP BY `mars_adgroupstat`.`id`, `mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`,
`mars_adgroup`.`visible_tags`
}}}
In the second or third queries, using 9 arguments in .values, the GROUP BY
has 18 items as it seems to be doubling the GROUP BY args:
{{{
GROUP BY `mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`,
`mars_adgroup`.`visible_tags`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, `mars_adgroup`.`visible_tags`
GROUP BY `mars_adgroupstat`.`ad_group_id`, `mars_adgroup`.`engine_id`,
`mars_adgroup`.`ad_group`, `mars_account`.`engine`,
`mars_campaign`.`campaign`, `mars_adgroup`.`max_cpc`,
`mars_adgroup`.`content_max_cpc`, `mars_adgroup`.`status`,
`mars_biddingrule`.`name`, `mars_adgroupstat`.`ad_group_id`,
`mars_adgroup`.`engine_id`, `mars_adgroup`.`ad_group`,
`mars_account`.`engine`, `mars_campaign`.`campaign`,
`mars_adgroup`.`max_cpc`, `mars_adgroup`.`content_max_cpc`,
`mars_adgroup`.`status`, `mars_biddingrule`.`name`
}}}
The first query does not return what I want, while the second and third
return correctly, but look pretty funky. Using MySQL 5.1.41-3ubuntu12.8,
Django SVN 16332, Python 2.6.5 on Ubuntu 10.04.
--
Ticket URL: <https://code.djangoproject.com/ticket/16165>
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.
