Invalid SQL query when using Subquery, caused by table alias quoting.

[Davit Mikava]

It seems that table aliases quoting issue reported in #27862 ticket still exists.

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.

[Simon Charette]

Reproduced against latest stable/1.11.x and master at 196b420fcb0cbdd82970e2b9aea80251bde82056.

[Mariusz Felisiak]

I think it is a duplicate of #29214.

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