#31445: Chain of unions leads to incorrect SQL query (MySQL)
-------------------------------------+-------------------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.9ab3c3056ab1345cda70546c039a2174%40djangoproject.com.
