id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32995	Incorrect GROUP BY in ORM query with Function	"Joshua ""jag"" Ginsberg"	nobody	"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)))
}}}"	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate			Unreviewed	0	0	0	0	0	0