#15624: aggregate produces incorrect sql statement
------------------------------------------+---------------------------
Reporter: zeroos <232002@…> | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: SVN
Keywords: aggregate sql syntax error | Triage Stage: Unreviewed
Has patch: 0 |
------------------------------------------+---------------------------
I have a following model:
{{{
class Solution(models.Model):
griddler = models.ForeignKey(Griddler)
user = models.ForeignKey(User)
user_time = models.IntegerField(null=True)
date = models.DateTimeField(auto_now_add=True)
class Griddler(models.Model):
....
solved_by = models.ManyToManyField(Uesr, through='Solution')
....
}}}
What I am trying to do is to find the Griddler that has the biggest
avg user time. I've written the following query:
Solution.objects.values('griddler').annotate(a=Avg('user_time')).aggregate(
Max('a'))
Unfortunately, after executing it I get
DatabaseError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'FROM (SELECT `griddlers_solution`.`griddler_id` AS
`griddler_id`, AVG(`griddlers' at line 1")
{{{
>>> connection.queries[-1]
{'time': '0.000', 'sql': u'SELECT FROM (SELECT
`griddlers_solution`.`griddler_id` AS `griddler_id`,
AVG(`griddlers_solution`.`user_time`) AS `a` FROM `griddlers_solution`
GROUP BY `griddlers_solution`.`griddler_id`,
`griddlers_solution`.`griddler_id` ORDER BY NULL) subquery'}
}}}
The problem is that Max('a') is skipped in the sql query. Instead of :
{{{
SELECT FROM (SELECT ...
}}}
It should look like this:
{{{
SELECT Max(`a`) FROM (SELECT...
}}}
I am using Django from SVN (pulled today, 16.03.11) and MySQL backend.
{{{
>>> django.get_version()
'1.3 beta 1'
}}}
--
Ticket URL: <http://code.djangoproject.com/ticket/15624>
Django <http://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.
