Opened 15 years ago
Closed 15 years ago
#13406 closed (invalid)
Aggregate query generating sql error
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.1 |
Severity: | Keywords: | sql, aggregate | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
0 vote down star
I'm using Django 1.1.1 on a SQL Server 2005 db using the latest sqlserver_ado library.
models.py includes:
class Project(models.Model): name = models.CharField(max_length=50) class Thing(models.Model): project = models.ForeignKey(Project) reference = models.CharField(max_length=50) class ThingMonth(models.Model): thing = models.ForeignKey(Thing) timestamp = models.DateTimeField() ThingMonthValue = models.FloatField() class Meta: db_table = u'ThingMonthSummary'
In a view, I have retrieved a queryset called 'things' which contains 25 Things:
things = Thing.objects.select_related().filter(project=1).order_by('reference')
I then want to do an aggregate query to get the average ThingMonthValue for the first 20 of those Things for a certain period, and the same value for the last 5.
For the first 20 I do:
averageThingMonthValue = ThingMonth.objects.filter(thing__in=things[:20],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg']
This works fine, and returns the desired value.
For the last 5 I do:
averageThingMonthValue = ThingMonth.objects.filter(thing__in=things[20:],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg']
But for this I get an SQL error: 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
The SQL query being used by django reads:
SELECT AVG([ThingMonthSummary].[ThingMonthValue]) AS [ThingMonthValue__avg] FROM [ThingMonthSummary] WHERE ([ThingMonthSummary].[thing_id] IN (SELECT _row_num, [id] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [AAAA].[id] ASC) as _row_num, [AAAA].[id] FROM ( SELECT U0.[id] FROM [Thing] U0 WHERE U0.[project_id] = 1 ) AS [AAAA]) as QQQ where 20 < _row_num) AND [ThingMonthSummary].[timestamp] BETWEEN '01/01/09 00:00:00' and '03/01/10 00:00:00')
Change History (3)
comment:1 by , 15 years ago
Keywords: | sql aggregate added |
---|
comment:2 by , 15 years ago
comment:3 by , 15 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
This Trac instance is for bugs in the Django core. Django doesn't officially support MS SQL Server - support for that database is provided by an external backend. In this case, it appears that there is a bug in the backend, so this problem should be reported to the maintainer of the sqlserver_ado backend.
appears '_row_num' is being added unnecessarily to the subquery by the generator