id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27610	Unexpected behavior on chained filters	Thomas Recouvreux	nobody	"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"")`).
"	Bug	closed	Database layer (models, ORM)	1.10	Normal	invalid	orm filter		Unreviewed	0	0	0	0	0	0