Django: Query works fine with Sqlite3, don't with others Database Management System

Question

I have quite long query with 'Q()', with Sqlite3 works very well, but with postgresql or mysql I have strange error e.g. for postgresql: invalid input syntax for integer: "(" and for mySQL: Truncated incorrect DOUBLE value: '('

How could I run that query with mysql? Where is my mistake?

Here is that query:

    watchersTuple = self.getWatchers()
    tagsTuple = self.getTags()
    blockedUserTuple = self.getBlockedUser()
    blockedTagTuple = self.getBlockedTag()

    statuses = Status.objects.filter( (Q(author__in = str(watchersTuple)) | Q(tags__in = str(tagsTuple)) | Q(author = self) | Q(recipient = self)) & ~Q(author__in = str(blockedUserTuple)) & ~Q(tags__in = str(blockedTagTuple)) ).distinct()

Answer 1

You haven't posted your models, but it looks like author is an integer field, or maybe a foreign key. In which case, why are you using str on the tuple of values? You presumably want to check whether author is one of those values. So just remove the str call.

The reason it appears to work in sqlite is that sqlite ignores field types - strings and integers are treated the same. Other DBMSs are more strict.

Answer 2

There is nothing obvious looking at the query. It would be useful to compare the raw queries for the Sqlite3 and MySql. This link shows you how to switch the SQL query debugging on.