Using Django 4.2 with MSSQL 2019 Aggregation Containing Subquery Fails

[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)')

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')
)