[Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"
Django
SELECT that is part of an expression"
----------------------------------------------+--------------------
Reporter: jonash | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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 `SELECT`ing all the fields if we don't need them anyways
(as in this case).
--
Ticket URL: <https://code.djangoproject.com/ticket/18580>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
SELECT that is part of an expression"
Reporter: jonash | Owner: nobody
Type: Bug | Status: new
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by aaugustin):
* needs_better_patch: => 0
* component: Database layer (models, ORM) => ORM aggregation
* needs_tests: => 0
* needs_docs: => 0
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18580#comment:1>
Django
SELECT that is part of an expression"
Reporter: jonash | Owner: nobody
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Comment (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?
--
Ticket URL: <https://code.djangoproject.com/ticket/18580#comment:2>
Django
SELECT that is part of an expression"
Reporter: jonash | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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?
--
Ticket URL: <https://code.djangoproject.com/ticket/18580#comment:4>
Django
SELECT that is part of an expression"
Reporter: jonash | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
All accepted tickets are good candidates to be fixed once we have a
working patch.
--
Ticket URL: <https://code.djangoproject.com/ticket/18580#comment:5>
Django
SELECT that is part of an expression"
Reporter: jonash | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"a84344bc539c66589c8d4fe30c6ceaecf8ba1af3" a84344bc]:
{{{
#!CommitTicketReference repository=""
revision="a84344bc539c66589c8d4fe30c6ceaecf8ba1af3"
Fixed #19513, #18580 -- Fixed crash on QuerySet.update() after annotate().
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18580#comment:6>