#26758: Annotate appears broken with Postgres in recent releases -------------------------------------+------------------------------------- Reporter: darkpixel | Owner: nobody Type: Uncategorized | Status: new Component: Database layer | Version: 1.9 (models, ORM) | Severity: Normal | Resolution: Keywords: | Triage Stage: | Unreviewed Has patch: 0 | Needs documentation: 0 Needs tests: 0 | Patch needs improvement: 0 Easy pickings: 0 | UI/UX: 0 -------------------------------------+------------------------------------- Description changed by darkpixel:
Old description: > 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* ± New description: 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* ± }}} -- -- Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:4> 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 django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/067.307cb636687ef794b3c9eac51a748279%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.