#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 [email protected].
To post to this group, send email to [email protected].
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.
