Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#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 Joey Wilhelm)

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)

29542.diff (1.3 KB ) - added by Mariusz Felisiak 6 years ago.
Test.

Download all attachments as: .zip

Change History (11)

comment:1 by Tim Graham, 6 years ago

Description: modified (diff)

Do you have a mistake in the ticket? I'm getting FieldError: Cannot resolve keyword 'tasks' into field. Choices are: id, requesttask, state.

comment:2 by Joey Wilhelm, 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 Carlton Gibson, 6 years ago

Triage Stage: UnreviewedAccepted
Version: 2.0master

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 Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added

comment:5 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

I've prepared test in our test suite.

by Mariusz Felisiak, 6 years ago

Attachment: 29542.diff added

Test.

comment:6 by Mariusz Felisiak, 6 years ago

Has patch: set

comment:7 by Tim Graham, 6 years ago

Triage Stage: AcceptedReady for checkin

comment:8 by Mariusz Felisiak, 6 years ago

Resolution: fixed
Status: assignedclosed

In 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.

comment:9 by Tim Graham <timograham@…>, 6 years ago

In e595a713:

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.

comment:10 by Tim Graham <timograham@…>, 6 years ago

In 3a505c7:

Refs #27149, #29542 -- Simplified subquery parentheses wrapping logic.

Note: See TracTickets for help on using tickets.
Back to Top