Opened 8 years ago

Closed 6 years ago

#2091 closed defect (fixed)

IN field lookup returns all objects without filtering by the supplied list when used on ManyToMany field.

Reported by: real dot human at mrmachine dot net Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: normal Keywords: in field lookup filter manytomany m2m many, qs-rf-fixed
Cc: gabor@…, freakboy3742@…, real.human@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


using an IN field lookup in a filter on a ManyToMany field seems to return all objects. as far as i know, the two e.filter statements below should return the same queryset.

>>> e = Entry.objects.all()
>>> e
[<Entry: smooth sailing>, <Entry: wii rocks e3>, <Entry: 30thirty reboot>]

>>> t = Tag.objects.get(id=6)
>>> t
<Tag: django>

>>> t.entry_set.all()
[<Entry: smooth sailing>, <Entry: 30thirty reboot>]

>>> e.filter(tags__id__exact=6)
[<Entry: smooth sailing>, <Entry: 30thirty reboot>]

>>> e.filter(tags__id__in=[6])
[<Entry: smooth sailing>, <Entry: wii rocks e3>, <Entry: 30thirty reboot>]

Attachments (0)

Change History (9)

comment:1 Changed 8 years ago by real dot human at mrmachine dot net

here's some additional information showing the broken sql being generated.

>>> from import Category, Entry, Tag
>>> from django.db import connection
>>> Entry.objects.filter(tags__id__in = [6])
[<Entry: smooth sailing>, <Entry: wii rocks e3>, <Entry: 30thirty reboot>]
>>> print connection.queries[-1]['sql']
SELECT "blog_entry"."id","blog_entry"."created","blog_entry"."modified","blog_entry"."author_id","blog_entry"."title","blog_entry"."synopsis","blog_entry"."synopsis_html","blog_entry"."body","blog_entry"."body_html","blog_entry"."slug","blog_entry"."category_id","blog_entry"."published","blog_entry"."status","blog_entry"."enable_comments","blog_entry"."is_sticky" FROM "blog_entry" LEFT OUTER JOIN "blog_entry_tags" AS "m2m_blog_entry__tags" ON "blog_entry"."id" = "m2m_blog_entry__tags"."entry_id" INNER JOIN "blog_tag" AS "blog_entry__tags" ON "m2m_blog_entry__tags"."tag_id" = "blog_entry__tags"."id" WHERE ("blog_entry__tags"."id" IN (6)) ORDER BY "blog_entry"."published" DESC

comment:2 Changed 8 years ago by gabor@…

  • Cc gabor@… added

i had the same problem, but for me it's only happening with sqlite3.

which database did you use?
and if you used sqlite3, could you try to reproduce the problem with postgresql?

comment:3 Changed 8 years ago by russellm

  • Cc freakboy3742@… added

I see this problem as well, but only with SQLite3. It doesn't appear to be a problem with PostgreSQL.

The problem appears to be caused by the fact that m2m tables are collated using a LEFT OUTER JOIN, rather than an INNER JOIN. Using SQLite, this approach puts partially sparse rows into the combined table for those m2m entries that should be excluded from the result set. PostgreSQL omits the sparse rows, and therefore returns the correct results.

If you hand modify the query produced by the filter operation and replace the outer join with an inner join, the results return as expected.

The use of LEFT OUTER JOIN was introduced in r2613 to fix #1535; however, it has caused other problems, such as #2306.

comment:4 Changed 8 years ago by mtredinnick

  • Owner changed from adrian to mtredinnick

comment:5 Changed 8 years ago by Gábor Farkas <gabor@…>

this issue seems to be very similar to the one in #2457. maybe it's the same issue?

comment:6 Changed 7 years ago by mrmachine <real dot human at mrmachine dot net>

  • Cc real.human@… added

comment:7 Changed 7 years ago by Simon G. <dev@…>

  • Triage Stage changed from Unreviewed to Accepted

comment:8 Changed 7 years ago by mtredinnick

  • Keywords many, qs-rf-fixed added; many removed

comment:9 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

Add Comment

Modify Ticket

Change Properties
<Author field>
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.