#31445: Chain of unions leads to incorrect SQL query (MySQL)
-------------------------------------+-------------------------------------
Reporter: Thodoris | Owner: nobody
Sotiropoulos |
Type: Bug | Status: new
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
-------------------------------------+-------------------------------------
Description changed by Thodoris Sotiropoulos:
Old description:
> 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
> `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:
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
`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 <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/066.3e4ee6e94f4047b889bdab95a7ee08f0%40djangoproject.com.
