- changed status to open
SQL issues with Message.objects.inbox.filter/get
Issue #67
resolved
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)
-
repo owner -
repo owner - changed status to resolved
Fixed in v3.3.1.
- Log in to comment
Confirmed