[Django] #30009: Invalid SQL query when using Subquery, caused by table alias quoting.

[Django]

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

[Django]

#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------
Reporter: datamik | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* 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>

[Django]

#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------

Reporter: Davit Mikava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by felixxm):

I think it is a duplicate of #29214.

--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:2>

[Django]

#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------

Reporter: Davit Mikava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* cc: felixxm (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:3>

[Django]

#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------

Reporter: Davit Mikava | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:

Keywords: queryset subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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>

[Django]

#30009: Invalid SQL query when using Subquery, caused by table alias quoting.
-------------------------------------+-------------------------------------

Reporter: Davit Mikava | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 1.11
(models, ORM) |

Severity: Normal | Resolution: duplicate
Keywords: queryset subquery | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => closed
* resolution: => duplicate

--
Ticket URL: <https://code.djangoproject.com/ticket/30009#comment:5>