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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: [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 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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#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,
 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')
 }}}

--

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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.f885f6917bbbdf29d75aa0a931de323a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#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:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Carlton Gibson):

 * 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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.b30eef6fb91788ae642b9434961804fa%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#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:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * cc: felixxm (added)


--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:4>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.f298903e1b19eb36ba07b66c54592870%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#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:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * Attachment "29542.diff" added.

 Test.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.f1d25fdb9f11a114d5c8a3afc03eecbe%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
     Reporter:  Joey Wilhelm         |                    Owner:  felixxm
         Type:  Bug                  |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * 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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.a4e89eac3439ccd1fe6a7d02b6f82348%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
     Reporter:  Joey Wilhelm         |                    Owner:  felixxm
         Type:  Bug                  |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * Attachment "29542.diff" added.

 Test.

--
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.648312d6dd7c3ba37a7fee978ced8ec0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
     Reporter:  Joey Wilhelm         |                    Owner:  felixxm
         Type:  Bug                  |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * has_patch:  0 => 1


Comment:

 [https://github.com/django/django/pull/10177 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:6>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.35d027a8554228bb5ab3c194e83b2f72%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
     Reporter:  Joey Wilhelm         |                    Owner:  felixxm
         Type:  Bug                  |                   Status:  assigned
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Ready for
                                     |  checkin
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * stage:  Accepted => Ready for checkin


--
Ticket URL: <https://code.djangoproject.com/ticket/29542#comment:7>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.1c14a4c90955fcdc16b39693780e8670%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Django
In reply to this post by Django
#29542: Annotated field created by subquery, referenced inside of F() generates
invalid SQL
-------------------------------------+-------------------------------------
     Reporter:  Joey Wilhelm         |                    Owner:  felixxm
         Type:  Bug                  |                   Status:  closed
    Component:  Database layer       |                  Version:  master
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  fixed
     Keywords:                       |             Triage Stage:  Ready for
                                     |  checkin
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by felixxm):

 * 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 <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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.de147525f26d5f612b42a4207da5ce59%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.