"invalid reference to FROM-clause" for nested annotate query

[omat]

As discussed here:
​http://groups.google.com/group/django-users/browse_thread/thread/6010c4375610f5cc/

I have 2 models:

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True, db_index=True)
    slug = models.SlugField(unique=True)
    forward = models.ForeignKey('self', blank=True, null=True)
    rank = models.IntegerField(default=0)
    relevance = models.IntegerField(default=0)
    added = models.DateTimeField(auto_now_add=True)

class TaggedItem(models.Model):
    tag = models.ForeignKey(Tag, related_name='items')
    added = models.DateTimeField(auto_now_add=True)
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField(_('object id'), db_index=True)
    object = generic.GenericForeignKey('content_type', 'object_id') 

Get a list of tag ids and use it as a nested query:

# obtain a list of tag ids
>>> tag_ids = TaggedItem.objects.all().order_by('-added__max').values_list('id', flat=True).annotate(Max('added'))[:10]
>>> Tag.objects.filter(id__in=tag_ids)

and the result is

ProgrammingError: invalid reference to FROM-clause entry for table "tagging_taggeditem"
LINE 1: ... WHERE "tagging_tag"."id" IN (SELECT U0."id", MAX("tagging_t...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "u0".

The full traceback is:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 148, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 163, in __len__
    self._result_cache.extend(list(self._iter))
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 281, in iterator
    for row in self.query.results_iter():
  File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 241, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 1974, in execute_sql
    cursor.execute(sql, params)
  File "/Library/Python/2.5/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: invalid reference to FROM-clause entry for table "tagging_taggeditem"
LINE 1: ... WHERE "tagging_tag"."id" IN (SELECT U0."id", MAX("tagging_t...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "u0".

Environment: Django r9803 / PostgreSQL 8.2 / Mac OS X.

[omat]

BTW: If I force the first query to be evaluated before it is used as a nested query, using the python step syntax or using list() for example, like:

>>> tag_ids = TaggedItem.objects.all().order_by('-added__max').values_list('id', flat=True).annotate(Max('added'))[:10:1]

Then the second runs fine.

[Russell Keith-Magee]

(In [9888]) Fixed #10182 -- Corrected realiasing and the process of evaluating values() for queries with aggregate clauses. This means that aggregate queries can now be used as subqueries (such as in an in clause). Thanks to omat for the report.

This involves a slight change to the interaction of annotate() and values() clauses that specify a list of columns. See the docs for details.