- MySQL boolean symbols "true", "false" work again. 0.9's change

in :ticket:`2682` disallowed the MySQL dialect from making use of the
"true" and "false" symbols in the context of "IS" / "IS NOT", but
MySQL supports this syntax even though it has no boolean type.
MySQL remains "non native boolean", but the :func:`.true`
and :func:`.false` symbols again produce the
keywords "true" and "false", so that an expression like
``column.is_(true())`` again works on MySQL.
fixes #3186

4 files changed, 70 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index e9b78fe78..55a275659 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,23 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 3186
+
+        MySQL boolean symbols "true", "false" work again.  0.9's change
+        in :ticket:`2682` disallowed the MySQL dialect from making use of the
+        "true" and "false" symbols in the context of "IS" / "IS NOT", but
+        MySQL supports this syntax even though it has no boolean type.
+        MySQL remains "non native boolean", but the :func:`.true`
+        and :func:`.false` symbols again produce the
+        keywords "true" and "false", so that an expression like
+        ``column.is_(true())`` again works on MySQL.
+
+        .. seealso::
+
+            :ref:`bug_3186`
+
+    .. change::
         :tags: changed, mssql
         :tickets: 3182
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 58aa42df0..2b9e5f0fd 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -808,6 +808,25 @@ a cursor to be closed unless all results are fully fetched.
 
 :ticket:`2515`
 
+.. _bug_3186:
+
+MySQL boolean symbols "true", "false" work again
+------------------------------------------------
+
+0.9's overhaul of the IS/IS NOT operators as well as boolean types in
+:ticket:`2682` disallowed the MySQL dialect from making use of the
+"true" and "false" symbols in the context of "IS" / "IS NOT".  Apparently,
+even though MySQL has no "boolean" type, it supports IS / IS NOT when the
+special "true" and "false" symbols are used, even though these are otherwise
+synonymous with "1" and "0" (and IS/IS NOT don't work with the numerics).
+
+So the change here is that the MySQL dialect remains "non native boolean",
+but the :func:`.true` and :func:`.false` symbols again produce the
+keywords "true" and "false", so that an expression like ``column.is_(true())``
+again works on MySQL.
+
+:ticket:`3186`
+
 .. _change_3182:
 
 PyODBC driver name is required with hostname-based SQL Server connections
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 4dccd2760..247b7a143 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1637,6 +1637,14 @@ class MySQLCompiler(compiler.SQLCompiler):
             value = value.replace('\\', '\\\\')
         return value
 
+    # override native_boolean=False behavior here, as
+    # MySQL still supports native boolean
+    def visit_true(self, element, **kw):
+        return "true"
+
+    def visit_false(self, element, **kw):
+        return "false"
+
     def get_select_precolumns(self, select):
         """Add special MySQL keywords in place of DISTINCT.
 
@@ -2215,6 +2223,10 @@ class MySQLDialect(default.DefaultDialect):
     name = 'mysql'
     supports_alter = True
 
+    # MySQL has no true "boolean" type; we
+    # allow for the "true" and "false" keywords, however
+    supports_native_boolean = False
+
     # identifiers are 64, however aliases can be 255...
     max_identifier_length = 255
     max_index_name_length = 64
diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py
index dd11fe2b4..e085d86c1 100644
--- a/test/dialect/mysql/test_query.py
+++ b/test/dialect/mysql/test_query.py
@@ -1,11 +1,32 @@
 # coding: utf-8
 
-from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_, is_
 from sqlalchemy import *
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL
 from sqlalchemy import testing
 
 
+class IdiosyncrasyTest(fixtures.TestBase, AssertsCompiledSQL):
+    __only_on__ = 'mysql'
+    __backend__ = True
+
+    def test_is_boolean_symbols_despite_no_native(self):
+        is_(
+            testing.db.scalar(select([cast(true().is_(true()), Boolean)])),
+            True
+        )
+
+        is_(
+            testing.db.scalar(select([cast(true().isnot(true()), Boolean)])),
+            False
+        )
+
+        is_(
+            testing.db.scalar(select([cast(false().is_(false()), Boolean)])),
+            True
+        )
+
+
 class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
     __only_on__ = 'mysql'
     __backend__ = True