Opened 3 years ago

Last modified 6 weeks ago

#18580 new Bug

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

Reported by: jonash 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 akaariai 3 years ago.

Download all attachments as: .zip

Change History (6)

comment:1 Changed 3 years ago by aaugustin

  • Component changed from Database layer (models, ORM) to ORM aggregation
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to Bug

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 Changed 3 years ago by akaariai

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 3 years ago by akaariai

comment:3 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)

comment:4 Changed 6 weeks ago by chris-mccabe1988

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 6 weeks ago by timgraham

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

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