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

[omat]

Hi all,

I obtain a list of tag ids by:
tag_ids = TaggedItem.objects.all().order_by('-added__max').annotate(Max
('added'))[:10]

and try to use it in the following query to obtain tag objects:
Tag.objects.filter(id__in=tag_ids)

But i get "invalid reference to FROM-clause" error:

Caught an exception while rendering: invalid reference to FROM-clause
entry for table "tagging_taggeditem"
LINE 1: ...RE "tagging_tag"."id" IN (SELECT U0."tag_id", MAX
("tagging_t...
^
HINT: Perhaps you meant to reference the table alias "u0".


If I force the first query to be evaluated, using the step syntax (ie
[:10:1] instead of [:10]), then everthing works fine.

Seems like a bug?

ps: I am using a recent svn version.


Thanks...

[Alex Koshelev]

What database backend do you use?

[Russell Keith-Magee]

I'm not seeing this failure on SQLite, Postgres or MySQL. However, I
was testing using my own test models, not your models - it's possible
that the problem could be caused by the interaction of some other
property of your model on the aggregate query.

Can you provide the full definition of the TaggedItem model you are
using? It would also be helpful to know the database version and
operating system you are using.

Yours,
Russ Magee %-)

[omat]

Hi,

TaggedItem model is as follows:


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


And I am using sqlite on Mac OS X with django revison 9781.


Thanks,
oMat



On Feb 1, 1:12 am, Russell Keith-Magee <freakboy3...@gmail.com> wrote:

[omat]

I just remembered that the above error occured when running on
Postgresql 8.2. Sorry for the misinformation about SQLite.

Then to give it a try with SQLite, I built a fresh database with
syncdb on SQLite.

This time, at the same point, I get a:

OperationalError: no such column: tagging_taggeditem.added


This is very strange, because the column 'added' is surely there.

[omat]

It seems like a problem with the cases of created aliases: 'u0' was
expected but 'U0' is used as the table alias.

But I couldn't find any reference to u0 or U0 when debugging. In the
query object, the table name "tagging_taggeditem" is used in the
query.

For the sake of completeness, the referenced Tag model is as follows:

class Tag(models.Model):
name = models.CharField(_('name'), 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)


Thanks...

On Feb 2, 9:46 am, omat <o...@gezgin.com> wrote:
> I just remembered that the above error occured when running on
> Postgresql 8.2. Sorry for the misinformation about SQLite.
>
> Then to give it a try with SQLite, I built a fresh database with
> syncdb on SQLite.
>
> This time, at the same point, I get a:
>
> OperationalError: no such column: tagging_taggeditem.added
>
> This is very strange, because the column 'added' is surely there.
>

[Russell Keith-Magee]

On Mon, Feb 2, 2009 at 4:46 PM, omat <om...@gezgin.com> wrote:
>
> I just remembered that the above error occured when running on
> Postgresql 8.2. Sorry for the misinformation about SQLite.
>
> Then to give it a try with SQLite, I built a fresh database with
> syncdb on SQLite.
>
> This time, at the same point, I get a:
>
> OperationalError: no such column: tagging_taggeditem.added
>
>
> This is very strange, because the column 'added' is surely there.

I can now confirm I am seeing the same problem. It appears that the
table names aren't being correctly promoted in the GROUP BY clause for
the inner query.

If you could open a ticket providing all the detail you have provided
here, I will ensure this bug gets fixed before we release v1.1

Many Thanks,
Russ Magee %-)

[omat]

I opened the ticket:
http://code.djangoproject.com/ticket/10182

Thanks.


On Feb 3, 1:25 am, Russell Keith-Magee <freakboy3...@gmail.com> wrote: