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