#32433: Delete distinct produces an unexpected and potentially harmful SQL
-------------------------------------+-------------------------------------
Reporter: egism | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, delete, | Triage Stage:
distinct, postgresql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by egism:
Old description:
> I was looking for a way to delete the first Comment of each Post (a
> sample domain). Since I know that every new Post starts with a system
> generated comment I decided to go with:
>
> `Comment.objects.order_by('post_id',
> 'created_at').distinct('post_id').delete()`
>
> Before proceeding I tested it with:
>
> `Comment.objects.order_by('post_id',
> 'created_at').distinct('post_id').count()`
>
> Made sure the result actually contains what I needed and proceeded with
> the `delete()`. The result was rather surprising. I was notified that the
> whole table was wiped clean. I then checked the actual SQL that was
> executed and it was a simple `DELETE FROM comments;`.
>
> As an ORM user, I would say it is the worst outcome possible and I would
> at least expect an error in such a case or ideally a SQL of what I was
> trying to achieve. At the same time, `count` and `delete` produces an
> inconsistent result which is even more mistaking.
>
> Potential solutions:
> * raise an error with a decent explanation
> * produce a desired SQL according to the query
New description:
I was looking for a way to delete the first Comment of each Post (a sample
domain). Since I know that every new Post starts with a system generated
comment I decided to go with:
`Comment.objects.order_by('post_id',
'created_at').distinct('post_id').delete()`
Before proceeding I tested it with:
`Comment.objects.order_by('post_id',
'created_at').distinct('post_id').count()`
Made sure the result actually contains what I needed and proceeded with
the `delete()`. The result was rather surprising. I was notified that the
whole table was wiped clean. I then checked the actual SQL that was
executed and it was a simple `DELETE FROM comments;`.
As an ORM user, I would say it is the worst outcome possible and I would
at least expect an error in such a case or ideally a SQL of what I was
trying to achieve. At the same time, `count` and `delete` produces an
inconsistent result which is even more mistaking.
Potential solutions:
* raise an error with a decent explanation
* produce a desired SQL according to the query
Since I have never submitted a change to Django, I have a very limited
knowledge of the ORM and its intricacies. I could give it a try and issue
a patch for this with some guidance.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32433#comment:1>
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/063.be279b0c0a729f3207d285623c31abc1%40djangoproject.com.
