[Django] #24171: (1054, "Unknown column '__col1' in 'field list'") when using values, annotate and aggrregate
Django
annotate and aggrregate
----------------------------------------------+-----------------------
Reporter: abdulhaq-e | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8alpha1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+-----------------------
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!?
--
Ticket URL: <https://code.djangoproject.com/ticket/24171>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
annotate and aggrregate
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8alpha1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* owner: nobody => jarshwah
* needs_better_patch: => 0
* status: new => assigned
* needs_tests: => 0
* needs_docs: => 0
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:1>
Django
annotate and aggrregate
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8alpha1
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* keywords: => 1.8-beta
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:2>
Django
annotate and aggrregate
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8alpha1
(models, ORM) |
Keywords: 1.8-beta | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8alpha1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: 1.8-beta | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: assigned
(models, ORM) |
Severity: Release blocker | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* keywords: 1.8-beta =>
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* version: 1.8alpha1 => 1.8beta1
Comment:
[https://github.com/django/django/pull/4240 PR] from Anssi (not passing
tests as of now).
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: abdulhaq-e | Owner: jarshwah
Component: Database layer | Version: 1.8beta1
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"fb146193c49e4c683dc8da39d9b7c479375fdb57"]:
{{{
#!CommitTicketReference repository=""
revision="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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: abdulhaq-e | Owner: jarshwah
Type: Bug | Status: closed
Component: Database layer | Version: 1.8beta1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Tim Graham <timograham@…>):
In [changeset:"3a0fe942ddf56ddcf4b958147f3914fe2788db30"]:
{{{
#!CommitTicketReference repository=""
revision="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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24171#comment:7>