#26336: GIS queries with distance and extra select fields generate a SQL
exception
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.
--
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/055.02adccf175b1569bee97db11cf5b40a4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
