#26758: Annotate appears broken with Postgres in recent releases
-------------------------------------+-------------------------------------
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
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 on x86_64-unknown-linux-gnu, compiled
by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:7>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.5ddf625be3eda8640e39fac1f0d1c372%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
