Opened 3 months ago

Closed 2 months ago

#30009 closed Bug (duplicate)

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

Reported by: Davit Mikava Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: queryset subquery
Cc: felixxm Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (5)

comment:1 Changed 3 months ago by Simon Charette

Triage Stage: UnreviewedAccepted

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

comment:2 Changed 2 months ago by felixxm

I think it is a duplicate of #29214.

comment:3 Changed 2 months ago by felixxm

Cc: felixxm added

comment:4 Changed 2 months ago 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.

comment:5 Changed 2 months ago by Simon Charette

Resolution: duplicate
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top