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
30009	Invalid SQL query when using Subquery, caused by table alias quoting.	Davit Mikava	nobody	"It seems that table aliases quoting issue reported in [https://code.djangoproject.com/ticket/27862 #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."	Bug	closed	Database layer (models, ORM)	1.11	Normal	duplicate	queryset subquery	Mariusz Felisiak	Accepted	0	0	0	0	0	0