Unexpected behavior on chained filters

[Thomas Recouvreux]

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")).

[Marten Kenbeek]

This is expected and ​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.