Opened 7 years ago

Closed 5 years ago

#7126 closed (worksforme)

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

Reported by: benwalton@… Owned by: jbronn
Component: GIS Version: gis
Severity: Keywords: select_related gis oracle mysql
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (1)

gis_select_related.diff (4.5 KB) - added by jbronn 7 years ago.
Fix for selection of geometry columns when using select_related on MySQL/Oracle.

Download all attachments as: .zip

Change History (11)

comment:1 Changed 7 years ago by benwalton@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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

comment:2 Changed 7 years ago by jbronn

  • Resolution invalid deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:3 Changed 7 years ago by jbronn

  • Owner changed from nobody to jbronn
  • Status changed from reopened to new

comment:4 Changed 7 years ago by 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!

comment:5 Changed 7 years ago by jbronn

  • Has patch set
  • Keywords oracle mysql added
  • Needs tests set
  • Summary changed from select_related(field) doesn't correctly SELECT GIS fields to select_related(field) doesn't correctly SELECT GIS fields on MySQL/Oracle

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.

Changed 7 years ago by jbronn

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

comment:6 follow-up: Changed 7 years ago by 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.

comment:7 in reply to: ↑ 6 Changed 7 years ago by 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).

comment:8 Changed 7 years ago by jbronn

  • Resolution set to fixed
  • Status changed from new to closed

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.

comment:9 follow-up: Changed 5 years ago by sdonk

  • Resolution fixed deleted
  • Status changed from closed to reopened

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.

comment:10 in reply to: ↑ 9 Changed 5 years ago by jbronn

  • Resolution set to worksforme
  • Status changed from reopened to closed

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.

Note: See TracTickets for help on using tickets.
Back to Top