#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
-------------------------------------+-------------------------------------
Changes (by kmtracey):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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.
New description:
Models in "mars" project:
{{{
#!python
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.
--
Comment:
Wrapped SQL to allow for easier reading.
--
Ticket URL: <https://code.djangoproject.com/ticket/16165#comment:1>
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.
