#12828 closed (fixed)
Invalid SQL in GIS DB
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | GIS | Version: | 1.1 |
Severity: | Keywords: | invalid SQL | |
Cc: | Triage Stage: | Design decision needed | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | yes | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
GIS Db generates invalid SQL when using the GIS model query as a subquery.
a = Venue.objects.filter(point__distance_lte=(Point(0, 0), Distance(m=1))) b = Event.objects.filter(venue__in=a)
With the query set a .. all goes ok but when I use a inside b then this translates in this sql query and producing an error.
(u'SELECT "event"."id", "event"."event_id", "event"."name", "event"."description", "event"."site", "event"."venue_id", "event"."start_time", "event"."end_time", "event"."extra_data" FROM "event" WHERE "event"."venue_id" IN (SELECT U0."id" FROM "venue" U0 WHERE ST_distance_sphere("U0"."point", %s) <= 1000.0)', (<django.contrib.gis.db.backend.postgis.adaptor.PostGISAdaptor object at 0x3317510>,))
The problem is that GIS ORM bind U0 to table venue but instead using that alias inside ST_distance_sphere function it uses the quoted alias, thus producing the error.
I used this code to bypass this error and it works.
b.query.where.children[0][3].data = (b.query.where.children[0][3].data[0].replace("\"U0\"", "U0"), b.query.where.children[0][3].data[1:])
Note: only Venue Model use the GIS objects manager.
Attachments (1)
Change History (6)
by , 15 years ago
comment:1 by , 15 years ago
Has patch: | set |
---|---|
Needs tests: | set |
Resolution: | → worksforme |
Status: | new → closed |
comment:2 by , 15 years ago
milestone: | → 1.2 |
---|---|
Resolution: | worksforme |
Status: | closed → reopened |
Triage Stage: | Unreviewed → Design decision needed |
comment:3 by , 15 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
comment:4 by , 15 years ago
FYI, this bug does not affect trunk, as it was revamped appropriately when multidb merged into trunk. I kept ticket as a reminder to fix in 1.1.X, it's really a duplicate of #11741.
I have just added a fix for this bug that works for me.
I dont know if this breaks any functionality but I think not.
Anyway, there is a quote_name_unless_alias function in BaseQuery class that can be used here, but since the query.py file does not exists in version 1.2 beta I don't know if this bug is reproducible anymore.
Hope this helps.