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
31977	Transforms silently don't work in F and OuterRef expressions.	Gordon Wrigley	nobody	"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.

"	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate	function, lookup, transform, F, OuterRef	Adam Johnson	Unreviewed	0	0	0	0	0	0