#27610: Unexpected behavior on chained filters
-------------------------------------+-------------------------------------
Reporter: Thomas | Owner: nobody
Recouvreux |
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords: orm filter
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Using Django 1.10.4 with psycopg engine, I got 2 different results
depending on filter chaining or not.
How to reproduce :
{{{
a = Group.objects.filter(user__date_joined__gte='2016-01-01',
user__date_joined__lt='2016-03-01').distinct().count()
b =
Group.objects.filter(user__date_joined__gte='2016-01-01').filter(user__date_joined__lt='2016-03-01').distinct().count()
if a == b:
print('as expected')
else:
print('something went wrong')
}}}
Expected result : `a == b`
Result : `a != b`
Using --print-sql I managed to extract the generated SQL.
{{{
-- SQL for a
SELECT COUNT(*)
FROM
(SELECT DISTINCT "auth_group"."id" AS Col1,
"auth_group"."name" AS Col2
FROM "auth_group"
INNER JOIN "ts_user_usr_groups" ON ("auth_group"."id" =
"ts_user_usr_groups"."group_id")
INNER JOIN "ts_user_usr" ON ("ts_user_usr_groups"."user_id" =
"ts_user_usr"."id")
WHERE ("ts_user_usr"."date_joined" <
'2016-03-01T00:00:00+00:00'::timestamptz
AND "ts_user_usr"."date_joined" >=
'2016-01-01T00:00:00+00:00'::timestamptz)) subquery
}}}
{{{
-- SQL for b
SELECT COUNT(*)
FROM
(SELECT DISTINCT "auth_group"."id" AS Col1,
"auth_group"."name" AS Col2
FROM "auth_group"
INNER JOIN "ts_user_usr_groups" ON ("auth_group"."id" =
"ts_user_usr_groups"."group_id")
INNER JOIN "ts_user_usr" ON ("ts_user_usr_groups"."user_id" =
"ts_user_usr"."id")
INNER JOIN "ts_user_usr_groups" T4 ON ("auth_group"."id" =
T4."group_id")
INNER JOIN "ts_user_usr" T5 ON (T4."user_id" = T5."id")
WHERE ("ts_user_usr"."date_joined" >=
'2016-01-01T00:00:00+00:00'::timestamptz
AND T5."date_joined" <
'2016-03-01T00:00:00+00:00'::timestamptz)) subquery
}}}
It seems that chaining filters adds an unecessary inner join on the
ts_user_usr table (`INNER JOIN "ts_user_usr" T5 ON (T4."user_id" =
T5."id")`).
--
Ticket URL: <https://code.djangoproject.com/ticket/27610>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.fd6434b039576b4f71a544f9d3573718%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
