Opened 7 weeks ago

Closed 4 weeks ago

Last modified 4 weeks ago

#28689 closed Bug (fixed)

OuterRef outputs unquoted table name in SQL query under specific circumstances

Reported by: Joey Wilhelm Owned by: felixxm
Component: Database layer (models, ORM) Version: 1.11
Severity: Release blocker Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (1)

28689.diff (1.0 KB) - added by felixxm 4 weeks ago.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 7 weeks ago by Claude Paroz

Triage Stage: UnreviewedAccepted

Just applying that will trigger similar errors:

  • tests/expressions/models.py

    diff --git a/tests/expressions/models.py b/tests/expressions/models.py
    index 85f18fdf0e..0ace4024e9 100644
    a b class Employee(models.Model): 
    1111    lastname = models.CharField(max_length=50)
    1212    salary = models.IntegerField(blank=True, null=True)
    1313
     14    class Meta:
     15        db_table = 'expressions_Employee'
     16
    1417    def __str__(self):
    1518        return '%s %s' % (self.firstname, self.lastname)
    1619
    class Company(models.Model): 
    2932        related_name='company_point_of_contact_set',
    3033        null=True)
    3134
     35    class Meta:
     36        db_table = 'expressions_Company'
     37
    3238    def __str__(self):
    3339        return self.name

comment:2 Changed 5 weeks ago by felixxm

Owner: changed from nobody to felixxm
Status: newassigned

Changed 4 weeks ago by felixxm

Attachment: 28689.diff added

comment:3 Changed 4 weeks ago by felixxm

Has patch: set
Severity: NormalRelease blocker
Version: master1.11

comment:4 Changed 4 weeks ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In 81e357a7:

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

Regression in f48bc7c3dbd204eefb3c19016b1e4906ac26bee3.

comment:5 Changed 4 weeks ago by 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

comment:6 Changed 4 weeks ago by 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

Note: See TracTickets for help on using tickets.
Back to Top