[Django] #19102: Deletion using subqueries doesn't work for aggregates, extra or select_related

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) | Keywords:
Severity: Release | Has patch: 1
blocker | Needs tests: 0
Triage Stage: Accepted | Easy pickings: 0
Needs documentation: 0 |
Patch needs improvement: 1 |
UI/UX: 0 |
-------------------------------------+-------------------------------------
Aggregates, extra and select_related add columns to the subselect.
However, the subselect must contain only one column.

Example case (modified from #18676's example):
{{{
class OrgUnit(models.Model):
name = models.CharField(max_length=64, unique=True)

class Login(models.Model):
description = models.CharField(max_length=32)
orgunit = models.ForeignKey(OrgUnit)

Login.objects.extra(
select={'foo':'description'}
).annotate(
n=models.Count('description')
).filter(n=1).select_related('orgunit').delete()
}}}

The above fails with "too many columns in subquery" for various databases.

I have a work-in-progress patch for this at:
https://github.com/akaariai/django/commit/34a39ba8981638db2eb3fdb3d8d45393130a9d99

This is a regression caused by #18676, so marking as release blocker.

--
Ticket URL: <https://code.djangoproject.com/ticket/19102>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------
Reporter: akaariai | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by pressureman):

* cc: django@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:1>

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------

Reporter: akaariai | Owner: akaariai
Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Release blocker | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by akaariai):

* owner: nobody => akaariai

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:2>

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------

Reporter: akaariai | Owner: akaariai
Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) | Resolution:

Severity: Release blocker | Triage Stage: Ready for
Keywords: | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by akaariai):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin

Comment:

Updated patch at
https://github.com/akaariai/django/compare/ticket_18676_fix

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:3>

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------

Reporter: akaariai | Owner: akaariai
Type: Bug | Status: closed

Component: Database layer | Version: master

(models, ORM) | Resolution: fixed

Severity: Release blocker | Triage Stage: Ready for
Keywords: | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Anssi Kääriäinen <akaariai@…>):

* status: new => closed
* resolution: => fixed

Comment:

In [changeset:"f64a5ef404cb6fd28e008a01039a3beea2fa8e1b"]:
{{{
#!CommitTicketReference repository=""
revision="f64a5ef404cb6fd28e008a01039a3beea2fa8e1b"
Fixed #19102 -- Fixed fast-path delete for modified SELECT clause cases

There was a bug introduced in #18676 which caused fast-path deletes
implemented as "DELETE WHERE pk IN <subquery>" to fail if the SELECT
clause contained additional stuff (for example extra() and annotate()).

Thanks to Trac alias pressureman for spotting this regression.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:4>

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------

Reporter: Anssi Kääriäinen | Owner: Anssi
| Kääriäinen

Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |

Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for

| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"4e8ecf0cb6ea36c45edb9cb86f0d63224e08097e" 4e8ecf0]:
{{{
#!CommitTicketReference repository=""
revision="4e8ecf0cb6ea36c45edb9cb86f0d63224e08097e"
Refs #19102 -- Removed flaky test
Ticket19102Tests.test_ticket_19102_distinct_on.

The subquery pushdown only happens because another table is involved in
filter. It's not the distinct usage that causes the pushdown.

The distinct('description').order_by('pk') expression is not valid
because SELECT DISTINCT ON must match initial ORDER BY expressions
which is not the case here.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:5>

[Django]

#19102: Deletion using subqueries doesn't work for aggregates, extra or
select_related
-------------------------------------+-------------------------------------

Reporter: Anssi Kääriäinen | Owner: Anssi
| Kääriäinen
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"98ce39b5a35c9d47b6c34764152fb3b1d2569c3e" 98ce39b5]:
{{{
#!CommitTicketReference repository=""
revision="98ce39b5a35c9d47b6c34764152fb3b1d2569c3e"
[3.2.x] Refs #19102 -- Removed flaky test
Ticket19102Tests.test_ticket_19102_distinct_on.

The subquery pushdown only happens because another table is involved in
filter. It's not the distinct usage that causes the pushdown.

The distinct('description').order_by('pk') expression is not valid
because SELECT DISTINCT ON must match initial ORDER BY expressions
which is not the case here.

Backport of 4e8ecf0cb6ea36c45edb9cb86f0d63224e08097e from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/19102#comment:6>