OuterRef outputs unquoted table name in SQL query under specific circumstances

[Joey Wilhelm]

Under very specific circumstances, I am getting a SQL query with an unquoted table name, causing a crash on PostgreSQL.

models.py

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:

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:

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:

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.

[Claude Paroz]

Just applying that will trigger similar errors:

tests/expressions/models.py

@@ -11 +11 @@
     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 +32 @@
         related_name='company_point_of_contact_set',
         null=True)
 
+    class Meta:
+        db_table = 'expressions_Company'
+
     def __str__(self):
         return self.name

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 81e357a7:

Fixed #28689 -- Fixed unquoted table names in Subquery SQL when using OuterRef.

Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 11f6d435:

[1.11.x] Fixed #28689 -- Fixed unquoted table names in Subquery SQL when using OuterRef.

Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.

Backport of 81e357a7e19f35235cc998459a10213532727d4e from master

[Tim Graham <timograham@…>]

In f5184ae:

[2.0.x] Fixed #28689 -- Fixed unquoted table names in Subquery SQL when using OuterRef.

Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.

Backport of 81e357a7e19f35235cc998459a10213532727d4e from master