id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
14008	GeoDjango subquery fails with bad quoting	Matthew	nobody	"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.
"		closed	GIS	1.2		duplicate			Unreviewed	0	0	0	0	0	0