#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 django-updates+unsubscr...@googlegroups.com.
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.
