(1054, "Unknown column '__col1' in 'field list'") when using values, annotate, and aggregate

[Abdulhaq Emhemmed]

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!?

[Josh Smeaton]

The full traceback would have been useful, but from a cursory glance this error is a bug. If you could put together a minimal test case I'll work on reproducing and seeing what can be done about it.

[Tim Graham]

Attaching a test case which reproduces the error on PostgreSQL. Note that the results of the output currently differ on SQLite depending on whether or not the values() clause is included in the query.

[Tim Graham]

​PR from Anssi (not passing tests as of now).

[Tim Graham <timograham@…>]

In fb146193c49e4c683dc8da39d9b7c479375fdb57:

Fixed #24171 -- Fixed failure with complex aggregate query and expressions

The query used a construct of qs.annotate().values().aggregate() where
the first annotate used an F-object reference and the values() and
aggregate() calls referenced that F-object.

Also made sure the inner query's select clause is as simple as possible,
and made sure .values().distinct().aggreate() works correctly.

[Tim Graham <timograham@…>]

In 3a0fe942ddf56ddcf4b958147f3914fe2788db30:

[1.8.x] Fixed #24171 -- Fixed failure with complex aggregate query and expressions

The query used a construct of qs.annotate().values().aggregate() where
the first annotate used an F-object reference and the values() and
aggregate() calls referenced that F-object.

Also made sure the inner query's select clause is as simple as possible,
and made sure .values().distinct().aggreate() works correctly.

Backport of fb146193c49e4c683dc8da39d9b7c479375fdb57 from master