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 31377,"Django 3.0: ""GROUP BY"" clauses error with tricky field annotation",Holovashchenko Vadym,Hasan Ramezani,"Let's pretend that we have next model structure with next model's relations: {{{ class A(models.Model): bs = models.ManyToManyField('B', related_name=""a"", through=""AB"") class B(models.Model): pass class AB(models.Model): a = models.ForeignKey(A, on_delete=models.CASCADE, related_name=""ab_a"") b = models.ForeignKey(B, on_delete=models.CASCADE, related_name=""ab_b"") status = models.IntegerField() class C(models.Model): a = models.ForeignKey( A, null=True, blank=True, on_delete=models.SET_NULL, related_name=""c"", verbose_name=_(""a"") ) status = models.IntegerField() }}} Let's try to evaluate next query {{{ ab_query = AB.objects.filter(a=OuterRef(""pk""), b=1) filter_conditions = Q(pk=1) | Q(ab_a__b=1) query = A.objects.\ filter(filter_conditions).\ annotate( status=Subquery(ab_query.values(""status"")), c_count=Count(""c""), ) answer = query.values(""status"").annotate(total_count=Count(""status"")) print(answer.query) print(answer) }}} On Django 3.0.4 we have an error {{{ django.db.utils.ProgrammingError: column reference ""status"" is ambiguous }}} and query is next: {{{ SELECT (SELECT U0.""status"" FROM ""test_app_ab"" U0 WHERE (U0.""a_id"" = ""test_app_a"".""id"" AND U0.""b_id"" = 1)) AS ""status"", COUNT((SELECT U0.""status"" FROM ""test_app_ab"" U0 WHERE (U0.""a_id"" = ""test_app_a"".""id"" AND U0.""b_id"" = 1))) AS ""total_count"" FROM ""test_app_a"" LEFT OUTER JOIN ""test_app_ab"" ON (""test_app_a"".""id"" = ""test_app_ab"".""a_id"") LEFT OUTER JOIN ""test_app_c"" ON (""test_app_a"".""id"" = ""test_app_c"".""a_id"") WHERE (""test_app_a"".""id"" = 1 OR ""test_app_ab"".""b_id"" = 1) GROUP BY ""status"" }}} However, Django 2.2.11 processed this query properly with the next query: {{{ SELECT (SELECT U0.""status"" FROM ""test_app_ab"" U0 WHERE (U0.""a_id"" = (""test_app_a"".""id"") AND U0.""b_id"" = 1)) AS ""status"", COUNT((SELECT U0.""status"" FROM ""test_app_ab"" U0 WHERE (U0.""a_id"" = (""test_app_a"".""id"") AND U0.""b_id"" = 1))) AS ""total_count"" FROM ""test_app_a"" LEFT OUTER JOIN ""test_app_ab"" ON (""test_app_a"".""id"" = ""test_app_ab"".""a_id"") LEFT OUTER JOIN ""test_app_c"" ON (""test_app_a"".""id"" = ""test_app_c"".""a_id"") WHERE (""test_app_a"".""id"" = 1 OR ""test_app_ab"".""b_id"" = 1) GROUP BY (SELECT U0.""status"" FROM ""test_app_ab"" U0 WHERE (U0.""a_id"" = (""test_app_a"".""id"") AND U0.""b_id"" = 1)) }}} so, the difference in ""GROUP BY"" clauses (as DB provider uses ""django.db.backends.postgresql"", postgresql 11) ",Bug,closed,"Database layer (models, ORM)",3.0,Release blocker,fixed,"group by, postgres, annotate",,Ready for checkin,1,0,0,0,0,0