Opened 4 years ago

Closed 23 months ago

Last modified 23 months ago

#20271 closed Bug (duplicate)

Filtering over generic relations with TextField/CharField object_id gives PostgreSQL error: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Reported by: arctelix@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: postgresql, pk
Cc: shanto@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

It seems this error is still around!

Traceback (most recent call last):

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 202, in wrapper
    response = callback(request, *args, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 441, in dispatch_list
    return self.dispatch('list', request, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 473, in dispatch
    response = method(request, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 1244, in get_list
    to_be_serialized = paginator.page()

  File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 186, in page
    count = self.get_count()

  File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 118, in get_count
    return self.objects.count()

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/query.py", line 351, in count
    return self.query.get_count(using=self.db)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 418, in get_count
    number = obj.get_aggregation(using=using)[None]

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 384, in get_aggregation
    result = query.get_compiler(using).execute_sql(SINGLE)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
    cursor.execute(sql, params)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute
    return self.cursor.execute(query, args)

DatabaseError: operator does not exist: integer = text LINE 1: ... INNER JOIN "django_comments" ON ("pins_pin"."id" = "django_...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Attachments (1)

generic_relations_regress-test_textlink_join.patch (942 bytes) - added by Shanto 2 years ago.

Download all attachments as: .zip

Change History (13)

comment:1 Changed 4 years ago by Russell Keith-Magee

Resolution: needsinfo
Status: newclosed

It's difficult to fix "This error" if you don't tell us what "this error" is. A stack trace by itself doesn't help - we need sample code and/or an explanation to demonstrate how to reproduce the problem.

comment:2 Changed 4 years ago by arctelix@…

The issue seems identical to https://code.djangoproject.com/ticket/6523. I am using the latest build of tastypie & using heroku postgreSQL The code that generates the error is in one of my my api resources. The error is generated by the line:

orm_filters['comments__user__id__exact'] = filters['cmnts']

In the below code:

class PinResource(ModelResource):    

    comments = fields.ToManyField('pinry.api.api.CmntResource', 'comments', full=True, null=True)

    def build_filters(self, filters=None):
        if filters is None:
            filters = {}

        orm_filters = super(PinResource, self).build_filters(filters)

        if 'cmnts' in filters:
            orm_filters['comments__user__id__exact'] = filters['cmnts']

class CmntResource(ModelResource):
    user = fields.ToOneField('my.api.api.UserResource', 'user', full=True)
    site_id = fields.CharField(attribute = 'site_id')
    content_object = GenericForeignKeyField({
        Pin: PinResource,
    }, 'content_object')
    username = fields.CharField(attribute = 'user__username', null=True)
    user_id = fields.CharField(attribute = 'user__id', null=True)

The CommentResource is based on the built in django-comments model. It apears the error is due to the fact that the object_pk is defined as models.TextField(_('object ID')) in the model and PostgreSQL requires it to be cast as in integer since the commented object's pk is an integer.

Let me know if you need more.

comment:3 Changed 4 years ago by arctelix@…

Resolution: needsinfo
Status: closednew

Also the query set for the comment resource is: queryset = Comment.objects.all()

comment:4 Changed 4 years ago by arctelix@…

I managed to work around the limitation caused by the object_id in django-comments being a text field as follows:

if 'cmnts' in filters:
    comments = Comment.objects.filter(user__id=filters['cmnts'], content_type__name = 'my',   site_id=settings.SITE_ID ).values_list('object_pk', flat=True)
    comments = [int(c) for c in comments]
    orm_filters['pk__in'] = comments

However, it would be great if django implimented someting similar to what has been done here:
https://github.com/coleifer/django-generic-aggregation/blob/master/generic_aggregation/utils.py

comment:5 Changed 4 years ago by Anssi Kääriäinen

Could you produce a test case using standard django models?

I think the problem is that there is need for casting the id to text in the join condition, so that object_id and the id field's types match. This doesn't seem to be easy to do, as currently only direct = conditions in joins are supported.

comment:6 Changed 4 years ago by Anssi Kääriäinen

Resolution: needsinfo
Status: newclosed

I think I will need to close this as needsinfo. I can't reproduce this, and until there is a way to reproduce this there is little we can do to fix anything. The other option is to mark this as accepted without any way to actually do anything to the ticket. So, picking what I assume to be lesser of the two evils...

comment:7 Changed 2 years ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 1.4master

Managed to reproduce with the provided patch against master.

As Anssi said this looks hard to fix but I'm tentatively accepting.

comment:8 Changed 2 years ago by Simon Charette

Resolution: needsinfo
Status: closednew

comment:9 Changed 2 years ago by Tim Graham

Description: modified (diff)

comment:10 Changed 2 years ago by Shanto

Cc: shanto@… added

comment:11 Changed 23 months ago by Tim Graham

Resolution: duplicate
Status: newclosed

Looks like a duplicate of #16055.

comment:12 Changed 23 months ago by Tim Graham

Summary: PostgreSQL error: No operator matches the given name and argument type(s). You might need to add explicit type casts.Filtering over generic relations with TextField/CharField object_id gives PostgreSQL error: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Note: See TracTickets for help on using tickets.
Back to Top