#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


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:

    top_case_id=Coalesce(F('case__parent_case__parent_case_id'), F('case__parent_case_id'), F('case_id')),

Following SQL is generated:

  COALESCE(T3."parent_case_id", "test_app_case"."parent_case_id", "test_app_task"."case_id") AS "top_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")


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 22 months ago by Simon Charette

Triage Stage: UnreviewedAccepted

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

comment:2 Changed 22 months ago by felixxm

I think it is a duplicate of #29214.

comment:3 Changed 22 months ago by felixxm

Cc: felixxm added

comment:4 Changed 22 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 22 months ago by Simon Charette

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