[Django] #26758: Annotate appears broken with Postgres in recent releases
Django
----------------------------------------------+--------------------
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: invalid
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 charettes):
* status: new => closed
* needs_better_patch: => 0
* resolution: => invalid
* needs_tests: => 0
* needs_docs: => 0
Comment:
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
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:1>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: invalid
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
FWIW I also tried to reproduce [https://github.com/charettes/django-
ticketing/commit/78ed6a345e760ea46434690e9385ae4d26fc2810 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:2>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by darkpixel):
* status: closed => new
* resolution: invalid =>
Old description:
New description:
Brief Summary
>>>
}}}
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
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Description changed by darkpixel:
Old description:
New description:
Brief Summary
>>>
}}}
The version of PostgreSQL is 9.3.12:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:4>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by darkpixel):
Updated description with version info. I'm using PostgreSQL 9.3.12.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:5>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* cc: charettes (added)
Comment:
Can you reproduce with the [https://github.com/charettes/django-
ticketing/commit/78ed6a345e760ea46434690e9385ae4d26fc2810 sample app] I
provided? Else I'm afraid you'll need to provide more details.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:6>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
Old description:
New description:
Brief Summary
>>>
}}}
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
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by darkpixel):
What details would you like? I can post a full copy of the two models
involved if you would like.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:8>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
You can create a sample app as Simon did that we can download and run to
reproduce the issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:9>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by frafra):
Replying to [comment:6 charettes]:
> Can you reproduce with the [https://github.com/charettes/django-
ticketing/commit/78ed6a345e760ea46434690e9385ae4d26fc2810 sample app] I
provided? Else I'm afraid you'll need to provide more details.
I am able to reproduce this issue using the same app.
Software used:
* Django 1.9.2
* PostgreSQL 9.0.13
{{{
Using existing test database for alias 'default'...
E
======================================================================
ERROR: test_foo (ticket_26758.tests.FooTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "ticket_26758_company.owner_id" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "ticket_26758_company"."owner_id", "ticket_26758_comp...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/frafra/Scaricati/ticket/ticket_26758/tests.py", line 10, in
test_foo
print(list(qs))
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/models/query.py", line 258, in __iter__
self._fetch_all()
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/models/query.py", line 1074, in _fetch_all
self._result_cache = list(self.iterator())
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/models/query.py", line 52, in __iter__
results = compiler.execute_sql()
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/models/sql/compiler.py", line 848, in execute_sql
cursor.execute(sql, params)
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/home/frafra/.local/lib/python3.5/site-
packages/django/db/utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/home/frafra/.local/lib/python3.5/site-
packages/django/utils/six.py", line 685, in reraise
raise value.with_traceback(tb)
File "/home/frafra/.local/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 "ticket_26758_company.owner_id"
must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "ticket_26758_company"."owner_id", "ticket_26758_comp...
^
----------------------------------------------------------------------
Ran 1 test in 0.478s
FAILED (errors=1)
Preserving test database for alias 'default'...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:10>
Django
Reporter: darkpixel | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
As comment:1 says, "Django 1.9 dropped support for PostgreSQL 9.0."
--
Ticket URL: <https://code.djangoproject.com/ticket/26758#comment:11>