Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#24171 closed Bug (fixed)

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

Reported by: Abdulhaq Emhemmed Owned by: Josh Smeaton
Component: Database layer (models, ORM) Version: 1.8beta1
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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

Attachments (1)

24171-test.diff (2.6 KB ) - added by Tim Graham 10 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 by Josh Smeaton, 10 years ago

Owner: changed from nobody to Josh Smeaton
Status: newassigned

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.

comment:2 by Tim Graham, 10 years ago

Keywords: 1.8-beta added

comment:3 by Tim Graham, 10 years ago

Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

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.

by Tim Graham, 10 years ago

Attachment: 24171-test.diff added

comment:4 by Tim Graham, 10 years ago

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

comment:5 by Tim Graham, 10 years ago

Has patch: set
Keywords: 1.8-beta removed
Patch needs improvement: set
Version: 1.8alpha11.8beta1

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

comment:6 by Tim Graham <timograham@…>, 10 years ago

Resolution: fixed
Status: assignedclosed

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.

comment:7 by Tim Graham <timograham@…>, 10 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top