[Django] #29542: Annotated field created by subquery, referenced inside of F() generates invalid SQL

[Django]

#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
               Reporter:  Joey       |          Owner:  nobody
  Wilhelm                            |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  2.0
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 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)
     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')
 }}}

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

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/054.33a95977f401ce3a231109615d4df61c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.