#34285 closed Bug (fixed)
Index transforms on filtered array aggregates produces incorrect SQL query
Reported by: | Nils Van Zuijlen | Owned by: | Nils Van Zuijlen |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.2 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
For example, with the following models:
class MembershipKind(models.TextChoices): MEMBER = "member", _("Member") DIRECTOR = "director", _("Director") MANAGER = "manager", _("Manager") class Project(models.Model): name = models.CharField(max_length=255) members = models.ManyToManyField(User, related_name="projects", blank=True, through="ProjectMember") class ProjectMember(models.Model): project = models.ForeignKey( Project, related_name="memberships", on_delete=models.CASCADE, verbose_name=_("project") ) user = models.ForeignKey(User, related_name="project_memberships", on_delete=models.CASCADE, verbose_name=_("user")) kind = models.CharField(choices=MembershipKind.choices, max_length=10) class Meta: constraints = [models.UniqueConstraint(fields=("project", "user"), name="project_user_unique_link")]
The following query has missing parenthesis around the annotated field first_director_id.
>>> Project.objects.all().annotate( ... director_ids=ArrayAgg('memberships__user_id', filter=Q(memberships__kind=MembershipKind.DIRECTOR)) ... ).annotate( ... first_director_id=F('director_ids__0') ... ).query.sql_with_params() ( '''SELECT "imputations_project"."id", "imputations_project"."name", ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE "imputations_projectmember"."kind" = %s) AS "director_ids", ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE "imputations_projectmember"."kind" = %s)[%s] AS "first_director_id" FROM "imputations_project" LEFT OUTER JOIN "imputations_projectmember" ON ("imputations_project"."id" = "imputations_projectmember"."project_id") GROUP BY "imputations_project"."id"''', (<MembershipKind.DIRECTOR: 'director'>, <MembershipKind.DIRECTOR: 'director'>, 1) )
It should be
(ARRAY_AGG("imputations_projectmember"."user_id" ) FILTER (WHERE "imputations_projectmember"."kind" = %s))[%s] AS "first_director_id"
Change History (8)
comment:1 by , 21 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 21 months ago
Component: | Uncategorized → Database layer (models, ORM) |
---|
comment:3 by , 21 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I tried to write unit tests on main, but they failed with another error.
The proposed solution makes another test on integer nested indexation fail, but I don't know how to fix that.
comment:4 by , 21 months ago
Has patch: | set |
---|---|
Patch needs improvement: | set |
comment:6 by , 20 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
Note:
See TracTickets
for help on using tickets.
Thanks for the report, it seems the issue lies in
IndexTransform.as_sql
where me might want to dodjango/contrib/postgres/fields/array.py
%s[%%s]" % lhs, params + [self.index]It seems that slicing is suffering from the same issue (e.g.
director_ids__0_2
)Would you be interested in submitting a PR with this patch that includes a regression test?