#14008: GeoDjango subquery fails with bad quoting
---------------------------+------------------------------------------------
Reporter: Matthew | Owner: nobody
Status: new | Milestone:
Component: Uncategorized | Version: 1.2
Keywords: | Stage: Unreviewed
Has_patch: 0 |
---------------------------+------------------------------------------------
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/14008>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-upda...@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
