#26758: Annotate appears broken with Postgres in recent releases
----------------------------------------------+--------------------
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Brief Summary
The brief overview is:
* I have a second postgres database that I am reading data from.
* It's data from a closed-source ticket system we are migrating away from.
* I ran a manage.py inspectdb on it about 2 years ago and have been
pulling stats from the data since then without trouble
* The database structure looks *ugly* thanks to the closed-source
designers, don't blame me... ;)
* Upgrading from 1.8.4 to 1.9.2 broke one of the queries I was running
Query:
{{{
Company.objects.all().annotate(ticketcount=Count('srservice')).exclude(ticketcount=0).order_by('-ticketcount')
}}}
Error:
{{{
>>>
Company.objects.all().annotate(ticketcount=Count('srservice')).exclude(ticketcount=0).order_by('-ticketcount')
Traceback (most recent call last):
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "company.owner_id" must appear in the
GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "company"."owner_id", "company"."company_recid", "com...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/aaron/.pyenv/versions/3.5.1/lib/python3.5/code.py", line 91,
in runcode
exec(code, self.locals)
File "<console>", line 1, in <module>
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/models/query.py", line 234, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/models/query.py", line 258, in __iter__
self._fetch_all()
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/models/query.py", line 1074, in _fetch_all
self._result_cache = list(self.iterator())
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/models/query.py", line 52, in __iter__
results = compiler.execute_sql()
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/models/sql/compiler.py", line 848, in execute_sql
cursor.execute(sql, params)
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/utils/six.py", line 685, in reraise
raise value.with_traceback(tb)
File "/home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "company.owner_id" must appear in
the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "company"."owner_id", "company"."company_recid", "com...
^
>>>
}}}
Generated SQL from the queryset:
{{{
SELECT "company"."owner_id", "company"."company_recid",
"company"."company_id", "company"."company_name", "company"."phonenbr",
"company"."phonenbr_fax", "company"."website_url", "company"."keywords",
"company"."account_nbr", "company"."currency_id",
"company"."time_zone_recid", "company"."sic_code_id",
"company"."remit_to_recid", "company"."exchange_flag",
"company"."last_update", "company"."updated_by",
"company"."company_type_recid", "company"."company_status_recid",
"company"."tax_code_recid", "company"."currency_recid",
"company"."owner_level_recid", "company"."billable_flag",
"company"."userfield_1", "company"."userfield_2", "company"."userfield_3",
"company"."userfield_4", "company"."userfield_5", "company"."userfield_6",
"company"."userfield_7", "company"."userfield_8", "company"."userfield_9",
"company"."userfield_10", "company"."delete_flag",
"company"."date_deleted", "company"."deleted_by",
"company"."market_recid", "company"."br_option", "company"."lead_flag",
"company"."lead_source", "company"."parent_company_recid",
"company"."annual_revenue", "company"."revenue_year",
"company"."nbr_employees", "company"."ownership_type_recid",
"company"."date_entered", "company"."billing_terms_recid",
"company"."billing_delivery_recid", "company"."cm_password",
"company"."ref_contact_recid", "company"."ref_member_recid",
"company"."ref_other", "company"."internal_flag",
"company"."exchange_guid", "company"."sr_notify",
"company"."autoassign_flag", "company"."sr_signoff_recid",
"company"."nosurvey_flag", "company"."bl_invtemplate_recid",
"company"."bill_override_flag", "company"."bill_sr_flag",
"company"."bill_complete_sr_flag", "company"."bill_unapproved_sr_flag",
"company"."bill_complete_pm_flag", "company"."bill_unapproved_pm_flag",
"company"."bill_restrict_down_payment_pm_flag", "company"."approval_flag",
"company"."tax_id", "company"."exchange_href", "company"."date_acquired",
"company"."unsubscribe_flag", "company"."vendor_nbr",
"company"."iv_price_header_recid", "company"."email_cc_flag",
"company"."email_cc_address", COUNT("sr_service"."sr_service_recid") AS
"ticketcount" FROM "company" LEFT OUTER JOIN "sr_service" ON
("company"."company_recid" = "sr_service"."company_recid") GROUP BY
"company"."company_recid" HAVING NOT
(COUNT("sr_service"."sr_service_recid") = 0) ORDER BY "ticketcount" DESC
}}}
models.py only showing the two related objects and snipping about 150
useless fields
{{{
class Company(models.Model):
owner_id = models.IntegerField(blank=True, null=True)
company_recid = models.IntegerField(primary_key=True)
company_id = models.CharField(max_length=50, blank=True)
company_name = models.CharField(max_length=50, blank=True)
--snip---
class SrService(models.Model):
owner_id = models.IntegerField(blank=True, null=True)
sr_service_recid = models.IntegerField(primary_key=True)
sr_location_recid = models.ForeignKey('SrLocation',
db_column='sr_location_recid', blank=True, null=True)
company_recid = models.ForeignKey('Company',
db_column='company_recid')
--snip--
}}}
The models haven't changed since I ran inspectdb and manually set up
ForeignKeys between the objects I wanted. As far as the database is
concerned, there are no keys. Did I mention we are migrating away from
this horrible system? ;)
I talked with @jarshwah in #django and he requested I file a bug.
Attempting to run the SQL generated by the queryset directly against the
DB results in the same error from Postgres.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.66514c763fd5673ddc3ba768f18d8013%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
