#29542 closed Bug (fixed)
Annotated field created by subquery, referenced inside of F() generates invalid SQL
Reported by: | Joey Wilhelm | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Mariusz Felisiak | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
This seems like it might be related to #29214, but presented itself in a different scenario.
The following code
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:
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
complete_tasks=F('total_tasks')
Attachments (1)
Change History (11)
comment:1 by , 6 years ago
Description: | modified (diff) |
---|
comment:2 by , 6 years ago
Description: | modified (diff) |
---|
Ah, right. I forgot about the related name. This is a heavily reduced version of my actual code. Sorry about that. Fixed!
comment:3 by , 6 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 2.0 → master |
OK, this reproduces for me (with a ready_to_complete.all()
in find_completed_requests
, obviously(?)).
I can't quite see at this exact moment if this is the same issue as #29214 or merely related, so I'll accept and make a note there too.
(It may be that they end up as duplicates.)
comment:4 by , 6 years ago
Cc: | added |
---|
comment:5 by , 6 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I've prepared test in our test suite.
comment:7 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:8 by , 6 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Do you have a mistake in the ticket? I'm getting
FieldError: Cannot resolve keyword 'tasks' into field. Choices are: id, requesttask, state
.