Transforms silently don't work in F and OuterRef expressions.

[Gordon Wrigley]

I've seen this on different databases and different Django versions.

Here's Sqlite with Django 3.1.1

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

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

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

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

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

[Mariusz Felisiak]

Duplicate of #31639.