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