#34798: Using Django 4.2 with MSSQL 2019 Aggregation Containing Subquery Fails
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* 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>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018a2e021524-21cd0e45-2036-4988-97fd-d45b6a598bdd-000000%40eu-central-1.amazonses.com.
