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

[Jonas H.]

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

[Aymeric Augustin]

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

[Anssi Kääriäinen]

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?

[Chris McCabe]

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?

[Tim Graham]

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

[Tim Graham <timograham@…>]

In a84344bc:

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