GeoDjango subquery fails with bad quoting

[Matthew]

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.

[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.

[jbronn]

Closing as duplicate of #12828.