id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24171	"(1054, ""Unknown column '__col1' in 'field list'"") when using values, annotate, and aggregate"	Abdulhaq Emhemmed	Josh Smeaton	"I will try my best here to explain when this comes up:

Say I have the following test model:
{{{
class Test(models.Model):

    fieldA = models.ForeignKey(AnotherModel)
    fieldB = models.IntegerField()
}}}

I want to sum the result of multiplying `fieldB` and `fieldC` where the latter is obtained by spanning the relationship as such (for example): `fieldA__ForeignKey2__ForeignKey3__fieldC`

To do this:

{{{
Test.objects.aggregate(Sum(F(`fieldA__ForeignKey2__ForeignKey3__fieldC`)*F('fieldB')))
}}}

The above will work fine:

But I want to use the new annotations features in v1.8 to make the relationship spanning shorter:

{{{
Test.objects.annotate(fieldC=F(`fieldA__ForeignKey2__ForeignKey3__fieldC`)).aggregate(Sum(F('fieldC')*F('fieldB')))
}}}

This will also work fine. However, I want a `ValuesQuerySet` (i.e. I want some specific fields). This will NOT WORK:

{{{
Test.objects.annotate(fieldC=F(`fieldA__ForeignKey2__ForeignKey3__fieldC`)).values('fieldC', 'fieldB').aggregate(Sum(F('fieldC')*F('fieldB')))
}}}
This will give the error in the title: `(1054, ""Unknown column '__col1' in 'field list'"")`. The error is definitely to do with fieldB and here is how I proved it!:

This works:
{{{
Test.objects.annotate(fieldC=F(`fieldA__ForeignKey2__ForeignKey3__fieldC`)).values('fieldC', 'fieldB').aggregate(Sum(F('fieldC')))
}}}

And surprisingly, giving an alternate annotation to fieldB works! (of course I can't use the field name as an alias name):
{{{
Test.objects.annotate(fieldC=F(`fieldA__ForeignKey2__ForeignKey3__fieldC`), FIELDB=F('fieldB')).values('fieldC', 'FIELDB').aggregate(Sum(F('fieldC')*F('FIELDB')))
}}}
 
Is this normal behaviour!?"	Bug	closed	Database layer (models, ORM)	1.8beta1	Release blocker	fixed			Accepted	1	0	0	1	0	0