#29214: Invalid SQL generated when annotating a subquery with an outerref to an
annotated field.
-------------------------------------+-------------------------------------
Reporter: Oskar Persson | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset | Triage Stage: Accepted
annotations |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Chetan Khanna):
Replying to [comment:5 Simon Charette]:
> #30009 was a duplicate that wasn't relying on `update()`.
I don't think this issue is strictly related to `annotate`. Possibly, its
the `update()` clause. If we remove the `update()` clause, the query runs
just fine.
{{{
latest_parent =
Recursive.objects.filter(link=OuterRef('parent_link')).order_by('id')
Recursive.objects.filter(parent__isnull=False) \
.annotate(parent_link=F('parent__link')) \
.annotate(p=Subquery(latest_parent.values('pk')[:1])) \
.values()
}}}
which produces the following SQL:
{{{
SELECT "recursive_model"."id",
"recursive_model"."name",
"recursive_model"."link",
"recursive_model"."parent_id",
T2."link" AS "parent_link",
(
SELECT U0."id"
FROM "recursive_model" U0
WHERE U0."link" = T2."link"
ORDER BY U0."id" ASC
LIMIT 1
) AS "p"
FROM "recursive_model"
INNER JOIN "recursive_model" T2
ON ("recursive_model"."parent_id" = T2."id")
WHERE "recursive_model"."parent_id" IS NOT NULL
LIMIT 21
}}}
Also the query on https://code.djangoproject.com/ticket/30009 seems to run
just fine on current master. (I had to tweak it a bit since the otherwise
the ORM complained about multiple columns being returned)
query:
{{{
Task.objects.annotate(
top_case_id=Coalesce(F('case__parent_case__parent_case_id'),
F('case__parent_case_id'), F('case_id')),
subject=Subquery(Subject.objects.filter(case_id=OuterRef('top_case_id')).values('id'),
output_field=IntegerField())
).all()
}}}
corresponding SQL:
{{{
SELECT "ticket29214_task"."id",
"ticket29214_task"."num",
"ticket29214_task"."case_id",
COALESCE(T3."parent_case_id", "ticket29214_case"."parent_case_id",
"ticket29214_task"."case_id") AS "top_case_id",
(
SELECT U0."id"
FROM "ticket29214_subject" U0
WHERE U0."case_id" = COALESCE(T3."parent_case_id",
"ticket29214_case"."parent_case_id", "ticket29214_task"."case_id")
) AS "subject"
FROM "ticket29214_task"
LEFT OUTER JOIN "ticket29214_case"
ON ("ticket29214_task"."case_id" = "ticket29214_case"."id")
LEFT OUTER JOIN "ticket29214_case" T3
ON ("ticket29214_case"."parent_case_id" = T3."id")
LIMIT 21
}}}
(No extra quoting aroung "T3" as mentioned in the description)
Database used was PostgreSQL and SQLs are picked from `shell_plus` of
`django_extensions`.
--
Ticket URL: <https://code.djangoproject.com/ticket/29214#comment:9>
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.a12ad76e1bf2ed7b1661fce2a844e1bd%40djangoproject.com.
