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:

14:58:16 1d [aaron@praxis:~/code/-redacted-] [-redacted-] develop* ± python manage.py dbshell
WARNING 2016-06-15 14:58:22,113 __init__ 17636 140073877276480 /home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-packages/favicon/urls.py:6: RemovedInDjango110Warning: django.conf.urls.patterns() is deprecated and will be removed in Django 1.10. Update your urlpatterns to be a list of django.conf.urls.url() instances instead.
  url(r'^favicon\.ico$', RedirectView.as_view(url=conf.FAVICON_PATH, permanent=True), name='favicon'),

WARNING 2016-06-15 14:58:22,115 remote 17636 140073877276480 /home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-packages/raven/conf/remote.py:67: UserWarning: Transport selection via DSN is deprecated. You should explicitly pass the transport class to Client() instead.
  warnings.warn('Transport selection via DSN is deprecated. You should explicitly pass the transport class to Client() instead.')

WARNING 2016-06-15 14:58:23,836 __init__ 17636 140073877276480 /home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-packages/site_basics/urls.py:11: RemovedInDjango110Warning: django.conf.urls.patterns() is deprecated and will be removed in Django 1.10. Update your urlpatterns to be a list of django.conf.urls.url() instances instead.
  url(r'^test_page_500/$', page_500, name="page_500"),

WARNING 2016-06-15 14:58:24,717 __init__ 17636 140073877276480 /home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-packages/django/db/models/fields/__init__.py:1393: RuntimeWarning: DateTimeField SrService.date_entered received a naive datetime (2016-06-08 00:00:00) while time zone support is active.
  RuntimeWarning)

WARNING 2016-06-15 14:58:25,024 __init__ 17636 140073877276480 /home/aaron/.virtualenvs/-redacted-/lib/python3.5/site-packages/haystack/urls.py:15: RemovedInDjango110Warning: django.conf.urls.patterns() is deprecated and will be removed in Django 1.10. Update your urlpatterns to be a list of django.conf.urls.url() instances instead.
  url(r'^$', SearchView(), name='haystack_search'),

psql (9.3.12)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

-redacted-=> select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 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
(1 row)

-redacted-=> \q
14:58:40 1d [aaron@praxis:~/code/-redacted-] [-redacted-] develop* ± 

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