id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 26758 Annotate appears broken with Postgres in recent releases Aaron C. de Bruyn nobody "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 """", line 1, in 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." Uncategorized closed Database layer (models, ORM) 1.9 Normal needsinfo Simon Charette Unreviewed 0 0 0 0 0 0