Opened 11 years ago

Closed 11 years ago

Last modified 3 years ago

#19102 closed Bug (fixed)

Deletion using subqueries doesn't work for aggregates, extra or select_related

Reported by: Anssi Kääriäinen Owned by: Anssi Kääriäinen
Component: Database layer (models, ORM) Version: dev
Severity: Release blocker Keywords:
Cc: django@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (6)

comment:1 by Daniel Swarbrick, 11 years ago

Cc: django@… added

comment:2 by Anssi Kääriäinen, 11 years ago

Owner: changed from nobody to Anssi Kääriäinen

comment:3 by Anssi Kääriäinen, 11 years ago

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:4 by Anssi Kääriäinen <akaariai@…>, 11 years ago

Resolution: fixed
Status: newclosed

In 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.

comment:5 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 4e8ecf0:

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.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 98ce39b5:

[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

Note: See TracTickets for help on using tickets.
Back to Top