Opened 12 years ago

Closed 8 years ago

#18580 closed Bug (fixed)

Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

Reported by: Jonas H. Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When executing something like

Foo.objects.annotate(bar_count=Count('bars')) \
           .update(spam=42)

SQLite throws an only a single result allowed for a SELECT that is part of an expression error.

This can be worked around using .values('id'):

Foo.objects.annotate(bar_count=Count('bars')) \
           .values('id') \
           .update(spam=42)

Looks like SQLite can't cope with multiple fields in the SELECT clause, so I suggest not SELECTing all the fields if we don't need them anyways (as in this case).

Attachments (1)

ticket_18580.diff (2.7 KB ) - added by Anssi Kääriäinen 12 years ago.

Download all attachments as: .zip

Change History (7)

comment:1 by Aymeric Augustin, 12 years ago

Component: Database layer (models, ORM)ORM aggregation
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

With these models:

class Foo(models.Model):
    name = models.CharField(max_length=10)

class Bar(models.Model):
    foo = models.ForeignKey(Foo)

I could reproduce the problem:

>>> Foo.objects.annotate(bar_count=Count('bar')).update(name='blah')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/myk/Documents/dev/django-trunk/django/db/models/query.py", line 544, in update
    rows = query.get_compiler(self.db).execute_sql(None)
  File "/Users/myk/Documents/dev/django-trunk/django/db/models/sql/compiler.py", line 990, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/Users/myk/Documents/dev/django-trunk/django/db/models/sql/compiler.py", line 822, in execute_sql
    cursor.execute(sql, params)
  File "/Users/myk/Documents/dev/django-trunk/django/db/backends/util.py", line 42, in execute
    return self.cursor.execute(sql, params)
  File "/Users/myk/Documents/dev/django-trunk/django/db/backends/sqlite3/base.py", line 342, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: only a single result allowed for a SELECT that is part of an expression
>>> Foo.objects.annotate(bar_count=Count('bar')).values('id').update(name='blah')
0

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

I created an evil little patch to debug this further: patch attached, the problematic SQL is this:

UPDATE "update_datapoint" SET "value" = foo WHERE "update_datapoint"."id" IN
    (SELECT U0."id", COUNT(U1."name") AS "relatedpoint__name__count"
     FROM "update_datapoint" U0 LEFT OUTER JOIN "update_relatedpoint" U1
           ON (U0."id" = U1."data_id")
    GROUP BY U0."id", U0."name", U0."value", U0."another_value")

I don't see any reason to keep the aggregation in the inner query set. Maybe just getting rid of the annotation in the inner qs will solve this ticket?

by Anssi Kääriäinen, 12 years ago

Attachment: ticket_18580.diff added

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

Component: ORM aggregationDatabase layer (models, ORM)

comment:4 by Chris McCabe, 9 years ago

I had the exact same issue, thanks jonash for the work around. I'm a little surprised that this hasn't been fixed in the 3 years since it's been raised, the work around is simple but not entirely obvious from the error message.

Out of curiosity, will this be fixed in a future version?

comment:5 by Tim Graham, 9 years ago

All accepted tickets are good candidates to be fixed once we have a working patch.

comment:6 by Tim Graham <timograham@…>, 8 years ago

Resolution: fixed
Status: newclosed

In a84344bc:

Fixed #19513, #18580 -- Fixed crash on QuerySet.update() after annotate().

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