[Django] #31133: Crash with expression annotation involving subquery

Django

unread,

Jan 2, 2020, 6:15:05 AM1/2/20

to django-...@googlegroups.com

{{{#!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.

Django

unread,

Jan 2, 2020, 6:43:31 AM1/2/20

to django-...@googlegroups.com

#31133: Annotations crash with Subquery and DurationFields.
-------------------------------------+-------------------------------------
Reporter: Reupen Shah | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* cc: Simon Charette (added)
* type: Uncategorized => Bug
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

Comment:

Thanks for this report.

Regression in 35431298226165986ad07e91f9d3aca721ff38ec.
Reproduced at 69331bb851c34f05bc77e9fc24020fe6908b9cd5.

--
Ticket URL: <https://code.djangoproject.com/ticket/31133#comment:1>

Django

unread,

Jan 2, 2020, 10:39:00 AM1/2/20

to django-...@googlegroups.com

#31133: Annotations crash with Subquery and DurationFields.
-------------------------------------+-------------------------------------

Reporter: Reupen Shah | Owner: Simon
| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => assigned
* owner: nobody => Simon Charette

Comment:

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`.

--
Ticket URL: <https://code.djangoproject.com/ticket/31133#comment:2>

Django

unread,

Jan 2, 2020, 11:38:41 AM1/2/20

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/31133#comment:3>

Django

unread,

Jan 3, 2020, 4:35:44 AM1/3/20

to django-...@googlegroups.com

#31133: Annotations crash with Subquery and DurationFields.
-------------------------------------+-------------------------------------
Reporter: Reupen Shah | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 3.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"9bcbcd599abac91ea853b2fe10b784ba32df043e" 9bcbcd59]:
{{{
#!CommitTicketReference repository=""
revision="9bcbcd599abac91ea853b2fe10b784ba32df043e"
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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31133#comment:4>

Django

unread,

Jan 3, 2020, 4:36:11 AM1/3/20

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"02cda09b13e677db01863fa0a7112dba631b9c5c" 02cda09b]:
{{{
#!CommitTicketReference repository=""
revision="02cda09b13e677db01863fa0a7112dba631b9c5c"
[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
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31133#comment:5>

Django

unread,

Mar 31, 2020, 10:57:23 AM3/31/20

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------