Can't update queryset after Count annotation

[Fraser Harris]

A Queryset that has been annotated with a Count of a ManyToMany field can not be update'd.

Example:

>>> Category.objects.annotate(Count('tiles')).update(name='foo')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 490, in update
    rows = query.get_compiler(self.db).execute_sql(None)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 976, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 782, in execute_sql
    cursor.execute(sql, params)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/backends/util.py", line 69, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/opt/secondfunnel/venv/local/lib/python2.7/site-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: subquery has too many columns
LINE 1: ..." SET "name" = 'foo' WHERE "assets_category"."id" IN (SELECT...
                                                             ^

Models:

class Category(BaseModel):
    tiles = models.ManyToManyField(Tile, related_name='categories')
    name = models.CharField(max_length=255)

class Tile(Model):
    pass

SQL Query from django.db.connections.queries:

UPDATE "assets_category" SET "name" = \'foo\' WHERE "assets_category"."id" IN (SELECT U0."id", COUNT(U2."tile_id") AS "tiles__count" FROM "assets_category" U0 LEFT OUTER JOIN "assets_category_tiles" U2 ON ( U0."id" = U2."category_id" ) GROUP BY U0."id", U0."name")

[Tim Graham]

I guess trying to update after annotations or aggregations probably isn't designed to work. Should we throw a friendlier error message?

[Carl Meyer]

Replying to timgraham:

I guess trying to update after annotations or aggregations probably isn't designed to work. Should we throw a friendlier error message?

Well, certainly not after a terminal aggregation. But I don't see any a priori reason to assume that if you have an annotated QuerySet you shouldn't be able to update it. ISTM this should be accepted as a bug, and we should only resort to "friendlier error message" if somebody looks at it closely and reports more authoritatively that it's not feasible to support.

[Shai Berger]

For the query given in the report:

Category.objects.annotate(Count('tiles')).update(name='foo')

I would like to see a warning (if not an error) because the annotation is "dead code" and probably a bug.
However, I'd expect this to work (assuming an additional numeric field "score" on the Category model)

Category.objects.annotate(n_tiles=Count('tiles')).update(score=F('score')+F('n_tiles'))

[Carl Meyer]

Replying to shaib:

For the query given in the report:

Category.objects.annotate(Count('tiles')).update(name='foo')

I would like to see a warning (if not an error) because the annotation is "dead code" and probably a bug.

Sure, but unless that falls out naturally in an implementation that supports the useful cases, I think it's firmly in nice-to-have territory. An implementation that let this pass silently wouldn't bother or surprise me at all.

[zauddelig]

This would be legit

categories = Category.objects.annotate(Count('tiles'))
#  some code
categories.update(name='foo')

Another thing that fails is this:

Category.objects.all().annotate(tiles_count=Count("tiles")).filter(tiles_count__gt=0).update(used=True)

Anyway the problem is due to the fact that it tries to use Category.id although it seems to be out of scope.
The problem seem to be with the SQLUpdateCompiler

[Josh Smeaton]

The query that would work in the above situation (aggregate, filter, update) would be roughly:

update category
set used=true
where id in (
    select X.id
    from (select id, COUNT(tiles_id) tile_count from category group by id) X
    where X.tile_count >= 0
)

-- OR preferably?

update category
set used=true
where id in (
    select id from category group by id
    having COUNT(tiles_id) > 0
)

Which I think can be generated with roughly the following ORM code (not tested):

inner = Category.objects.all().annotate(tiles_count=Count("tiles")).filter(tiles_count__gt=0)
# maybe inner = inner.values('pk') ?
Category.objects.filter(pk__in=inner).update(used=True)

It might be possible to get SQLUpdateCompiler to detect annotations, and then create a new queryset, passing the existing queryset as an argument to pk__in=previous. Or teach it to drop anything from the subquerys annotations_select but ensure it stills GROUPs and HAVINGs.

[zauddelig]

If you take out the annotation part then you wouldn't be able to use annotated values in filters nor in F functions.

[Tim Graham]

Is this a duplicate of or related to #19513? It describes a failure of update() after annotate(val=Sum(...)).

[Josh Smeaton]

Duplicate of #19513 as Tim pointed out.