Code

Opened 4 years ago

Closed 4 years ago

Last modified 3 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:

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)

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

Download all attachments as: .zip

Change History (6)

Changed 4 years ago by anonymous

comment:1 Changed 4 years ago by joliveirinha

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

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.

comment:2 Changed 4 years ago by russellm

  • milestone set to 1.2
  • Resolution worksforme deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Unreviewed to Design decision needed

comment:3 Changed 4 years ago by jbronn

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

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

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

  • milestone 1.2 deleted

Milestone 1.2 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.