#31133: Crash with expression annotation involving subquery
-------------------------------------+-------------------------------------
Reporter: Reupen | Owner: nobody
Shah |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Consider the following models:
{{{#!python
class ParentModel(models.Model):
pass
class DatedModel(models.Model):
timestamp = models.DateTimeField()
parent = models.ForeignKey(ParentModel, on_delete=models.CASCADE)
}}}
and the following query set:
{{{#!python
from django.db.models import DurationField, ExpressionWrapper, F
from django.db.models.functions import Now
from django.db.models import Max, OuterRef, Subquery
# importation of models omitted
queryset = ParentModel.objects.annotate(
max_timestamp=Subquery(
DatedModel.objects.annotate(
_annotation=Max('timestamp'),
).filter(
parent_id=OuterRef('pk'),
).values(
'_annotation',
),
),
test_annotation=ExpressionWrapper(
Now() - F('max_timestamp'),
output_field=DurationField(),
),
)
queryset.first()
}}}
With Django 2.2.9, there is no error when evaluating this query set.
With Django 3.0.0 to 3.0.2, and master at
e3d546a1d986f83d8698c32e13afd048b65d06eb, the following error happens:
{{{#!python
Traceback (most recent call last):
File "<input>", line 23, in <module>
File "/project/env/lib/python3.7/site-
packages/django/db/models/query.py", line 664, in first
for obj in (self if self.ordered else self.order_by('pk'))[:1]:
File "/project/env/lib/python3.7/site-
packages/django/db/models/query.py", line 276, in __iter__
self._fetch_all()
File "/project/env/lib/python3.7/site-
packages/django/db/models/query.py", line 1261, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/project/env/lib/python3.7/site-
packages/django/db/models/query.py", line 57, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size)
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 1131, in execute_sql
sql, params = self.as_sql()
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 490, in as_sql
extra_select, order_by, group_by = self.pre_sql_setup()
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 51, in pre_sql_setup
self.setup_query()
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 42, in setup_query
self.select, self.klass_info, self.annotation_col_map =
self.get_select()
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 257, in get_select
sql, params = self.compile(col)
File "/project/env/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 422, in compile
sql, params = node.as_sql(self, self.connection)
File "/project/env/lib/python3.7/site-
packages/django/db/models/expressions.py", line 876, in as_sql
return self.expression.as_sql(compiler, connection)
File "/project/env/lib/python3.7/site-
packages/django/db/models/expressions.py", line 451, in as_sql
return TemporalSubtraction(self.lhs, self.rhs).as_sql(compiler,
connection)
File "/project/env/lib/python3.7/site-
packages/django/db/models/expressions.py", line 512, in as_sql
return
connection.ops.subtract_temporals(self.lhs.output_field.get_internal_type(),
lhs, rhs)
File "/project/env/lib/python3.7/site-
packages/django/db/backends/postgresql/operations.py", line 273, in
subtract_temporals
return super().subtract_temporals(internal_type, lhs, rhs)
File "/project/env/lib/python3.7/site-
packages/django/db/backends/base/operations.py", line 628, in
subtract_temporals
return "(%s - %s)" % (lhs_sql, rhs_sql), lhs_params + rhs_params
TypeError: can only concatenate list (not "tuple") to list
}}}
For reference, Django 2.2.9 executes this when evaluating the entire query
set:
{{{#!sql
SELECT "people_parentmodel"."id",
(
SELECT MAX(U0."timestamp") AS "_annotation"
FROM "people_datedmodel" U0
WHERE U0."parent_id" = ("people_parentmodel"."id")
GROUP BY U0."id"
) AS "max_timestamp",
(STATEMENT_TIMESTAMP() - (SELECT MAX(U0."timestamp") AS
"_annotation"
FROM "people_datedmodel" U0
WHERE U0."parent_id" =
("people_parentmodel"."id")
GROUP BY U0."id")) AS "test_annotation"
FROM "people_parentmodel"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31133>
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/049.35e84cdf38b41d69dc5bb25950375d92%40djangoproject.com.
