[Django] #32995: Incorrect GROUP BY in ORM query with Function
Django
-------------------------------------+-------------------------------------
Reporter: Joshua | Owner: nobody
"jag" Ginsberg |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.1
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 |
-------------------------------------+-------------------------------------
With this model (abbreviated to relevant fields):
{{{
class ListeningLineCall(models.Model):
class CallResult(models.IntegerChoices):
IN_PROGRESS = 1, "In progress"
CONNECTED = 2, "Connected"
LOST = 3, "Lost before connection"
BLOCKED = 4, "Blocked"
UNAVAILABLE = 5, "Unavailable"
class Medium(models.IntegerChoices):
VOICE = 1, "Voice"
SMS = 2, "Text"
caller = models.ForeignKey(ListeningLineCaller,
on_delete=models.CASCADE)
medium = models.PositiveIntegerField(choices=Medium.choices,
default=Medium.VOICE)
call_sid = models.CharField(max_length=34, unique=True, blank=True,
null=True)
result = models.PositiveIntegerField(choices=CallResult.choices,
default=CallResult.IN_PROGRESS)
called_at = models.DateTimeField(auto_now_add=True)
}}}
and the following QuerySet:
{{{
calls_by_date_medium_and_status = (
ListeningLineCall.objects.annotate(called_at_date=TruncDate("called_at")).filter(called_at_date__gt=seven_days_ago,
called_at_date__lt=today)
.exclude(result=ListeningLineCall.CallResult.IN_PROGRESS)
.values_list("medium", "result", "called_at_date")
.annotate(count=Count("call_sid"))
)
}}}
... the following SQL is generated:
{{{
>>> print(calls_by_date_medium_and_status.query)
SELECT "listeningline_listeninglinecall"."medium",
"listeningline_listeninglinecall"."result",
("listeningline_listeninglinecall"."called_at" AT TIME ZONE
'US/Eastern')::date AS "called_at_date",
COUNT("listeningline_listeninglinecall"."call_sid") AS "count" FROM
"listeningline_listeninglinecall" WHERE
(("listeningline_listeninglinecall"."called_at" AT TIME ZONE
'US/Eastern')::date > 2021-07-29 AND
("listeningline_listeninglinecall"."called_at" AT TIME ZONE
'US/Eastern')::date < 2021-08-05 AND NOT
("listeningline_listeninglinecall"."result" = 1)) GROUP BY
"listeningline_listeninglinecall"."medium",
"listeningline_listeninglinecall"."result",
("listeningline_listeninglinecall"."called_at" AT TIME ZONE
'US/Eastern')::date, "listeningline_listeninglinecall"."called_at"
}}}
I expected that the GROUP BY clause would contain the date-truncated
version of `called_at` but I did not expect that it would include the non-
truncated version of `called_at`, the inclusion of which is making my
result-set unusable.
I do not see `called_at` without the function in the Query object's
`group_by` attribute:
{{{
>>> pprint.pprint(calls_by_date_medium_and_status.query.group_by)
(Col(listeningline_listeninglinecall,
listeningline.ListeningLineCall.medium),
Col(listeningline_listeninglinecall,
listeningline.ListeningLineCall.result),
TruncDate(Col(listeningline_listeninglinecall,
listeningline.ListeningLineCall.called_at)))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32995>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Ginsberg |
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* type: Uncategorized => Bug
* resolution: => needsinfo
Comment:
Thanks for the report, unfortunately I cannot reproduce your issue with
provided models and querysets (at least on Django 3.1, 3.2, and the
current `main` branch). Can you provide a sample project?
--
Ticket URL: <https://code.djangoproject.com/ticket/32995#comment:1>
Django
-------------------------------------+-------------------------------------
Ginsberg |
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
I think I found the issue/discrepancy between the model snippet and my
actual one: the default ordering for queries included `called_at` -- which
seems to have affected the GROUP BY, even though `called_at` wasn't in the
query and the resulting SQL didn't have an ORDER BY clause.
This feels like a bug, though not the one I originally believed. Thoughts?
--
Ticket URL: <https://code.djangoproject.com/ticket/32995#comment:2>
Django
-------------------------------------+-------------------------------------
Ginsberg |
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Thanks for extra details.
> This feels like a bug, though not the one I originally believed.
Thoughts?
It's a duplicate of #32546 (fixed in Django 4.0).
--
Ticket URL: <https://code.djangoproject.com/ticket/32995#comment:3>