#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
-------------------------------------+-------------------------------------
Changes (by darkpixel):
* status: closed => new
* resolution: invalid =>
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.
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:3>
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.e940b700b54001478c450deef84f86aa%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
