ORM exclute strange

[anonymous]

My models is:

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)

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:

('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

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:

('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))

[Malcolm Tredinnick]

(Reformatted description to avoid lots of side-scrolling.)

[Malcolm Tredinnick]

Are you actually seeing incorrect results returned from the database? What do you think the problem is here? I'm having trouble understanding what the bug might be and the SQL looks correct to me.

[anonymous]

I think that the correct SQL is:

('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` NOT IN (%s, %s, %s)))) 
GROUP BY `doska_type`.`id`
ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))

[Malcolm Tredinnick]

No, your SQL isn't correct here. It would not exclude all the results if, say, there were multiple Active records involved, with user_ids of 1 and 4, say. The user_id of 4 would allow that record to slip through the filter, when your queryest is asking to exclude anything with at least one record matching 1, 2 or 3 for user_id values.

Multi-valued relations are tricky like that. The SQL Django is generating is necessary here. This isn't a bug.

[Jacob]

Milestone 1.1 deleted