Chain of unions leads to incorrect SQL query on MySQL.

[Thodoris Sotiropoulos]

I have the following model

  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.

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 `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

[Mariusz Felisiak]

Thanks. I'm pretty confident that it's the same issue that I encounter in #29338 (see ​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.

[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).

[Mariusz Felisiak]

MariaDB < 10.4 is not supported anymore.

[Mariusz Felisiak]

This was fixed in MySQL 8.0.31, see ​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

[Mariusz Felisiak]

I don't think it's worth juggling parentheses around UNION's to support this on MySQL < 8.0.31.

[GitHub <noreply@…>]

In 96bc4254:

Refs #31445 -- Added test for nesting QuerySet.union().

This was fixed in MySQL 8.0.31.