Opened 5 years ago

Last modified 7 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@… 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@… 5 years ago.
patch for tests
16055_tests.diff (2.2 KB) - added by ramiro 5 years ago.
Minimal patch for current existing tests
16055-test.diff (1.7 KB) - added by timgraham 10 months ago.

Download all attachments as: .zip

Change History (11)

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

patch for tests

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

  • Cc victor.van.den.elzen@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 5 years ago by joeri@…

  • Cc joeri@… added

comment:3 in reply to: ↑ description Changed 5 years ago by ramiro

  • Triage Stage changed from Unreviewed to Accepted

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 5 years ago by ramiro

Minimal patch for current existing tests

comment:4 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:5 Changed 10 months ago by timgraham

  • Component changed from Database layer (models, ORM) to contrib.contenttypes
  • Summary changed from Filtering over funky generic relations breaks in postgres to Filtering 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 10 months ago by timgraham

comment:6 Changed 10 months ago by timgraham

#20271 is a duplicate with an alternate test.

comment:7 Changed 7 months ago by mitar

  • Cc mmitar@… added

comment:8 Changed 7 months ago by brian

  • Cc brian@… added
Note: See TracTickets for help on using tickets.
Back to Top