SQL issues with Message.objects.inbox.filter/get

After upgrading to latest Postman and Django, any nested query on Message.objects.inbox is producing invalid SQL and failing to return any result.

Best demonstrated with an example:

import os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')

import django
from postman.models import Message
from django.contrib.auth.models import User

django.setup()

u1 = User.objects.get(pk=1)
m1 = Message.objects.create(recipient=u1, body='test', moderation_status='a')

inbox1 = Message.objects.inbox(u1)

print inbox1.filter(pk=m1.pk)
# empty set; should contain m1

print inbox1.get(pk=m1.pk)
# SQLite: postman.models.DoesNotExist: Message matching query does not exist.
# PostgreSQL: LINE 1: ...ge" WHERE ("postman_message"."recipient_archived" = 1 AND "...
#                                  ^
# HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Debugging at SQL backend level, it becomes apparent that the final SQL produced in such case contains params in wrong order.

Value of of sql and params at django.db.backends.utils.CursorWrapper.execute:

SELECT
    (PM. COUNT) AS "count",
    "postman_message"."id",
    "postman_message"."subject",
    "postman_message"."body",
    "postman_message"."sender_id",
    "postman_message"."recipient_id",
    "postman_message"."email",
    "postman_message"."parent_id",
    "postman_message"."thread_id",
    "postman_message"."sent_at",
    "postman_message"."read_at",
    "postman_message"."replied_at",
    "postman_message"."sender_archived",
    "postman_message"."recipient_archived",
    "postman_message"."sender_deleted_at",
    "postman_message"."recipient_deleted_at",
    "postman_message"."moderation_status",
    "postman_message"."moderation_by_id",
    "postman_message"."moderation_date",
    "postman_message"."moderation_reason",
    "auth_user"."id",
    "auth_user"."password",
    "auth_user"."last_login",
    "auth_user"."is_superuser",
    "auth_user"."username",
    "auth_user"."first_name",
    "auth_user"."last_name",
    "auth_user"."email",
    "auth_user"."is_staff",
    "auth_user"."is_active",
    "auth_user"."date_joined"
FROM
    "postman_message"
INNER JOIN (
    SELECT
        (0) AS "count",
        "postman_message"."id"
    FROM
        "postman_message"
    WHERE
        (
            "postman_message"."recipient_archived" = % s
            AND "postman_message"."moderation_status" = % s
            AND "postman_message"."recipient_id" = % s
            AND "postman_message"."recipient_deleted_at" IS NULL
            AND "postman_message"."thread_id" IS NULL
        )
    UNION
        SELECT
            COUNT ("postman_message"."id") AS "count",
            MAX ("postman_message"."id") AS "id"
        FROM
            "postman_message"
        WHERE
            (
                "postman_message"."recipient_archived" = % s
                AND "postman_message"."moderation_status" = % s
                AND "postman_message"."recipient_id" = % s
                AND "postman_message"."recipient_deleted_at" IS NULL
                AND "postman_message"."thread_id" IS NOT NULL
            )
        GROUP BY
            "postman_message"."thread_id"
) PM ON ("postman_message"."id" = PM."id")
LEFT OUTER JOIN "auth_user" ON (
    "postman_message"."sender_id" = "auth_user"."id"
)
WHERE
    "postman_message"."id" = % s

(1, False, u'a', 1, False, u'a', 1)

The last expected parameter (for postman_message.id) is appearing first in the params list while rest of the params are getting shifted right. This completely changes the purpose of the query, which results in either SQL error (PostgreSQL backend), or empty result set (other backends).

Comments (2)