GIS queries with distance and extra select fields generate a SQL exception in pagination

[simondrabble]

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

[Claude Paroz]

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.

[Tim Graham]

Also 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.

[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!

[simondrabble]

Also verified (for completeness) simply replacing extra() with an appropriate annotate() fixes the problem in Django 1.8