Description
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)
895f28f9cbed (django/django 3.1), dependencies installed and tests runnable from the first minute. You are graded by hidden tests taken from the fix that was actually merged upstream.