Opened 15 years ago

Closed 15 years ago

#10182 closed (fixed)

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

Reported by: omat Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: nested query, annotate
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (2)

comment:1 by omat, 15 years ago

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.

comment:2 by Russell Keith-Magee, 15 years ago

Resolution: fixed
Status: newclosed

(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.

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