Opened 15 years ago

Closed 15 years ago

Last modified 12 years ago

#10723 closed (invalid)

ORM exclute strange

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Malcolm Tredinnick)

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

Change History (5)

comment:1 by Malcolm Tredinnick, 15 years ago

Description: modified (diff)
milestone: 1.1
Triage Stage: UnreviewedAccepted

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

comment:2 by Malcolm Tredinnick, 15 years ago

Description: modified (diff)

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.

comment:3 by anonymous, 15 years ago

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

comment:4 by Malcolm Tredinnick, 15 years ago

Resolution: invalid
Status: newclosed

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.

comment:5 by Jacob, 12 years ago

milestone: 1.1

Milestone 1.1 deleted

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