Opened 8 years ago

Closed 4 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


When executing something like

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

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') \

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 8 years ago.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 8 years ago by Aymeric Augustin

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/", line 544, in update
    rows = query.get_compiler(self.db).execute_sql(None)
  File "/Users/myk/Documents/dev/django-trunk/django/db/models/sql/", line 990, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/Users/myk/Documents/dev/django-trunk/django/db/models/sql/", line 822, in execute_sql
    cursor.execute(sql, params)
  File "/Users/myk/Documents/dev/django-trunk/django/db/backends/", line 42, in execute
    return self.cursor.execute(sql, params)
  File "/Users/myk/Documents/dev/django-trunk/django/db/backends/sqlite3/", 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')

comment:2 Changed 8 years ago by 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?

Changed 8 years ago by Anssi Kääriäinen

Attachment: ticket_18580.diff added

comment:3 Changed 7 years ago by Anssi Kääriäinen

Component: ORM aggregationDatabase layer (models, ORM)

comment:4 Changed 5 years ago by 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?

comment:5 Changed 5 years ago by Tim Graham

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

comment:6 Changed 4 years ago by Tim Graham <timograham@…>

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