#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
Reporter: Joey Wilhelm | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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')
> }}}
New description:
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')
}}}
--
Comment (by Tim Graham):
Do you have a mistake in the ticket? I'm getting `FieldError: Cannot
resolve keyword 'tasks' into field. Choices are: id, requesttask, state`.
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:1>
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/069.7c623e2e1c4800d318b56c128c6882a4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
