#26758 closed Uncategorized (needsinfo)
Annotate appears broken with Postgres in recent releases
Reported by: | Aaron C. de Bruyn | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.9 |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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.
The version of PostgreSQL is 9.3.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit.
Change History (11)
comment:1 by , 8 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
comment:2 by , 8 years ago
FWIW I also tried to reproduce against PostgreSQL 9.3 with no success and managed to reproduce against 9.0.
The simple fact that the error messages mentions "company"."owner_id"
must be in the GROUP BY
clause when "company"."company_recid"
is already part of it is a strong indicator that's the underlying issue here.
comment:3 by , 8 years ago
Description: | modified (diff) |
---|---|
Resolution: | invalid |
Status: | closed → new |
comment:4 by , 8 years ago
Description: | modified (diff) |
---|
follow-up: 10 comment:6 by , 8 years ago
Cc: | added |
---|
Can you reproduce with the sample app I provided? Else I'm afraid you'll need to provide more details.
comment:7 by , 8 years ago
Description: | modified (diff) |
---|---|
Resolution: | → needsinfo |
Status: | new → closed |
comment:8 by , 8 years ago
What details would you like? I can post a full copy of the two models involved if you would like.
comment:9 by , 8 years ago
You can create a sample app as Simon did that we can download and run to reproduce the issue.
comment:10 by , 8 years ago
Replying to charettes:
Can you reproduce with the sample app I provided? Else I'm afraid you'll need to provide more details.
I am able to reproduce this issue using the same app.
Software used:
- Django 1.9.2
- PostgreSQL 9.0.13
Using existing test database for alias 'default'... E ====================================================================== ERROR: test_foo (ticket_26758.tests.FooTests) ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/frafra/.local/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) psycopg2.ProgrammingError: column "ticket_26758_company.owner_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "ticket_26758_company"."owner_id", "ticket_26758_comp... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/frafra/Scaricati/ticket/ticket_26758/tests.py", line 10, in test_foo print(list(qs)) File "/home/frafra/.local/lib/python3.5/site-packages/django/db/models/query.py", line 258, in __iter__ self._fetch_all() File "/home/frafra/.local/lib/python3.5/site-packages/django/db/models/query.py", line 1074, in _fetch_all self._result_cache = list(self.iterator()) File "/home/frafra/.local/lib/python3.5/site-packages/django/db/models/query.py", line 52, in __iter__ results = compiler.execute_sql() File "/home/frafra/.local/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql cursor.execute(sql, params) File "/home/frafra/.local/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) File "/home/frafra/.local/lib/python3.5/site-packages/django/db/utils.py", line 95, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback) File "/home/frafra/.local/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise raise value.with_traceback(tb) File "/home/frafra/.local/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 "ticket_26758_company.owner_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "ticket_26758_company"."owner_id", "ticket_26758_comp... ^ ---------------------------------------------------------------------- Ran 1 test in 0.478s FAILED (errors=1) Preserving test database for alias 'default'...
It looks like you are using a version of PostgreSQL that is not supported by the version of Django you are using.
Django 1.9 dropped support for PostgreSQL 9.0 and takes advantage of a feature in PostgreSQL 9.1 that allows grouping by only the selected tables primary keys.
Please re-open if you're experiencing the same issue on PostgreSQL 9.1