#33759: Using subquery to filter a model delete creates sub-optimal SQL
-------------------------------------+-------------------------------------
Reporter: bertonha | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by bertonha:
Old description:
> Taking in consideration the app of tutorial for demonstrate the issue.
> using postgresql as db.
>
> Executing this following code:
>
> {{{
> subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6,
> 1))[:1000]
> Comment.objects.filter(id__in=subquery).delete()
> }}}
>
> Generates the following SQL:
>
> {{{
> DELETE FROM "comment" WHERE "comment"."id" IN (SELECT V0."id" FROM
> "comment" V0 WHERE V0."id" IN (SELECT U0."id" FROM "comment" U0 WHERE
> U0."created_at" < \'2022-06-01T00:00:00+00:00\'::timestamptz LIMIT 1000))
> }}}
>
> The inner **select V0** is completely unnecessary
>
> If a simple select with the same subquery is executed. It generates
> expected SQL query.
>
> {{{
> subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6,
> 1))[:1000]
> comments = list( Comment.objects..filter(id__in=subquery) )
> }}}
>
> {{{
> SELECT * FROM "comment" WHERE "comment"."id" IN (SELECT U0."id" FROM
> "comment" U0 WHERE U0."created_at" <
> '2022-06-01T00:00:00+00:00'::timestamptz LIMIT 1000),
> }}}
New description:
Taking in consideration the app of tutorial for demonstrate the issue.
using postgresql as db.
Executing this following code:
{{{
subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6,
1))[:1000]
Comment.objects.filter(id__in=subquery).delete()
}}}
Generates the following SQL:
{{{
DELETE FROM "comment" WHERE "comment"."id" IN (SELECT V0."id" FROM
"comment" V0 WHERE V0."id" IN (SELECT U0."id" FROM "comment" U0 WHERE
U0."created_at" < '2022-06-01T00:00:00+00:00'::timestamptz LIMIT 1000))
}}}
The inner **select V0** is completely unnecessary
If a simple select with the same subquery is executed. It generates
expected SQL query.
{{{
subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6,
1))[:1000]
comments = list( Comment.objects..filter(id__in=subquery) )
}}}
{{{
SELECT * FROM "comment" WHERE "comment"."id" IN (SELECT U0."id" FROM
"comment" U0 WHERE U0."created_at" <
'2022-06-01T00:00:00+00:00'::timestamptz LIMIT 1000),
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33759#comment:2>
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/01070181201ab895-736400ca-20b3-4d5a-8aab-d70dcfd9ad56-000000%40eu-central-1.amazonses.com.
