Opened 10 months ago

Last modified 8 months ago

#34798 closed Bug

Using Django 4.2 with MSSQL 2019 Aggregation Containing Subquery Fails — at Version 1

Reported by: Haldun Komsuoglu Owned by: nobody
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: MSSQL, Aggregation, Subquery
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Haldun Komsuoglu)

Aggregation in a query employing a subquery expression fails with the following error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery. (130) (SQLExecDirectW)')

However, the same query works in Django 4.1.10.

To generate the error for following example can be used:

class Exchange(models.Model):
  date = models.DateField()
  value = models.FloatField()

class Invoice(models.Model):
  date = models.DateField()
  gross = models.FloatFlied()

exchange = Subquery(Exchange.objects.filter(date__lte=OuterRef('date')).order_by('-date').values('value')[:1])

Invoice.objects.annotate(
  exchange=exchange, 
  gross_currency=F('gross') / F('exchange')
).aggregate(
  avg_gross_currency=Avg('gross_currency')
)

Change History (1)

comment:1 by Haldun Komsuoglu, 10 months ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top