[Django] #31977: Transforms silently don't work in F and OuterRef expressions.

Django

unread,

Sep 2, 2020, 11:59:15 AM9/2/20

to django-...@googlegroups.com

#31977: Transforms silently don't work in F and OuterRef expressions.
-------------------------------------+-------------------------------------
Reporter: Gordon | Owner: nobody
Wrigley |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: function, lookup,
Severity: Normal | transform, F, OuterRef
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I've seen this on different databases and different Django versions.

Here's Sqlite with Django 3.1.1

{{{#!python
>>> qs =
Order.objects.filter(submitted_time__date=F("submitted_time__date"))
>>> qs
<QuerySet []>
>>> print(qs.query)
SELECT <REDACTED> FROM "core_order" WHERE
django_datetime_cast_date("core_order"."submitted_time", 'UTC', 'UTC') =
"core_order"."submitted_time"
}}}

With a JSONField

{{{#!python
>>> qs = Order.objects.filter(cruft__bob=F("cruft__fred"))
>>> print(qs.query)
SELECT <REDACTED> FROM "core_order" WHERE
JSON_EXTRACT("core_order"."cruft", $."bob") = "core_order"."cruft"
}}}

With JSON in an OuterRef

{{{#!python
>>> qs =
Order.objects.annotate(prev=Subquery(Order.objects.filter(cruft__bob=OuterRef("cruft__fred")).values("pk")[:1]))
>>> print(qs.query)
SELECT <REDACTED>, (SELECT U0."id" FROM "core_order" U0 WHERE
JSON_EXTRACT(U0."cruft", $."bob") = "core_order"."cruft" LIMIT 1) AS
"prev" FROM "core_order"
}}}

And on Postgres

{{{#!python
>>> qs =
Order.objects.filter(submitted_time__date=F("submitted_time__date"))
>>> qs
<QuerySet []>
>>> print(qs.query)
SELECT <REDACTED> FROM "core_order" WHERE ("core_order"."submitted_time"
AT TIME ZONE 'UTC')::date = "core_order"."submitted_time"
}}}

It does the same thing on Django 2.2 as well.

All these failures are totally silent, you can create and evaluate the
queryset and the only indication that something is amiss is you get the
wrong results. And I couldn't find any reference to this behaviour in the
documentation or other tickets, although it's not an easy thing to search
for.

--
Ticket URL: <https://code.djangoproject.com/ticket/31977>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Sep 2, 2020, 12:15:10 PM9/2/20

to django-...@googlegroups.com

#31977: Transforms silently don't work in F and OuterRef expressions.
-------------------------------------+-------------------------------------

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

Changes (by Adam (Chainz) Johnson):

* cc: Adam (Chainz) Johnson (added)

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

Django

unread,

Sep 2, 2020, 12:59:28 PM9/2/20

to django-...@googlegroups.com

#31977: Transforms silently don't work in F and OuterRef expressions.
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

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

Comment (by Gordon Wrigley):

Incidentally (and for anyone who ends up here trying to find a work
around) functions work, so you can do
`Order.objects.filter(submitted_time__date=TruncDate("submitted_time"))`
for the OuterRef case you can annotate the transformed value onto the
outer queryset and OuterRef to the annotated field.

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

Django

unread,

Sep 2, 2020, 2:54:50 PM9/2/20

to django-...@googlegroups.com

#31977: Transforms silently don't work in F and OuterRef expressions.
-------------------------------------+-------------------------------------

Reporter: Gordon Wrigley | Owner: nobody
Type: Bug | Status: closed

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

Severity: Normal | Resolution: duplicate

Keywords: function, lookup, | Triage Stage:
transform, F, OuterRef | Unreviewed

Has patch: 0 | Needs documentation: 0

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