Re: [Django] #11293: Filters on aggregates lose connector
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by uwe+django@…):
* status: closed => reopened
* severity: => Normal
* type: => Bug
* version: master => 1.4
* easy: => 0
* ui_ux: => 0
* resolution: fixed =>
Comment:
I think I hit exactly this bug with Django 1.4 (from Debian 1.4-1)
{{{
>>> from django.db.models import Q, Count
>>> from django.contrib.auth.models import User
>>> q1 = Q(username='tim')
>>> q2 = Q(groups__count__gt=1)
>>> query = User.objects.annotate(Count('groups'))
>>> query.filter(q1)
[<User: tim>]
>>> query.filter(q2)
[<User: uwe>]
>>> query.filter(q1 | q2)
[]
}}}
I expected the last result to contain both, tim and uwe.
{{{
>>> query.filter(q1 | q2).query.sql_with_params()
('SELECT `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`,
`auth_user`.`password`, `auth_user`.`is_staff`, `auth_user`.`is_active`,
`auth_user`.`is_superuser`, `auth_user`.`last_login`,
`auth_user`.`date_joined`, COUNT(`auth_user_groups`.`group_id`) AS
`groups__count` FROM `auth_user` LEFT OUTER JOIN `auth_user_groups` ON
(`auth_user`.`id` = `auth_user_groups`.`user_id`) WHERE
(`auth_user`.`username` = %s ) GROUP BY `auth_user`.`id`,
`auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`,
`auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
`auth_user`.`is_active`, `auth_user`.`is_superuser`,
`auth_user`.`last_login`, `auth_user`.`date_joined` HAVING
COUNT(`auth_user_groups`.`group_id`) > %s ORDER BY NULL', ('tim', 1))
}}}
I guess the former 'fixed' marking only applies to the easier case in the
original report.
Thanks
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:20>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 1
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by uwe+django@…):
* cc: uwe+django@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:21>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
Comment:
I tested the above with
https://github.com/akaariai/django/tree/refactor_utils_tree and that patch
seems to solve this issue.
This is the SQL query I get:
{{{
SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."password",
"auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser", "auth_user"."last_login",
"auth_user"."date_joined", COUNT("auth_user_groups"."group_id") AS
"groups__count" FROM "auth_user" LEFT OUTER JOIN "auth_user_groups" ON
("auth_user"."id" = "auth_user_groups"."user_id") GROUP BY
"auth_user"."id", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."password",
"auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser", "auth_user"."last_login",
"auth_user"."date_joined" HAVING ("auth_user"."username" = tim OR
COUNT("auth_user_groups"."group_id") > 1 )
}}}
I can't test if I get the correct results, as I don't have the test data
available. But the query looks correct to me.
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:22>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
I can confirm that commit e691d86 from
https://github.com/akaariai/django/tree/refactor_utils_tree returns the
correct result for me. But note that commit
{{{
96c2eb4 (Added pre-add_q stage to sql/query.py)
}}}
broke that again. The following is returned on 96c2eb4:
{{{
'SELECT "auth_user"."id", "auth_user"."username",
"auth_user"."first_name", "auth_user"."last_name", "auth_user"."email",
"auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser", "auth_user"."last_login",
"auth_user"."date_joined", COUNT("auth_user_groups"."group_id") AS
"groups__count" FROM "auth_user" LEFT OUTER JOIN "auth_user_groups" ON
("auth_user"."id" = "auth_user_groups"."user_id") GROUP BY
"auth_user"."id", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."password",
"auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser", "auth_user"."last_login",
"auth_user"."date_joined"'
}}}
which returns '''all''' users for me. This is not surprising as it doesn't
have the "HAVING" clause and doesn't even mention "tim".
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:23>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
Are you sure about not doing some typo in the test? The result seems
really surprising to me. Here is my test results:
{{{
akaariai@akaariai-UX31E:~/Programming/django/django_test/django$ git log
-1
commit 96c2eb46b96bb0cd47e3ae7932d32401a6a28421
Author: Anssi Kääriäinen <akaa...@gmail.com>
Date: Sat Jun 2 03:40:23 2012 +0300
Added pre-add_q stage to sql/query.py
akaariai@akaariai-UX31E:~/Programming/django/django_test/django$ cd ..
akaariai@akaariai-UX31E:~/Programming/django/django_test$ cat test.py
from django.core.management import setup_environ
import settings
setup_environ(settings)
from django.db.models import Q, Count
from django.contrib.auth.models import User
q1 = Q(username='tim')
q2 = Q(groups__count__gt=1)
query = User.objects.annotate(Count('groups'))
print query.filter(q1 | q2).query
akaariai@akaariai-UX31E:~/Programming/django/django_test$ python test.py
SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."password",
"auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login", "auth_user"."date_joined",
COUNT("auth_user_groups"."group_id") AS "groups__count"
FROM "auth_user"
LEFT OUTER JOIN "auth_user_groups" ON ("auth_user"."id" =
"auth_user_groups"."user_id")
GROUP BY "auth_user"."id", "auth_user"."username",
"auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."password",
"auth_user"."is_staff", "auth_user"."is_active",
"auth_user"."is_superuser",
"auth_user"."last_login", "auth_user"."date_joined"
HAVING ("auth_user"."username" = tim OR
COUNT("auth_user_groups"."group_id") > 1 )
}}}
So, for me this seems to give the correct result.
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:24>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
Component: ORM aggregation | Version: 1.4
Severity: Normal | Resolution:
Keywords: having, where, | Triage Stage: Accepted
aggregate, connector | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by akaariai):
There was an error in the branch - the branch splits q_objects to multiple
different trees for having - where clause separation
(find_having_splits()) in the branch. This made in-place modifications to
the given q_object and this was the cause of the error. I made a change
where the q_object is used as-is in the query, except when it contains
references to aggregates, in which case it is first deepcopied, and only
then splitted to parts.
I have updated the
https://github.com/akaariai/django/tree/refactor_utils_tree branch to
contain a fix for this.
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:25>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: reopened
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: having, where, | Needs documentation: 0
aggregate, connector, annotate | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by fhahn):
* keywords: having, where, aggregate, connector => having, where,
aggregate, connector, annotate
* component: ORM aggregation => Database layer (models, ORM)
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:26>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Component: Database layer | Version: 1.4
Severity: Normal | Triage Stage: Accepted
Keywords: having, where, | Needs documentation: 0
aggregate, connector, annotate | Patch needs improvement: 0
Has patch: 1 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: reopened => closed
* resolution: => fixed
Comment:
This was fixed in d3f00bd5706b35961390d3814dd7e322ead3a9a3.
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:27>
Django
-------------------------------------+-------------------------------------
Reporter: django@… | Owner: -
Type: Bug | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) |
Keywords: having, where, | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"a46bc327e70f81b66800780edf3830f6137a89e3" a46bc32]:
{{{
#!CommitTicketReference repository=""
revision="a46bc327e70f81b66800780edf3830f6137a89e3"
Refs #11293 -- Added test for filtering aggregates with negated &
operator.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/11293#comment:28>