#31377: Django 3.0: "GROUP BY" clauses error with tricky field annotation
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
Golova1111 |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) | Keywords: group by, postgres,
Severity: Normal | annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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)
--
Ticket URL: <https://code.djangoproject.com/ticket/31377>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.742f859cdd31c2f91b36e0e5012c959c%40djangoproject.com.
