[Django] #28689: OuterRef outputs unquoted table name in SQL query under specific circumstances
Django
circumstances
-------------------------------------+-------------------------------------
Reporter: Joey | Owner: nobody
Wilhelm |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Under very specific circumstances, I am getting a SQL query with an
unquoted table name, causing a crash on PostgreSQL.
**models.py**
{{{#!python
from django.contrib.contenttypes.fields import GenericForeignKey
from django.contrib.contenttypes.models import ContentType
from django.db import models
class PrimaryModel(models.Model):
name = models.CharField(max_length=255)
class Meta:
db_table = 'PrimaryModel'
class GenericRelatedModel(models.Model):
name = models.CharField(max_length=255)
content_type = models.ForeignKey(ContentType,
on_delete=models.CASCADE)
object_id = models.PositiveIntegerField()
content_object = GenericForeignKey()
class Meta:
db_table = 'GenericRelatedModel'
}}}
Then when running the following code:
{{{#!python
from django.contrib.contenttypes.models import ContentType
from django.db import connection
from django.db.models import Exists, OuterRef
from testapp.models import PrimaryModel, GenericRelatedModel
PrimaryModel.objects.get_or_create(name='foo')
generic = GenericRelatedModel.objects.filter(object_id=OuterRef('id'),
content_type=ContentType.objects.get_for_model(PrimaryModel))
primary = PrimaryModel.objects.annotate(generic_exists=Exists(generic))
print(primary)
}}}
I encounter the following error:
{{{#!python
Traceback (most recent call last):
File ".virtualenvs/unquoted_outerref/lib/python3.6/site-
packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: missing FROM-clause entry for table
"primarymodel"
LINE 1: ..."GenericRelatedModel" U0 WHERE (U0."object_id" = (PrimaryMod...
^
}}}
Looking at the generated SQL, I see that the table name, "PrimaryModel",
is left unquoted inside the EXISTS query:
{{{#!sql
SELECT "PrimaryModel"."id", "PrimaryModel"."name", EXISTS(SELECT U0."id",
U0."name", U0."content_type_id", U0."object_id" FROM "GenericRelatedModel"
U0 WHERE (U0."object_id" = (PrimaryModel."id") AND U0."content_type_id" =
8)) AS "generic_exists" FROM "PrimaryModel" LIMIT 21
}}}
This doesn't cause any problems in SQLite, but I get a crash when using
PostgreSQL due to its case sensitivity.
This bug does not surface if referencing a normal foreign key; it appears
to be related to the fact of relating the PositiveIntegerField (object_id)
to the OuterRef.
This bug occurs both in the latest 1.11, and in 2.0a1.
If necessary, I can provide a sample project with all of this code.
--
Ticket URL: <https://code.djangoproject.com/ticket/28689>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Just applying that will trigger similar errors:
{{{#!patch
diff --git a/tests/expressions/models.py b/tests/expressions/models.py
index 85f18fdf0e..0ace4024e9 100644
--- a/tests/expressions/models.py
+++ b/tests/expressions/models.py
@@ -11,6 +11,9 @@ class Employee(models.Model):
lastname = models.CharField(max_length=50)
salary = models.IntegerField(blank=True, null=True)
+ class Meta:
+ db_table = 'expressions_Employee'
+
def __str__(self):
return '%s %s' % (self.firstname, self.lastname)
@@ -29,6 +32,9 @@ class Company(models.Model):
related_name='company_point_of_contact_set',
null=True)
+ class Meta:
+ db_table = 'expressions_Company'
+
def __str__(self):
return self.name
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:1>
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => felixxm
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:2>
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "28689.diff" added.
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* version: master => 1.11
* severity: Normal => Release blocker
Comment:
[https://github.com/django/django/pull/9287 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:3>
Django
circumstances
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"81e357a7e19f35235cc998459a10213532727d4e" 81e357a7]:
{{{
#!CommitTicketReference repository=""
revision="81e357a7e19f35235cc998459a10213532727d4e"
Fixed #28689 -- Fixed unquoted table names in Subquery SQL when using
OuterRef.
Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:4>
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"11f6d435667dd28db0e861db55e51f53c28862d2" 11f6d435]:
{{{
#!CommitTicketReference repository=""
revision="11f6d435667dd28db0e861db55e51f53c28862d2"
[1.11.x] Fixed #28689 -- Fixed unquoted table names in Subquery SQL when
using OuterRef.
Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.
Backport of 81e357a7e19f35235cc998459a10213532727d4e from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:5>
Django
circumstances
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"f5184ae5dc370892d1b96576f68962b2c1c6c979" f5184ae]:
{{{
#!CommitTicketReference repository=""
revision="f5184ae5dc370892d1b96576f68962b2c1c6c979"
[2.0.x] Fixed #28689 -- Fixed unquoted table names in Subquery SQL when
using OuterRef.
Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.
Backport of 81e357a7e19f35235cc998459a10213532727d4e from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28689#comment:6>