[Django] #27610: Unexpected behavior on chained filters
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm filter | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/27610#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.10
(models, ORM) |
Keywords: orm filter | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
This is expected and
[https://docs.djangoproject.com/en/1.10/topics/db/queries/#spanning-multi-
valued-relationships documented] behaviour:
> To handle both of these situations, Django has a consistent way of
processing filter() calls. Everything inside a single filter() call is
applied simultaneously to filter out items matching all those
requirements. Successive filter() calls further restrict the set of
objects, but for multi-valued relations, they apply to any object linked
to the primary model, not necessarily those objects that were selected by
an earlier filter() call.
--
Ticket URL: <https://code.djangoproject.com/ticket/27610#comment:2>