[Django] #26336: GIS queries with distance and extra select fields generate a SQL exception in pagination
Django
in pagination
------------------------------+----------------------------------------
Reporter: simondrabble | Owner: nobody
Type: Bug | Status: new
Component: GIS | Version: 1.8
Severity: Normal | Keywords: gis pagination query extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
------------------------------+----------------------------------------
Given:
{{{
from django.contrib.gis.db import models as gis
from django.contrib.gis.geos import Point
from django.db import models
POINT = Point(-104.9903, 39.7392, srid=4326)
class PagedModel(models.Model):
objects = gis.GeoManager()
name = models.CharField(max_length=64, default='Nothing')
location = gis.PointField(srid=4326, default=POINT)
}}}
and:
{{{
from django.contrib.gis.geos import Point
from django.core import paginator
def test():
point = Point(-101.214, 36.135, srid=4326)
qs = models.PagedModel.objects.all()
# Both of these modifiers are required to trigger the bug.
qs = qs.distance(point)
qs = qs.extra(select={'confidence': '0'})
pager = paginator.Paginator(qs, 1)
results = pager.page(1)
}}}
Observed:
{{{
SELECT COUNT(*) FROM (
SELECT (0) AS "confidence",
"example_pagedmodel"."id" AS Col1,
(ST_distance_sphere("example_pagedmodel"."location",
ST_GeomFromEWKB('\x0101000020e610000004560e2db24d59c0e17a14ae47114240'::bytea))
) AS "distance"
FROM "example_pagedmodel"
GROUP BY "example_pagedmodel"."id",
(0), -- Here is the problem
-- Should be a 1-based column number,
-- or the name of the column ("confidence")
(ST_distance_sphere("example_pagedmodel"."location",
ST_GeomFromEWKB('\x0101000020e610000004560e2db24d59c0e17a14ae47114240'::bytea)))
) subquery
}}}
which is exposed as
{{{
======================================================================
ERROR: test_pagination (pagebug.example.tests.PagedModelTest)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/funstuff/django/pagebug/pagebug/example/tests.py", line 48, in
test_pagination
results = pager.page(1)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/core/paginator.py", line 50, in page
number = self.validate_number(number)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/core/paginator.py", line 39, in validate_number
if number > self.num_pages:
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/core/paginator.py", line 86, in _get_num_pages
if self.count == 0 and not self.allow_empty_first_page:
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/core/paginator.py", line 72, in _get_count
self._count = self.object_list.count()
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/models/query.py", line 318, in count
return self.query.get_count(using=self.db)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/models/sql/query.py", line 466, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/models/sql/query.py", line 447, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 840, in execute_sql
cursor.execute(sql, params)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/pyenv/django1.8/lib/python2.7/site-packages/django/db/utils.py",
line 98, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/pyenv/django1.8/lib/python2.7/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
ProgrammingError: GROUP BY position 0 is not in select list
LINE 1: ..._pagedmodel" GROUP BY "example_pagedmodel"."id", (0), (ST_di...
}}}
Expected:
Pagination query completes successfully.
Looks like the 0 from the extra() clause is being used explicitly as a
column name/ alias.
I have a test project that tickles the bug at
https://github.com/simondrabble/pagebug
--
Ticket URL: <https://code.djangoproject.com/ticket/26336>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
in pagination
Reporter: simondrabble | Owner: nobody
Type: Bug | Status: new
Component: GIS | Version: 1.8
Keywords: gis pagination | Triage Stage:
query extra | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Do you have the opportunity to test with Django 1.9 and the new Distance
function?
https://docs.djangoproject.com/en/1.9/ref/contrib/gis/functions/#django.contrib.gis.db.models.functions.Distance
The legacy GeoQuerySet methods are now deprecated and unless the issue can
be reproduced with the Distance function, we probably won't fix the old
method.
--
Ticket URL: <https://code.djangoproject.com/ticket/26336#comment:1>
Django
in pagination
Reporter: simondrabble | Owner: nobody
Type: Bug | Status: closed
Component: GIS | Version: 1.8
Severity: Normal | Resolution: wontfix
Keywords: gis pagination | Triage Stage:
query extra | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => wontfix
Comment:
Also `QuerySet.extra()`
[https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra
is discouraged] and we aren't fixing bugs with its usage:
This is an old API that we aim to deprecate at some point in the future.
Use it only if you cannot express your query using other queryset methods.
If you do need to use it, please file a ticket using the `QuerySet.extra`
keyword with your use case (please check the list of existing tickets
first) so that we can enhance the QuerySet API to allow removing
`extra()`. We are no longer improving or fixing bugs for this method.
--
Ticket URL: <https://code.djangoproject.com/ticket/26336#comment:2>
Django
in pagination
Reporter: simondrabble | Owner: nobody
Type: Bug | Status: closed
Component: GIS | Version: 1.8
Severity: Normal | Resolution: wontfix
Keywords: gis pagination | Triage Stage:
query extra | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by simondrabble):
Thanks for the quick responses!
I verified the bug exists in Django 1.9 with the Distance function, but
replacing the call to extra() with an appropriate call to annotate()
solves the problem. Thanks for the pointers!
--
Ticket URL: <https://code.djangoproject.com/ticket/26336#comment:3>
Django
in pagination
Reporter: simondrabble | Owner: nobody
Type: Bug | Status: closed
Component: GIS | Version: 1.8
Severity: Normal | Resolution: wontfix
Keywords: gis pagination | Triage Stage:
query extra | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by simondrabble):
Also verified (for completeness) simply replacing extra() with an
appropriate annotate() fixes the problem in Django 1.8
--
Ticket URL: <https://code.djangoproject.com/ticket/26336#comment:4>