[Django] #33050: calling count on union of queries having select_related results in error.
Django
-------------------------------------+-------------------------------------
Reporter: Sunkyue- | Owner: nobody
Kim |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: orm, count, union,
Severity: Normal | select_related
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
test case:
{{{
class ModelA(models.Model):
value = models.IntegerField()
class ModelB(models.Model):
model_a = models.ForeignKey("ModelA", on_delete=models.CASCADE)
}}}
->
{{{
ModelB.objects.select_related('model_a').union(ModelB.objects.select_related('model_a')).count()
}}}
results in error.
{{{
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\models\query.py", line 412, in count
return self.query.get_count(using=self.db)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\models\sql\query.py", line 521, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\models\sql\query.py", line 506, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\models\sql\compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\utils.py", line 98, in execute
return super().execute(sql, params)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\django\db\backends\mysql\base.py", line 73, in execute
return self.cursor.execute(query, args)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\MySQLdb\cursors.py", line 206, in execute
res = self._query(query)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\MySQLdb\cursors.py", line 319, in _query
db.query(q)
File "c:\Users\user\Documents\workspace\realclass2-api\.venv\lib\site-
packages\MySQLdb\connections.py", line 259, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1060, "Duplicate column name 'id'")
}}}
suggested fix:
on django.db.models.sql.query.py -> Query.get_aggregation, from
{{{
inner_query.select_for_update = False
inner_query.select_related = False
inner_query.set_annotation_mask(self.annotation_select)
}}}
to
{{{
inner_query.select_for_update = False
inner_query.select_related = False
for combined_query in inner_query.combined_queries:
combined_query.select_related = False
inner_query.set_annotation_mask(self.annotation_select)
}}}
can solve the problem I think.
--
Ticket URL: <https://code.djangoproject.com/ticket/33050>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage:
select_related | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Sunkyue-Kim):
* easy: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage:
select_related | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Description changed by Sunkyue-Kim:
Old description:
New description:
reproduced on on 3.2.5
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage:
select_related | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Description changed by Sunkyue-Kim:
Old description:
> test case:
> reproduced on on 3.2.5
New description:
reproduced on on 3.2.5/3.2.6
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage:
select_related | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Description changed by Sunkyue-Kim:
Old description:
> test case:
> reproduced on on 3.2.5/3.2.6
New description:
my current monkey-patching code is,
{{{
from django.db.models.sql.query import Query
old_get_aggregation = Query.get_aggregation
def get_aggregation(self, using, added_aggregate_names):
original_select_related_values = {}
for combined_query in self.combined_queries:
original_select_related_values[combined_query] =
combined_query.select_related
combined_query.select_related = False
result = old_get_aggregation(self, using, added_aggregate_names)
for combined_query in self.combined_queries:
combined_query.select_related =
original_select_related_values[combined_query]
return result
Query.get_aggregation = get_aggregation
}}}
reproduced on on 3.2.5/3.2.6
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage:
select_related | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Description changed by Sunkyue-Kim:
Old description:
> test case:
> my current monkey-patching code is,
> {{{
> from django.db.models.sql.query import Query
>
> old_get_aggregation = Query.get_aggregation
>
> def get_aggregation(self, using, added_aggregate_names):
> original_select_related_values = {}
> for combined_query in self.combined_queries:
> original_select_related_values[combined_query] =
> combined_query.select_related
> combined_query.select_related = False
> result = old_get_aggregation(self, using, added_aggregate_names)
> for combined_query in self.combined_queries:
> combined_query.select_related =
> original_select_related_values[combined_query]
> return result
>
> Query.get_aggregation = get_aggregation
> }}}
>
> reproduced on on 3.2.5/3.2.6
New description:
reproduced on on 3.2.5/3.2.6
suggested fix:
on django.db.models.sql.query.py -> Query.get_aggregation, from
{{{
inner_query.select_for_update = False
inner_query.select_related = False
inner_query.set_annotation_mask(self.annotation_select)
}}}
to
{{{
inner_query.select_for_update = False
inner_query.select_related = False
for combined_query in inner_query.combined_queries:
combined_query.select_related = False
inner_query.set_annotation_mask(self.annotation_select)
}}}
can solve the problem I think.
my current monkey-patching code is,
{{{
from django.db.models.sql.query import Query
old_get_aggregation = Query.get_aggregation
def get_aggregation(self, using, added_aggregate_names):
cloned_self = self.clone()
for combined_query in cloned_self.combined_queries:
combined_query.select_related = False
return old_get_aggregation(cloned_self, using, added_aggregate_names)
Query.get_aggregation = get_aggregation
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:5>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue-Kim | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
select_related mysql |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: orm, count, union, select_related => orm, count, union,
select_related mysql
* easy: 1 => 0
* stage: Unreviewed => Accepted
Comment:
Thanks for the report. The proposed patch looks good. Would you like to
prepare PR?
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:6>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue-Kim | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sunkyue-Kim):
Replying to [comment:6 Mariusz Felisiak]:
> Thanks for the report. The proposed patch looks good. Would you like to
prepare PR?
Thank you for your review.
I'd like to submit a PR, however I don't have enough time for that for a
while. (I haven't submitted any PRs to open source project...so for me, it
will took some time..)
Anyone who can sub PR instead of me would be welcomed.
----
In the mean time, I found another buggy case:
{{{
ModelB.objects.select_related('model_a').union(ModelB.objects.select_related('model_a')).union(ModelB.objects.select_related('model_a')).count()
}}}
produces,
{{{
OperationalError: (1222, 'The used SELECT statements have a different
number of columns')
}}}
because it produces nested configuration like,
{{{
query.combined_queries = (query.combined_queries(query, query), query)
}}}
So,
{{{
for combined_query in inner_query.combined_queries:
combined_query.select_related = False
}}}
this fix should be improved by doing this in recursive way I think...
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:7>
Django
MySQL.
-------------------------------------+-------------------------------------
| Bae
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Jordan Bae
* status: new => assigned
Comment:
Could i look into and make a patch for this?
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:8>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue | Owner: Jordan
| Bae
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jordan Bae):
As my investigation, this issue is from subquery's duplicated column. it's
not related to aggregate function.
In my opinion, we had better to improve the subquery which includes join.
I attached a example for this.
mysql> select * from (
-> select django_migrations.id, dj.id from django_migrations inner
join django_migrations as dj on dj.id = django_migrations.id
-> ) subquery;
ERROR 1060 (42S21): Duplicate column name 'id' `
and current SQL is created by Subquery.
> a=Subquery(ModelB.objects.select_related('model_a'))
> print(a.query)
> SELECT `blog_modelb`.`id`, `blog_modelb`.`model_a_id`,
`blog_modela`.`id`, `blog_modela`.`value` FROM `blog_modelb` INNER JOIN
> `blog_modela` ON (`blog_modelb`.`model_a_id` = `blog_modela`.`id`)
If there is any missing or I misunderstood. please know me.
I will try to fix for this.
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:9>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue | Owner: Jordan
| Bae
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jordan Bae):
Originally, that queryset generates below query.
{{{
SELECT
COUNT(*)
FROM ((
SELECT
`blog_modelb`.`id`,
`blog_modelb`.`model_a_id`,
`blog_modela`.`id`,
`blog_modela`.`value`
FROM
`blog_modelb`
INNER JOIN `blog_modela` ON
(`blog_modelb`.`model_a_id` = `blog_modela`.`id`))
UNION (
SELECT
`blog_modelb`.`id`,
`blog_modelb`.`model_a_id`,
`blog_modela`.`id`,
`blog_modela`.`value`
FROM
`blog_modelb`
INNER JOIN `blog_modela` ON (`blog_modelb`.`model_a_id` =
`blog_modela`.`id`))) subquery
}}}
@ Mariusz Felisiak
@Sunkyue
I think you can use this during shot-term period.
{{{
>>> ModelB.objects.select_related('model_a').extra(select={'a':
'`blog_modela`.`id`'}).values('id', 'model_a_id', 'a',
'model_a__value').union(ModelB.objects.select_related('model_a').extra(select={'a':
'`blog_modela`.`id`'}).values('id', 'model_a_id', 'a',
'model_a__value')).count()
SELECT
COUNT(*)
FROM ((
SELECT
(`blog_modela`.`id`) AS `a`,
`blog_modelb`.`id`,
`blog_modelb`.`model_a_id`,
`blog_modela`.`value`
FROM
`blog_modelb`
INNER JOIN `blog_modela` ON
(`blog_modelb`.`model_a_id` = `blog_modela`.`id`))
UNION (
SELECT
(`blog_modela`.`id`) AS `a`,
`blog_modelb`.`id`,
`blog_modelb`.`model_a_id`,
`blog_modela`.`value`
FROM
`blog_modelb`
INNER JOIN `blog_modela` ON (`blog_modelb`.`model_a_id` =
`blog_modela`.`id`))) subquery
0
}}}
your fix looks making an unexpected SQL.
@ Mariusz Felisiak if you have a good idea for this bug, please share.
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:10>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue | Owner: Jordan
| Bae
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
I haven't tried it out but I wouldn't be surprised if this was fixed by
70499b25c708557fb9ee2264686cd172f4b2354e (#34123) as it ensures that all
combined queries have non-colliding aliases.
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:11>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue | Owner: Jordan
| Bae
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
Replying to [comment:11 Simon Charette]:
> I haven't tried it out but I wouldn't be surprised if this was fixed by
70499b25c708557fb9ee2264686cd172f4b2354e (#34123) as it ensures that all
combined queries have non-colliding aliases.
Good catch! I submitted [https://github.com/django/django/pull/16323 PR]
with tests.
Fixed in 70499b25c708557fb9ee2264686cd172f4b2354e.
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:12>
Django
MySQL.
-------------------------------------+-------------------------------------
Reporter: Sunkyue | Owner: Jordan
| Bae
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: orm, count, union, | Triage Stage: Accepted
select_related mysql |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"a411b909671b7b8f8773af6b7cffa6992fe29138" a411b909]:
{{{
#!CommitTicketReference repository=""
revision="a411b909671b7b8f8773af6b7cffa6992fe29138"
Refs #33050 -- Added test for QuerySet.count() on combined queries with
select_related().
Thanks Simon Charette for noticing this.
Fixed in 70499b25c708557fb9ee2264686cd172f4b2354e.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33050#comment:13>