#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------
Reporter: datamik | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords: queryset subquery
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/30009>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.be5b2b0dcf16303c581cf9f9e32a4561%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
