[Django] #31445: Chain of unions leads to incorrect SQL query (MySQL)
Django
-------------------------------------+-------------------------------------
Reporter: theosotr | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have the following model
{{{#!python
class Listing(models.Model):
id = models.TextField(primary_key=True)
foo = models.CharField(max_length=20)
class Meta:
managed = False
db_table = 'listing'
}}}
Based on this model, I perform the following query in a MySQL backend.
{{{#!python
ret1 = Listing.objects.using('mysql')
ret2 = Listing.objects.using('mysql')
ret3 = Listing.objects.using('mysql')
ret4= ret2.union(ret3)
ret5 = ret1.union(ret4)
print(ret5.count())
}}}
Unfortunately, this query crashes with a ProgrammingError exception. The
track trace is
{{{
Traceback (most recent call last):
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
209, in execute
res = self._query(query)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
315, in _query
db.query(q)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/connections.py",
line 239, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'UNION (SELECT `listing`.`id`,
`listing`.`foo` FROM `listing`))) subquery' at line 1")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "driver_mysql.py", line 27, in <module>
raise e
File "driver_mysql.py", line 24, in <module>
print(ret5.count())
File "/dir/.env/lib/python3.6/site-packages/django/db/models/query.py",
line 397, in count
return self.query.get_count(using=self.db)
File "/dir/.env/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 517, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File "/dir/.env/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 502, in get_aggregation
result = compiler.execute_sql(SINGLE)
File "/dir/.env/lib/python3.6/site-
packages/django/db/models/sql/compiler.py", line 1151, in execute_sql
cursor.execute(sql, params)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.6/site-packages/django/db/utils.py", line
90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/dir/.env/lib/python3.6/site-
packages/django/db/backends/mysql/base.py", line 74, in execute
return self.cursor.execute(query, args)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
209, in execute
res = self._query(query)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
315, in _query
db.query(q)
File "/dir/.env/lib/python3.6/site-packages/MySQLdb/connections.py",
line 239, in query
_mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'UNION (SELECT `listing`.`id`,
`listing`.`foo` FROM `listing`))) subquery' at line 1")
}}}
The generated query is
{{{
SELECT "listing"."id", "listing"."foo" FROM "listing" UNION SELECT * FROM
(SELECT "listing"."id", "listing"."foo" FROM "listing" UNION SELECT
"listing"."id", "listing"."foo" FROM "listing")
}}}
Note that although MySQL crashes, the query above seems to be a valid
Postgres query.
Django version: 3.0.4
MySQL version: 5.7.29
--
Ticket URL: <https://code.djangoproject.com/ticket/31445>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: theosotr | Owner: nobody
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:1>
Django
-------------------------------------+-------------------------------------
Sotiropoulos |
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
The generated query is
{{{
(SELECT `listing`.`id`, `listing`.`foo` FROM `listing`) UNION ((SELECT
`listing`.`id`, `listing`.`foo` FROM `listing`) UNION (SELECT
`listing`.`id`, `listing`.`foo` FROM `listing`))
}}}
Note that although MySQL crashes, the query above seems to be a valid
Postgres query.
Django version: 3.0.4
MySQL version: 5.7.29
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:2>
Django
-------------------------------------+-------------------------------------
Sotiropoulos |
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> I have the following model
> (SELECT `listing`.`id`, `listing`.`foo` FROM `listing`) UNION ((SELECT
> `listing`.`id`, `listing`.`foo` FROM `listing`) UNION (SELECT
> `listing`.`id`, `listing`.`foo` FROM `listing`))
> }}}
>
> Note that although MySQL crashes, the query above seems to be a valid
> Postgres query.
>
> Django version: 3.0.4
> MySQL version: 5.7.29
New description:
The generated query is
{{{
(SELECT `listing`.`id`, `listing`.`foo` FROM `listing`) UNION ((SELECT
`listing`.`id`, `listing`.`foo` FROM `listing`) UNION (SELECT
`listing`.`id`, `listing`.`foo` FROM `listing`))
}}}
Note that although MySQL crashes, chaining unions in SQLite or Postgres
works as expected.
Django version: 3.0.4
MySQL version: 5.7.29
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: felixxm
Sotiropoulos |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => felixxm
* status: new => assigned
* stage: Unreviewed => Accepted
Comment:
Thanks. I'm pretty confident that it's the same issue that I encounter in
#29338 (see
[https://github.com/django/django/pull/11692#issuecomment-581568690
comment]) related with using of parentheses. Unfortunately result depends
on version of MySQL/MariaDB:
- MySQL 8.0.4 and MariaDB 10.3 raise `1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near...`,
- MySQL 8.0.19 raises `1235, "This version of MySQL doesn't yet support
'nesting of unions at the right-hand side'"`,
- MariaDB 10.4 - works.
I will try to finally fix this.
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:4>
Django
-------------------------------------+-------------------------------------
Sotiropoulos | Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Matthew Somerville):
Just stumbled across this trying to union three identical models together
with MariaDB 10.3. Can't upgrade MariaDB to 10.4 yet (which added bracket
support for unions: https://jira.mariadb.org/browse/MDEV-11953), so in
case this is useful to anyone else, the workaround I have used is
https://github.com/dracos/django/commit/e71c62b95e (probably has issues,
but appears to work okay for my use case, which is to display a place's
productions and all its children's productions in the same
query/pagination with this change to use `union()`:
https://github.com/dracos/Theatricalia/commit/423f8d5e , displayed as eg
https://theatricalia.com/place/7c/birmingham-repertory-theatre-
birmingham).
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: (none)
Sotiropoulos |
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Mariusz Felisiak => (none)
* status: assigned => new
Comment:
MariaDB < 10.4 is not supported anymore.
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: (none)
Sotiropoulos |
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
This was fixed in MySQL 8.0.31, see
[https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html#mysqld-8-0-31-optimizer
release notes]:
> ''"Bodies of parenthesized query expressions can now be nested to in
combination with `UNION`. For example, the query shown here, previously
rejected with error `ER_NOT_SUPPORTED_YET`, is now allowed"''
I'm going to add a regression test and skip it on MySQL < 8.0.31
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: (none)
Sotiropoulos |
Component: Database layer | Version: 3.0
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
I don't think it's worth juggling parentheses around `UNION`'s to support
this on MySQL < 8.0.31.
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: (none)
Sotiropoulos |
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"96bc4254ee84596e0d1fef1f2356d8736da3857a" 96bc4254]:
{{{
#!CommitTicketReference repository=""
revision="96bc4254ee84596e0d1fef1f2356d8736da3857a"
Refs #31445 -- Added test for nesting QuerySet.union().
This was fixed in MySQL 8.0.31.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31445#comment:9>