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