Opened 2 years ago

Last modified 8 days ago

#20271 new Bug

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 timgraham)

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 8 days ago.

Download all attachments as: .zip

Change History (11)

comment:1 Changed 2 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to needsinfo
  • Status changed from new to closed

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 2 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 2 years ago by arctelix@…

  • Resolution needsinfo deleted
  • Status changed from closed to new

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

comment:4 Changed 2 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 2 years ago by akaariai

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 2 years ago by akaariai

  • Resolution set to needsinfo
  • Status changed from new to closed

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 8 days ago by charettes

  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.4 to master

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 8 days ago by charettes

  • Resolution needsinfo deleted
  • Status changed from closed to new

comment:9 Changed 8 days ago by timgraham

  • Description modified (diff)

comment:10 Changed 8 days ago by Shanto

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