#10723: ORM exclute strange
---------------------------------------------------+------------------------
Reporter: anonymous | Owner: nobody
Status: new | Milestone: 1.1
Component: Database layer (models, ORM) | Version: SVN
Resolution: | Keywords:
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Old description:
> 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 description:
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))
}}}
Comment (by mtredinnick):
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/10723#comment:2>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
