Opened 8 years ago

Closed 8 years ago

#27610 closed Bug (invalid)

Unexpected behavior on chained filters

Reported by: Thomas Recouvreux Owned by: nobody
Component: Database layer (models, ORM) Version: 1.10
Severity: Normal Keywords: orm filter
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (2)

comment:1 by Thomas Recouvreux, 8 years ago

Type: UncategorizedBug

comment:2 by Marten Kenbeek, 8 years ago

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top