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
10723	ORM exclute strange	anonymous	nobody	"My models is:
{{{
#!python
class Type(models.Model):
    id = models.AutoField(primary_key=True)
    slug = models.SlugField(unique=True, max_length=10)
    title = models.CharField(max_length=20)
    weight = models.SmallIntegerField(default=0, db_index=True)

class Active(models.Model):
    post = models.ForeignKey(Post, related_name='activeposts')
    rubrica = models.ForeignKey(Rubrica, related_name='activeposts')
    type = models.ForeignKey(Type, related_name='activeposts')
    user_id = models.PositiveIntegerField()
    pub_date = models.DateTimeField(default=datetime.now)
}}}


{{{
#!python
Type.objects.filter(Q(activeposts__rubrica__pk=112) & \
   Q(activeposts__user_id__in=[1,2,3])). \
   annotate(count=Count('activeposts')).query.as_sql()
}}}

Result is correct:

{{{
#!sql
('SELECT `doska_type`.`id`, `doska_type`.`slug`, 
`doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count` 
FROM `doska_type` LEFT OUTER JOIN `doska_active_new`
      ON (`doska_type`.`id` = `doska_active_new`.`type_id`)
WHERE (`doska_active_new`.`rubrica_id` = %s
   AND `doska_active_new`.`user_id` IN (%s, %s, %s))
GROUP BY `doska_type`.`id`
ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
}}}
But
{{{
#!python
Type.objects.filter(Q(activeposts__rubrica__pk=112) & \
~Q(activeposts__user_id__in=[1,2,3])). \
annotate(count=Count('activeposts')).query.as_sql()
}}}

Result is not correct:

{{{
#!sql
('SELECT `doska_type`.`id`, `doska_type`.`slug`, 
`doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count` 
FROM `doska_type` LEFT OUTER JOIN `doska_active_new`
   ON (`doska_type`.`id` = `doska_active_new`.`type_id`)
WHERE (`doska_active_new`.`rubrica_id` = %s
   AND NOT (`doska_type`.`id` IN (
      SELECT U1.`type_id` 
      FROM `doska_active_new` U1 
      WHERE U1.`user_id` IN (%s, %s, %s)))) 
GROUP BY `doska_type`.`id`
ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
}}}
"		new	Database layer (models, ORM)	dev					Accepted	0	0	0	0	0	0