#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.a12ad76e1bf2ed7b1661fce2a844e1bd%40djangoproject.com.
