Code

Opened 4 years ago

Closed 4 years ago

#14008 closed (duplicate)

GeoDjango subquery fails with bad quoting

Reported by: Matthew Owned by: nobody
Component: GIS Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

This code (adapted from source code of http://github.com/mysociety/mapit ):

areas = Area.objects.filter(
  polygons__polygon__contains = location,
  generation_low__lte = 13,
  generation_high__gte = 13
)
Code.objects.filter( area__in = areas )

generates the following SQL (on a PostGIS database):

SELECT "areas_code"."id", "areas_code"."area_id", "areas_code"."type", "areas_code"."code"
FROM "areas_code"
WHERE "areas_code"."area_id" IN (
  SELECT U0."id" FROM "areas_area" U0
  INNER JOIN "areas_geometry" U1 ON (U0."id" = U1."area_id")
  WHERE (ST_Contains("U1"."polygon", ST_GeomFromWKB('\\001\\001\\000\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A', 27700))
  AND U0."generation_high_id" >= 13 AND U0."generation_low_id" <= 13 )
);

which gives this error:

ERROR:  missing FROM-clause entry in subquery for table "U1"
LINE 1: ...U1 ON (U0."id" = U1."area_id") WHERE (ST_Contains("U1"."poly...

It appears that the "U1" must not be quoted in the first argument to ST_Contains, as then it works fine:

SELECT "areas_code"."id", "areas_code"."area_id", "areas_code"."type", "areas_code"."code"
FROM "areas_code"
WHERE "areas_code"."area_id" IN (
  SELECT U0."id" FROM "areas_area" U0
  INNER JOIN "areas_geometry" U1 ON (U0."id" = U1."area_id")
  WHERE (ST_Contains(U1."polygon", ST_GeomFromWKB('\\001\\001\\000\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A', 27700))
  AND U0."generation_high_id" >= 13 AND U0."generation_low_id" <= 13 )
);
  id   | area_id |  type   |  code  
-------+---------+---------+--------
 14532 |    9498 | unit_id | 148
 14531 |    9498 | ons     | 00CUGA
 44890 |   65890 | unit_id | 24640
 17586 |   11809 | unit_id | 41426
  2324 |    2546 | unit_id | 72
  2323 |    2546 | ons     | 00CU
(6 rows)

I did look at the code to try and see what does the quoting or inner joining, but I'm afraid I couldn't work it out, sorry.

Attachments (0)

Change History (3)

comment:1 Changed 4 years ago by jbronn

  • Component changed from Uncategorized to GIS
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 4 years ago by jbronn

What version of Django are you using? This is very similar to #11741 & #12828, which were both fixed a while ago. Using trunk, SQL that's generated for me is:

SELECT "bugs_code"."id", "bugs_code"."area_id", "bugs_code"."type", "bugs_code"."code" FROM "bugs_code" WHERE "bugs_code"."area_id" IN (SELECT U0."id" FROM "bugs_area" U0 INNER JOIN "bugs_geometry" U1 ON (U0."id" = U1."area_id") WHERE (ST_Contains(U1."polygon", ST_GeomFromEWKB(E'\\001\\001\\000\\000 4l\\000\\000\\000\\000\\000\\000\\000j\\030A\\000\\000\\000\\000\\200O\\022A'::bytea)) AND U0."generation_low_id" <= 13  AND U0."generation_high_id" >= 13 ))

Notice U1 is not quoted. Will close this as a duplicate unless I'm convinced otherwise.

comment:3 Changed 4 years ago by jbronn

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

Closing as duplicate of #12828.

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.