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!

[Malcolm Tredinnick]

On Mon, 2008-09-22 at 18:22 -0700, cfobel wrote:
> 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)

This is a pretty good test case, but the looks of it. The only thing
missing is the custom manager (since you're calling MyItem.objects_all,
not MyItem.objects) and that possibly affects something, too.

In any case, any time we're generating invalid SQL instead of raising
some other error (or doing the right thing for valid code -- and the
above looks valid at first glance), it's a bug.

Could you please open a ticket for this so that I remember to look at it
(assign it to "mtredinnick" straight away, if you like).

Well, not really. The users_myitem table in the main query and
users_myitem in the subquery are different instances of the table, so it
has to have an alias. The main table from the outer query is always
going to end up being "U0" in an inner query like this (since it's the
first in a list of names and the number is the index in the list).

> and then is referred to as 'U1' on the next line.

The interesting bit is what happened to U1. I have a suspicion this
might be related to another optimisation that isn't working reliably,
but which I thought wasn't harming anything (just generating less than
optimal code): we shouldn't really need U0 in the above query, since we
can just test against the thing it's joining to.

There's definitely something going wrong here. Again, we shouldn't ever
be sending malformed SQL to the database. That's always a Django bug. I
can't drop everything right this minute to look at it, but if you open a
ticket I'll certainly take a look, since SQL bugs are something I take
very personally.

Regards,
Malcolm

[cfobel]

Thanks for your quick reply. I will submit a bug report.

Thanks!
Christian

On Sep 22, 9:45 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

> On Mon, 2008-09-22 at 18:22 -0700, cfobel wrote:
> > Hello,
>

> > I'm encountering anerrorwhen 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)
>

> This is a pretty good test case, but the looks of it. The only thingmissingis the custom manager (since you're calling MyItem.objects_all,

> not MyItem.objects) and that possibly affects something, too.
>
> In any case, any time we're generating invalid SQL instead of raising

> some othererror(or doing the right thing for valid code -- and the

> above looks valid at first glance), it's a bug.
>
> Could you please open a ticket for this so that I remember to look at it
> (assign it to "mtredinnick" straight away, if you like).
>
>
>
>
>

> > TheerrorI get is:

> > It looks like thetable"users_myitem" is being improperly labeled as
> > 'U0',
>
> Well, not really. The users_myitemtablein the main query and
> users_myitem in thesubqueryare different instances of thetable, so it
> has to have an alias. The maintablefrom the outer query is always

[cfobel]

Malcolm,

I've posted a new ticket and assigned it to you at:
http://code.djangoproject.com/ticket/9188

I look forward to hearing from you.

Thanks again!
Christian