Opened 7 years ago

Closed 7 years ago

Last modified 5 years ago

#12828 closed (fixed)

Invalid SQL in GIS DB

Reported by: joliveirinha@… 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: UI/UX:


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)

fix.patch (639 bytes) - added by anonymous 7 years ago.

Download all attachments as: .zip

Change History (6)

Changed 7 years ago by anonymous

Attachment: fix.patch added

comment:1 Changed 7 years ago by joliveirinha

Has patch: set
Needs documentation: unset
Needs tests: set
Patch needs improvement: unset
Resolution: worksforme
Status: newclosed

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 file does not exists in version 1.2 beta I don't know if this bug is reproducible anymore.

Hope this helps.

comment:2 Changed 7 years ago by Russell Keith-Magee

milestone: 1.2
Resolution: worksforme
Status: closedreopened
Triage Stage: UnreviewedDesign decision needed

comment:3 Changed 7 years ago by jbronn

Resolution: fixed
Status: reopenedclosed

(In [12587]) [1.1.X] Fixed #12828 -- The table quoting function is now argument get_geo_where_clause.

comment:4 Changed 7 years ago by jbronn

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.

comment:5 Changed 5 years ago by Jacob

milestone: 1.2

Milestone 1.2 deleted

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