[Django] #34798: Using Django 4.2 with MSSQL 2019 Aggregation Containing Subquery Fails
Django
-------------------------------------+-------------------------------------
Reporter: haldunk | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) | Keywords: MSSQL, Aggregation,
Severity: Normal | Subquery
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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:
{{{
#!python
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')
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34798>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MSSQL, Aggregation, | Triage Stage:
Subquery | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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:
>
> {{{
> #!python
> 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')
> )
> }}}
New description:
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:
{{{
#!python
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')
)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34798#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Haldun Komsuoglu | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: MSSQL, Aggregation, | Triage Stage:
Subquery | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Old description:
> 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)')
>
New description:
gross = models.FloatField()
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')
)
}}}
--
Comment:
Thanks for the report, however I cannot reproduce it using any builtin
backend. You should try to report it to the issue tracker of the 3rd-party
database backend that you're using.
--
Ticket URL: <https://code.djangoproject.com/ticket/34798#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Haldun Komsuoglu | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MSSQL, Aggregation, | Triage Stage:
Subquery | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
This seems to be related to #34551 somehow. The `refs_subquery` check
added in e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4 is not transitive in the
sense that it would work in cases where `exchange` is referenced directly
but not when not through combined expression. I'm surprised that the test
added there doesn't fail if the aggregation is made through another `F`
annotation referring the subquery annotation.
--
Ticket URL: <https://code.djangoproject.com/ticket/34798#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Haldun Komsuoglu | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: MSSQL, Aggregation, | Triage Stage:
Subquery | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Simon Charette (added)
Comment:
As an example the following patch
{{{#!diff
diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py
index ad00afdcc1..63d446196e 100644
--- a/tests/aggregation/tests.py
+++ b/tests/aggregation/tests.py
@@ -2249,11 +2249,12 @@ def
test_referenced_subquery_requires_wrapping(self):
with self.assertNumQueries(1) as ctx:
aggregate = (
Author.objects.annotate(
- total_books=Subquery(total_books_qs.values("total"))
+ total_books=Subquery(total_books_qs.values("total")),
+ total_books_ref=F("total_books") / 1,
)
- .values("pk", "total_books")
+ .values("pk", "total_books_ref")
.aggregate(
- sum_total_books=Sum("total_books"),
+ sum_total_books=Sum("total_books_ref"),
)
)
sql = ctx.captured_queries[0]["sql"].lower()
}}}
Makes the
`aggregation.tests.AggregateAnnotationPruningTests.test_referenced_subquery_requires_wrapping`
test fail because the `total_books_ref` annotation doesn't have a
`.subquery` property. [https://forum.djangoproject.com/t/django-4-2
-behavior-change-when-using-arrayagg-on-unnested-arrayfield-postgresql-
specific/21547/2 What I think is required here] is an
`Expression.returns_set -> bool` method (better name welcome) that is
transitive in the sense that it behaves like `contains_aggregate` does
with regards to nested expressions.
This flag would also have [https://forum.djangoproject.com/t/proposal-add-
generate-series-support-to-contrib-postgres/21947/4 other possible use
cases] but I wonder if it'd be too invasive for a backport.
Here's what it a backportable solution could look like
{{{#!diff
diff --git a/django/db/models/expressions.py
b/django/db/models/expressions.py
index a3d08d4734..40a47c4873 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -256,6 +256,12 @@ def contains_column_references(self):
for expr in self.get_source_expressions()
)
+ @cached_property
+ def contains_subquery(self):
+ return (
+ expr and expr.contains_subquery for expr in
self.get_source_expressions()
+ )
+
def resolve_expression(
self, query=None, allow_joins=True, reuse=None, summarize=False,
for_save=False
):
@@ -1544,6 +1550,7 @@ class Subquery(BaseExpression, Combinable):
contains_aggregate = False
empty_result_set_value = None
subquery = True
+ contains_subquery = True
def __init__(self, queryset, output_field=None, **extra):
# Allow the usage of both QuerySet and sql.Query objects.
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 9853919482..8013fcefe6 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -189,6 +189,7 @@ class Query(BaseExpression):
filter_is_sticky = False
subquery = False
+ contains_subquery = True
# SQL-related attributes.
# Select and related select clauses are expressions to use in the
SELECT
@@ -420,7 +421,7 @@ def get_aggregation(self, using, aggregate_exprs):
# members of `aggregates` to resolve against each others.
self.append_annotation_mask([alias])
refs_subquery |= any(
- getattr(self.annotations[ref], "subquery", False)
+ getattr(self.annotations[ref], "contains_subquery",
False)
for ref in aggregate.get_refs()
)
refs_window |= any(
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34798#comment:4>