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
29542	Annotated field created by subquery, referenced inside of F() generates invalid SQL	Joey Wilhelm	nobody	"This seems like it might be related to #29214, but presented itself in a different scenario.

The following code
{{{#!python
from django.db import models
from django.db.models import Count, F, IntegerField, OuterRef, Subquery


class Request(models.Model):
    state = models.CharField(max_length=255)


class RequestTask(models.Model):
    request = models.ForeignKey(Request, on_delete=models.CASCADE, related_name='tasks')
    state = models.CharField(max_length=255)


def find_completed_requests():
        complete = RequestTask.objects.filter(
            request=OuterRef('pk'),
            state='success'
        ).order_by().values('request')
        complete_count = complete.annotate(c=Count('*')).values('c')

        ready_to_complete = Request.objects.annotate(
            total_tasks=Count('tasks'),
            complete_tasks=Subquery(complete_count, output_field=IntegerField())
        ).filter(
            state='in_progress',
            total_tasks=F('complete_tasks')
        )

}}}
Generates the error:
{{{#!python
Traceback (most recent call last):
  File "".venv/lib/python3.6/site-packages/django/db/backends/utils.py"", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: syntax error at or near ""SELECT""
LINE 1: ...0.""state"" = 'success') GROUP BY U0.""request_id""), SELECT COU...
}}}

This can be resolved by swapping the LHS and RHS of the fields in the final filter, to
{{{#!python
complete_tasks=F('total_tasks')
}}}"	Bug	new	Database layer (models, ORM)	2.0	Normal				Unreviewed	0	0	0	0	0	0