#32995: Incorrect GROUP BY in ORM query with Function
-------------------------------------+-------------------------------------
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.
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.3b54f19de915b9bddc339dc978f78cc5%40djangoproject.com.
