Postgresql 'missing FROM-clause entry in subquery for table' error on lookup that spans relationships

[cfobel]

Hello,

I'm encountering an error when performing a lookup that spans
relationships.  The query is as follows:

myitems =
MyItem.objects_all.exclude(user__somemodel__created__gte=(datetime.now()
- timedelta(days=3)))

With the following (stripped) models:

class MyItem(models.Model):
    user = models.ForeignKey(user, unique=True)

class SomeModel(models.Model):
    created = models.DateTimeField('Date created',
default=datetime.now)
    user = models.ForeignKey(User)

The error I get is:

Traceback (most recent call last):
  ....
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py",
line 179, in _result_iter
    self._fill_cache()
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py",
line 612, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/usr/lib/python2.5/site-packages/django/db/models/query.py",
line 269, in iterator
    for row in self.query.results_iter():
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/
query.py", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/lib/python2.5/site-packages/django/db/models/sql/
query.py", line 1723, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.5/site-packages/django/db/backends/util.py",
line 19, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: missing FROM-clause entry in subquery for
table "u1"
LINE 1: ..._myitem" U0 INNER JOIN "notes_note" U2 ON (U1."id" = ...
 
^

To debug the issue, I looked at the SQL generated by the 'myitems'
queryset above.  The generated SQL is:

SELECT "users_myitem"."id", "users_myitem"."user_id"
FROM "users_myitem"
WHERE NOT (
  "users_myitem"."user_id" IN (
    SELECT U2."user_id"
    FROM "users_myitem" U0
    INNER JOIN "myapp_somemodel" U2 ON (U1."id" = U2."user_id")
    WHERE U2."created" >= 2008-09-19 19:57:43.111687
  )
)

It looks like the table "users_myitem" is being improperly labeled as
'U0', and then is referred to as 'U1' on the next line.  If I correct
this issue and run the SQL command manually, I get the expected
records returned.  The corrected SQL is as follows:

SELECT "users_myitem"."id", "users_myitem"."user_id"
FROM "users_myitem"
WHERE NOT (
  "users_myitem"."user_id" IN (
    SELECT U2."user_id"
    FROM "users_myitem" U1
    INNER JOIN "myapp_somemodel" U2 ON (U1."id" = U2."user_id")
    WHERE U2."created" >= '2008-09-19 19:51:43.151089'
  )
)

Am I doing something wrong?  Is this a bug?

Any help would be greatly appreciated.

Thanks,
Christian

PS - I'm really enjoying 1.0!

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---