[Django] #29542: Annotated field created by subquery, referenced inside of F() generates invalid SQL
Django
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.
Django
invalid SQL
-------------------------------------+-------------------------------------
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:
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)
--
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
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
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)
>
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,
related_name='tasks')
state = models.CharField(max_length=255)
--
Comment (by Joey Wilhelm):
Ah, right. I forgot about the related name. This is a heavily reduced
version of my actual code. Sorry about that. Fixed!
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:2>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.0 => master
* stage: Unreviewed => Accepted
Comment:
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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:3>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: felixxm (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:4>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "29542.diff" added.
Test.
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => felixxm
* status: new => assigned
Comment:
I've prepared
[https://code.djangoproject.com/attachment/ticket/29542/29542.diff test]
in our test suite.
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:5>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "29542.diff" added.
Test.
--
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10177 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:6>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:7>
Django
invalid SQL
-------------------------------------+-------------------------------------
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In
[https://github.com/django/django/commit/dd3b4707198f17557fdd9fe7a6fd9025b23dcaf3
dd3b470]:
Fixed #29542 -- Fixed invalid SQL if a Subquery from the HAVING clause is
used in the GROUP BY clause.
Thanks Tim Graham for the review.
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:8>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"e595a713cc5ce66dfc5e22f85d671c06d842e99b" e595a713]:
{{{
#!CommitTicketReference repository=""
revision="e595a713cc5ce66dfc5e22f85d671c06d842e99b"
Refs #29542, #30158 -- Enabled a HAVING subquery filter test on Oracle.
Now that subquery annotations aren't included in the GROUP BY unless
explicitly grouped against, the test works on Oracle.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:9>
Django
invalid SQL
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"3a505c70e7b228bf1212c067a8f38271ca86ce09" 3a505c7]:
{{{
#!CommitTicketReference repository=""
revision="3a505c70e7b228bf1212c067a8f38271ca86ce09"
Refs #27149, #29542 -- Simplified subquery parentheses wrapping logic.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:10>