select_related(field) doesn't correctly SELECT GIS fields on MySQL/Oracle

[benwalton@…]

I'm not quite sure how to write a test, but I produced a simple script of what goes wrong.

I have a simple Item object which can optionally have a Location

from django.contrib.gis.db import models

class Location(models.Model):
    location = models.PointField()
    
    objects = models.GeoManager()

class Item(models.Model):
    name = models.CharField(max_length=10)
    location = models.ForeignKey(Location, null=True)
    
    objects = models.GeoManager()

Setup some data

from django.contrib.gis.geos import Point
from test.models import Location, Item

item = Item(name="Place 1")
item.save()
item = Item(name="Place 2")
location = Location(location=Point(1,1))
location.save()
item.location = location
item.save()

When I query the Items with select_related() everything works fine

>>> for i in Item.objects.select_related():
...     if i.location:
...         i.location.location
... 
<Point object at 0x21ad784>
>>> print connection.queries[-1]['sql']
SELECT `test_location`.`id`, AsText(`test_location`.`location`) FROM `test_location` WHERE `test_location`.`id` = 1 

When I specify select_related('location') to do a LEFT JOIN onto location, the query isn't formed properly

>>> for i in Item.objects.select_related('location'):
...     if i.location:
...         i.location.location
... 
Traceback (most recent call last):
  File "<console>", line 3, in <module>
  File "/opt/local/lib/python2.5/site-packages/django/contrib/gis/db/models/proxy.py", line 36, in __get__
    geom = self._klass(geom_value)
  File "/opt/local/lib/python2.5/site-packages/django/contrib/gis/geos/base.py", line 72, in __init__
    raise ValueError('String or unicode input unrecognized as WKT EWKT, and HEXEWKB.')
ValueError: String or unicode input unrecognized as WKT EWKT, and HEXEWKB.
>>> print connection.queries[-1]['sql']
SELECT `test_item`.`id`, `test_item`.`name`, `test_item`.`location_id`, `test_location`.`id`, `test_location`.`location` FROM `test_item` LEFT OUTER JOIN `test_location` ON (`test_item`.`location_id` = `test_location`.`id`)

I understand that this is a new feature rather than an old one that is broken.

[benwalton@…]

Sorry, I think this might be something else as it doesn't work with a related-select_related on a pre qsrf chechout.

[jbronn]

Yes this is a bug when specifying a geometry field for select_related on MySQL and/or Oracle backends. This is also a new feature added by the queryset-refactor changes, and thus this functionality was not present in trunk.

The problem stems from the fact that Oracle and MySQL need to add a wrapper (AsText) around SELECT SQL. This isn't happening for the fields specified by select_related. In order to achieve this functionality GeoQuery will need to override at least get_columns and possibly fill_related_selections. This one may take a while.

[benwalton@…]

I think it existed before... I've checked out revision 7476 and used a related field, and not passed any arguments to select_related()

class Location(models.Model):
    location = models.PointField()
    
    objects = models.GeoManager()

class Item(models.Model):
    name = models.CharField(max_length=10)
    location = models.ForeignKey(Location)
    
    objects = models.GeoManager()

data

item = Item(name="Place 1")
location = Location(location=Point(1,1))
location.save()
item.location = location
item.save()
item = Item(name="Place 2")
location = Location(location=Point(2,2))
location.save()
item.location = location
item.save()

queries

>>> for i in Item.objects.select_related():
...     if i.location:
...         i.location.location
... 
Traceback (most recent call last):
  File "<console>", line 3, in <module>
  File "/opt/local/lib/python2.5/site-packages/django/contrib/gis/db/models/proxy.py", line 36, in __get__
    geom = self._klass(geom_value)
  File "/opt/local/lib/python2.5/site-packages/django/contrib/gis/geos/base.py", line 72, in __init__
    raise ValueError('String or unicode input unrecognized as WKT EWKT, and HEXEWKB.')
ValueError: String or unicode input unrecognized as WKT EWKT, and HEXEWKB.
>>> print connection.queries[-1]['sql']
SELECT `test_item`.`id`,`test_item`.`name`,`test_item`.`location_id`,`test_location`.`id`,`test_location`.`location` FROM `test_item` , `test_location` WHERE `test_item`.`location_id` = `test_location`.`id`

This is why I thought it wasn't a bug as it was there before the merge, I just never noticed it before!

[jbronn]

It's a bug regardless; fortunately its limited in scope in only affecting MySQL/Oracle when using select_related on Geographic fields.

Attached is a patch which fixes the problem -- only get_columns needed to be overridden. I don't know when I'll get around to committing it because I want to have unit tests.

[jbronn]

Fix for selection of geometry columns when using select_related on MySQL/Oracle.

[benwalton@…]

I'll be more than happy to write some tests for this. I'll just need a bit of pointing in the right direction as to what things to test for etc.

[anonymous]

Replying to benwalton@gmail.com:

I'll be more than happy to write some tests for this. I'll just need a bit of pointing in the right direction as to what things to test for etc.

The GeoDjango [browser:django/branches/gis/django/contrib/gis/tests tests folder] contains all tests. Since we want to test geographic model functionality a test application directory should be created (e.g., the existing ones are geoapp, layermap, and distapp) which can have the Item and Location models in a models.py. Create a file in the new application directory (say srapp) called tests_mysql.py (see the geoapp [browser:django/branches/gis/django/contrib/gis/tests/geoapp/tests_mysql.py tests] for an example) and add the tests there. Finally add add the test application name ('srapp') to the test_models variable for GeoDjango's ​test runner (read that code to see how to run the GeoDjango test suite).

[jbronn]

fixed: (In [7512]) gis: Fixed #7126 (with tests); moved GeoQuery and GeoWhereNode into sql submodule; the GeoQuerySet.transform may now be used on geometry fields related via foreign key.

[sdonk]

I'm getting the same error using Python2.6, Django 1.2.4 and MySQL 5.1.49

Traceback (most recent call last):
  File "<console>", line 2, in <module>
  File "/usr/local/lib/python2.6/dist-packages/django/contrib/gis/db/models/proxy.py", line 38, in __get__
    geom = self._klass(geom_value)
  File "/usr/local/lib/python2.6/dist-packages/django/contrib/gis/geos/geometry.py", line 73, in __init__
    raise ValueError('String or unicode input unrecognized as WKT EWKT, and HEXEWKB.')
ValueError: String or unicode input unrecognized as WKT EWKT, and HEXEWKB.

[jbronn]

Replying to sdonk:

I'm getting the same error using Python2.6, Django 1.2.4 and MySQL 5.1.49

I'm unable to reproduce. Ensure your models have objects = models.GeoManager() and that there's no corrupt data in your MySQL geometry columns.

A similar error message does not imply that this bug has regressed -- the current test suite ​checks for (and passes on) the conditions described in this ticket. If think your problem is still a bug, open up a new ticket with clear instructions and code describing how to reproduce.