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
14645	Exclude query with multiple conditions for the same multi-value relation not correct	Ben Buchwald <bb2@…>	nobody	"According to [http://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships]: ""all the conditions in a single exclude() statement apply to a single instance (if those conditions are talking about the same multi-valued relation)."" This works correctly for filter() but for exclude() it is operating the same as if it was 2 separate exclude() calls. Here's an example set of models:

{{{
class Song(models.Model):
   name = models.CharField(max_length=30)

class Release(models.Model):
   song = models.ForeignKey(Song)
   format = models.CharField(max_length=3,choices=(('cd',""CD""),('mp3',""MP3"")))
   released = models.BooleanField()
   class Meta:
      unique_together = ['song','format']
}}}

If I want to ask for all of the songs that have a CD release that has already gone out this filter works:

{{{Song.objects.filter(release_set__format='cd',release_set__released=1)}}}

But if I want to find all the songs that don't have a CD  release that has gone out (either it has one that hasn't been released yet, or it doesn't have a release record at all), this exclude statement is not working:

{{{Song.objects.exclude(release_set__format='cd',release_set__released=1)}}}

It produces this SQL:

{{{
('SELECT U0.`id` FROM `songs_song` U0 
  WHERE NOT (U0.`id` IN (SELECT U1.`id` FROM `songs_release` U1 
                         WHERE U1.`format` = %s) AND 
             U0.`id` IN (SELECT U1.`id` FROM `songs_release` U1 
                         WHERE U1.`released` = %s ))',
('cd', True))
}}}

Instead of what I would expect (and give me the right results):

{{{
('SELECT U0.`id` FROM `songs_song` U0 
  WHERE NOT (U0.`id` IN (SELECT U1.`id` FROM `songs_release` U1 
                         WHERE U1.`format` = %s AND 
                               U1.`released` = %s ))',
('cd', True))
}}}

{{{Song.objects.filter(~Q(release_set__format='cd',release_set__released=1))}}} produces the same result, but {{{Song.objects.exclude(Q(release_set__format='cd',release_set__released=1))}}} produces the even more wrong:

{{{
('SELECT U0.`id` FROM `songs_song` U0 INNER JOIN `songs_release` U1 ON (U0.`id` = U1.`song_id`)
  WHERE NOT ((U1.`format` = %s  AND U1.`released` = %s ))',
('cd', True))
}}}"	Bug	new	Database layer (models, ORM)	master	Normal		exclude manytomany	rma Chris PhiR_42 benkraft bugs@… Can Sarıgöl	Accepted	1	0	0	0	0	0