[Django] #35369: MySQL .union().aggregate() raises Unknown Column

[Django]

#35369: MySQL .union().aggregate() raises Unknown Column
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
DS/Charlie |
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) | Keywords:
Severity: Normal | union,aggregate,mysql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
```
class BugModel(models.Model):
foo = models.CharField(max_length=255)

class BugTestCase(TestCase):
def test_bug(self):
a = BugModel.objects.all()
b = BugModel.objects.all()

ab = a.union(b, all=False)
"""
(
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
) UNION (
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
)
"""

ab.aggregate(max=Max("foo"))
"""
SELECT MAX(`__col1`)
FROM (
(
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
) UNION (
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
)
) subquery


Traceback (most recent call last):
File "/private/tmp/djangobug/venv/lib/python3.12/site-
packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/djangobug/venv/lib/python3.12/site-
packages/django/db/backends/mysql/base.py", line 75, in execute
return self.cursor.execute(query, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/djangobug/venv/lib/python3.12/site-
packages/MySQLdb/cursors.py", line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/private/tmp/djangobug/venv/lib/python3.12/site-
packages/MySQLdb/cursors.py", line 330, in _query
db.query(q)
File "/private/tmp/djangobug/venv/lib/python3.12/site-
packages/MySQLdb/connections.py", line 261, in query
_mysql.connection.query(self, query)
MySQLdb.OperationalError: (1054, "Unknown column '__col1' in
'field list'")
"""
```


Django 4.2.11 and 5.0.4
mysqlclient 2.2.4

all in a clean virtualenv on python 3.12.2
--
Ticket URL: <https://code.djangoproject.com/ticket/35369>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35369: MySQL .union().aggregate() raises Unknown Column
-------------------------------------+-------------------------------------

Reporter: DS/Charlie | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
union,aggregate,mysql | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by DS/Charlie:

Old description:

New description:

{{{

--
Ticket URL: <https://code.djangoproject.com/ticket/35369#comment:1>

[Django]

ab = a.union(b)

Ticket URL: <https://code.djangoproject.com/ticket/35369#comment:2>

[Django]

#35369: MySQL .union().aggregate() raises Unknown Column
-------------------------------------+-------------------------------------
Reporter: DS/Charlie | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
union,aggregate,mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by DS/Charlie:

Old description:

> {{{
>
> class BugModel(models.Model):
> foo = models.CharField(max_length=255)
>
> class BugTestCase(TestCase):
> def test_bug(self):
> a = BugModel.objects.all()
> b = BugModel.objects.all()
>

ab = a.union(b) # does NOT happen with `a | b`

"""
(
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
) UNION (
SELECT `bugapp_bugmodel`.`id` AS `col1`,
`bugapp_bugmodel`.`foo` AS `col2`
FROM `bugapp_bugmodel`
)
"""

q = ab.aggregate(max=Max("foo"))

self.assertIsNone(q['max'])

}}}


Django 4.2.11 and 5.0.4
mysqlclient 2.2.4

all in a clean virtualenv on python 3.12.2

--
--

Ticket URL: <https://code.djangoproject.com/ticket/35369#comment:3>

[Django]

#35369: MySQL .union().aggregate() raises Unknown Column
-------------------------------------+-------------------------------------
Reporter: DS/Charlie | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 5.0
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage:
union,aggregate,mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* resolution: => duplicate
* status: new => closed

Comment:

Per
[https://docs.djangoproject.com/en/5.0/ref/models/querysets/#django.db.models.query.QuerySet.union
the documentation]

> In addition, only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying
columns (i.e. slicing, count(), exists(), order_by(), and
values()/values_list()) are allowed on the resulting QuerySet. Further,
databases place restrictions on what operations are allowed in the
combined queries.

Closing as duplicate of #28519.
--
Ticket URL: <https://code.djangoproject.com/ticket/35369#comment:4>