id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26336	GIS queries with distance and extra select fields generate a SQL exception in pagination	simondrabble	nobody	"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

"	Bug	closed	GIS	1.8	Normal	wontfix	gis pagination query extra		Unreviewed	0	0	0	0	0	0