This happens when Django is configured with PostgreSQL backend.
to reproducing it use following models:
{{{
class Case(models.Model):
case_number = models.CharField(max_length=32)
parent_case = models.ForeignKey('self', null=True, blank=True)
class Subject(models.Model):
name = models.CharField(max_length=128)
case = models.ForeignKey(Case)
class Task(models.Model):
num = models.CharField(max_length=32, blank=True)
case = models.ForeignKey(Case, null=True, blank=True)
}}}
and then:
{{{
Task.objects.annotate(
top_case_id=Coalesce(F('case__parent_case__parent_case_id'),
F('case__parent_case_id'), F('case_id')),
subject=Subquery(Subject.objects.filter(case_id=OuterRef('top_case_id')))
).all()
}}}
Following SQL is generated:
{{{
SELECT
"test_app_task"."id",
"test_app_task"."num",
"test_app_task"."case_id",
COALESCE(T3."parent_case_id", "test_app_case"."parent_case_id",
"test_app_task"."case_id") AS "top_case_id",
(SELECT
U0."id",
U0."name",
U0."case_id"
FROM "test_app_subject" U0
WHERE U0."case_id" = (COALESCE("T3"."parent_case_id",
"test_app_case"."parent_case_id", "test_app_task"."case_id"))) AS
"subject"
FROM "test_app_task" LEFT OUTER JOIN
"test_app_case" ON ("test_app_task"."case_id" = "test_app_case"."id")
LEFT OUTER JOIN
"test_app_case" T3 ON ("test_app_case"."parent_case_id" = T3."id")
}}}
Error:
{{{
ProgrammingError: missing FROM-clause entry for table "T3"
LINE 1: ...st_app_subject" U0 WHERE U0."case_id" = (COALESCE("T3"."pare...
}}}
SQL works as expected, when quotes are removed from T3 alias.
--
Ticket URL: <https://code.djangoproject.com/ticket/30009>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
* stage: Unreviewed => Accepted
Comment:
Reproduced against latest `stable/1.11.x` and `master` at
196b420fcb0cbdd82970e2b9aea80251bde82056.
--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:1>
Comment (by felixxm):
I think it is a duplicate of #29214.
--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:2>
* cc: felixxm (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:3>
Comment (by Simon Charette):
I think you are right Felix, both use a subquery with an outer ref to an
annotate field.
I'll close this one as a duplicate of #29214 and reword its summary.
--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:4>
* status: new => closed
* resolution: => duplicate
--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:5>