Opened 6 years ago

Closed 6 years ago

#13406 closed (invalid)

Aggregate query generating sql error

Reported by: Graeme Hawker <graeme@…> 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: UI/UX:


0 vote down star

I'm using Django 1.1.1 on a SQL Server 2005 db using the latest sqlserver_ado library. 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 Changed 6 years ago by Graeme Hawker <graeme@…>

Keywords: sql aggregate added
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

comment:2 Changed 6 years ago by Graeme Hawker <graeme@…>

appears '_row_num' is being added unnecessarily to the subquery by the generator

comment:3 Changed 6 years ago by Russell Keith-Magee

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top