id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31133	Annotations crash with Subquery and DurationFields.	Reupen Shah	Simon Charette	"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""
}}}"	Bug	closed	Database layer (models, ORM)	3.0	Release blocker	fixed		Simon Charette Gagaro	Accepted	1	0	0	0	0	0