Annotate appears broken with Postgres in recent releases

[Aaron C. de Bruyn]

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.

[Simon Charette]

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

[Simon Charette]

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.

[Aaron C. de Bruyn]

Updated description with version info. I'm using PostgreSQL 9.3.12.

[Simon Charette]

Can you reproduce with the ​sample app I provided? Else I'm afraid you'll need to provide more details.

[Aaron C. de Bruyn]

What details would you like? I can post a full copy of the two models involved if you would like.

[Tim Graham]

You can create a sample app as Simon did that we can download and run to reproduce the issue.

[Francesco Frassinelli]

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'...

[Tim Graham]

As comment:1 says, "Django 1.9 dropped support for PostgreSQL 9.0."