Opened 9 years ago

Last modified 6 months ago

#16055 new Bug

Filtering over generic relations with TextField/CharField object_id breaks in postgres

Reported by: anonymous Owned by: nobody
Component: contrib.contenttypes Version: 1.3
Severity: Normal Keywords:
Cc: victor.van.den.elzen@…, joeri@…, mmitar@…, brian@…, Kye Russell, Kevin Wiliarty Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When you have a generic foreign key with TextField / CharField object_id, and try to use it in a filter with a model that has an integer primary key, PostgreSQL 9.0.4 errors out with this:

DatabaseError: operator does not exist: integer = text

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

A small example:

class Tag(models.Model):
    tag = models.SlugField()
    content_type = models.ForeignKey(ContentType)
    object_id = models.TextField()
    content_object = generic.GenericForeignKey()

class Animal(models.Model):
    name = models.TextField()
    tags = generic.GenericRelation(Tag)

print Animal.objects.filter(tags__tag='mammal')

I have a patch that changes Django's generic foreign key tests to also try everything with a textual object_id, which exhibits this problem.

A workaround is to create this cast as implicit in postgres:

CREATE CAST (integer AS text) WITH INOUT AS IMPLICIT

Attachments (3)

django_generic_text_id_test.diff (10.4 KB) - added by victor.van.den.elzen@… 9 years ago.
patch for tests
16055_tests.diff (2.2 KB) - added by Ramiro Morales 9 years ago.
Minimal patch for current existing tests
16055-test.diff (1.7 KB) - added by Tim Graham 5 years ago.

Download all attachments as: .zip

Change History (15)

Changed 9 years ago by victor.van.den.elzen@…

patch for tests

comment:1 Changed 9 years ago by victor.van.den.elzen@…

Cc: victor.van.den.elzen@… added

comment:2 Changed 9 years ago by joeri@…

Cc: joeri@… added

comment:3 in reply to:  description Changed 9 years ago by Ramiro Morales

Triage Stage: UnreviewedAccepted

Replying to anonymous:

When you have a generic foreign key with TextField / CharField object_id, and try to use it in a filter with a model that has an integer primary key, PostgreSQL 9.0.4 errors out with this:

DatabaseError: operator does not exist: integer = text

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

A small example:

I've verified this specific test case by modifying tests already present in the test suite (added in [12353]). See the attached patch.

I see no failure with Postgres 8.3 (the version in which cast behavior changes were introduced). Can you please test it with Postgres 9 and post the results?

Changed 9 years ago by Ramiro Morales

Attachment: 16055_tests.diff added

Minimal patch for current existing tests

comment:4 Changed 8 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:5 Changed 5 years ago by Tim Graham

Component: Database layer (models, ORM)contrib.contenttypes
Summary: Filtering over funky generic relations breaks in postgresFiltering over generic relations with TextField/CharField object_id breaks in postgres

I updated the tests to apply cleanly to master and verified the failure on PostgreSQL 9.4 at 58c7ff39fb265754fb17ab8d7f8a1401b355777b (Django 1.10.dev).

======================================================================
ERROR: test_charlink_filter (generic_relations_regress.tests.GenericRelationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/tests/generic_relations_regress/tests.py", line 57, in test_charlink_filter
    list(OddRelation1.objects.filter(clinks__title='title'))
  File "/home/tim/code/django/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/tim/code/django/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 839, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 92, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/tim/code/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: operator does not exist: integer = character varying
LINE 1: ...ON ("generic_relations_regress_oddrelation1"."id" = "generic...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Changed 5 years ago by Tim Graham

Attachment: 16055-test.diff added

comment:6 Changed 5 years ago by Tim Graham

#20271 is a duplicate with an alternate test.

comment:7 Changed 5 years ago by Mitar

Cc: mmitar@… added

comment:8 Changed 5 years ago by Brian May

Cc: brian@… added

comment:9 Changed 2 years ago by Kye Russell

Cc: Kye Russell added

comment:10 Changed 8 months ago by Kevin Wiliarty

Cc: Kevin Wiliarty added

comment:11 Changed 8 months ago by Simon Charette

If anyone is interested in addressing this issue it should be doable by adjusting Join.as_sql to add the appropriate cast when lhs_col and rhs_col's .db_type(connection) doesn't match.

https://github.com/django/django/blob/129583a0d3cf69b08d058cd751d777588801b7ad/django/db/models/sql/datastructures.py#L70-L77

Ideally most of the Cast logic would be moved to the database backend operations so Join.as_sql can rely on it instead of having to depend on db.models stuff.

A less invasive solution would be to make GenericRelation's join_field return an empty get_joining_columns and include the adapted condition in get_extra_restriction instead but that seems like this should be solved at the Join level instead since we don't offer a low level way of completely configuring join conditions; we only allow to augment them using FilteredRelation.

comment:12 Changed 6 months ago by Nekmo

I have made a hack for Django-guardian. In my case, this query is the one that fails:

from customers.models import CustomerMemberObjectPermission

CustomerMemberObjectPermission.objects.filter(service_api__service__service_name='foo')  # Raise DatabaseError

My models:

class CustomerMemberObjectPermission(UserObjectPermission):
    objects = CustomerMemberObjectPermissionManager()


class ServiceApi(Model):
    customer_permissions = GenericRelation(CustomerMemberObjectPermission, object_id_field='object_pk',
                                           related_query_name='service_api')

This is the wrong sql:

SELECT "guardian_userobjectpermission"."id", "guardian_userobjectpermission"."permission_id", "guardian_userobjectpermission"."content_type_id", "guardian_userobjectpermission"."object_pk", "guardian_userobjectpermission"."user_id" FROM "guardian_userobjectpermission" INNER JOIN "services_serviceapi" ON ("guardian_userobjectpermission"."object_pk" = "services_serviceapi"."id" AND ("guardian_userobjectpermission"."content_type_id" = 22)) INNER JOIN "services_service" ON ("services_serviceapi"."service_id" = "services_service"."id") INNER JOIN "customers_customer" ON ("services_service"."customer_id" = "customers_customer"."id") WHERE "customers_customer"."customer_code" = BCL

The error is in the inner join. Django-guardian uses a field of type string for object_pk. But my id is a integer type:

"guardian_userobjectpermission"."object_pk" = "services_serviceapi"."id"

The solution is to use a cast:

CAST("guardian_userobjectpermission"."object_pk" AS integer) =  "services_serviceapi"."id"

Although I know it is not the best, my solution has been this hack thanks to Simon Charette:

class CustomJoin(Join):
    def as_sql(self, compiler, connection):
        sql, params = super(CustomJoin, self).as_sql(compiler, connection)
        sql = sql.replace('"guardian_userobjectpermission"."object_pk"',
                          'CAST("guardian_userobjectpermission"."object_pk" AS integer)')
        return sql, params


class CustomSQLCompiler(SQLCompiler):
    def compile(self, node, select_format=False):
        if isinstance(node, Join):
            node = CustomJoin(
                node.table_name, node.parent_alias, node.table_alias, node.join_type,
                node.join_field, node.nullable, node.filtered_relation
            )
        return super(CustomSQLCompiler, self).compile(node, select_format)


class CustomQuery(Query):
    def get_compiler(self, using=None, connection=None):
        original_compiler = super(CustomQuery, self).get_compiler(using=using, connection=connection)
        return CustomSQLCompiler(original_compiler.query, original_compiler.connection, original_compiler.using)
        # return super(CustomQuery, self).get_compiler(using=using, connection=connection)


class CustomerMemberObjectPermissionQuerySet(CustomerQuerySet):
    def __init__(self, model=None, query=None, using=None, hints=None):
        if not query:
            query = CustomQuery(model)
        super(CustomerMemberObjectPermissionQuerySet, self).__init__(
            model=model, query=query, using=using, hints=hints
        )


class CustomerMemberObjectPermissionManager(CustomerManager, UserObjectPermissionManager):
    def get_queryset(self):
        return CustomerMemberObjectPermissionQuerySet(self.model, using=self._db)
Note: See TracTickets for help on using tickets.
Back to Top