Annotations crash with Subquery and DurationFields.

[Reupen Shah]

Consider the following models:

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:

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:

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:

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"

[Mariusz Felisiak]

Thanks for this report.

Regression in 35431298226165986ad07e91f9d3aca721ff38ec.
Reproduced at 69331bb851c34f05bc77e9fc24020fe6908b9cd5.

[Simon Charette]

FWIW the issue has been around for a while but this only broke on 3.0 because Subquery.as_sql return type changed from Tuple[str, list] to Tuple[str, tuple].

e.g. it would have crashed with a RawSQL('NOW()', ()) annotation as well and all the other ones using tuple as params.

[Simon Charette]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9bcbcd59:

Fixed #31133 -- Fixed crash when subtracting against a subquery annotation.

The subtract_temporals() database operation was not handling expressions
returning SQL params in mixed database types.

Regression in 35431298226165986ad07e91f9d3aca721ff38ec.

Thanks Reupen Shah for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 02cda09b:

[3.0.x] Fixed #31133 -- Fixed crash when subtracting against a subquery annotation.

The subtract_temporals() database operation was not handling expressions
returning SQL params in mixed database types.

Regression in 35431298226165986ad07e91f9d3aca721ff38ec.

Thanks Reupen Shah for the report.

Backport of 9bcbcd599abac91ea853b2fe10b784ba32df043e from master

[Gagaro]

I have the issue on 2.2.11 with the following queryset:

IndicatorValue.objects
.annotate(
    leave_time=Subquery(
        IndicatorValue.objects
            .values('datetime')[:1]
    ),
    loading_time=F('leave_time') - F('datetime'),
)

It works if I apply the fix made for 3.0.

Is a backport possible? I don't think it's a regression as it also doesn't work on 2.2.0.