- A warning is emitted when :func:`.cast` is used with the MySQL

dialect on a type where MySQL does not support CAST; MySQL only
supports CAST on a subset of datatypes.   SQLAlchemy has for a long
time just omitted the CAST for unsupported types in the case of
MySQL.  While we don't want to change this now, we emit a warning
to show that it's taken place.   A warning is also emitted when
a CAST is used with an older MySQL version (< 4) that doesn't support
CAST at all, it's skipped in this case as well.
fixes #3237

3 files changed, 66 insertions, 29 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 277fe28f8..1356c8eba 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -24,6 +24,19 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 3237
+
+        A warning is emitted when :func:`.cast` is used with the MySQL
+        dialect on a type where MySQL does not support CAST; MySQL only
+        supports CAST on a subset of datatypes.   SQLAlchemy has for a long
+        time just omitted the CAST for unsupported types in the case of
+        MySQL.  While we don't want to change this now, we emit a warning
+        to show that it's taken place.   A warning is also emitted when
+        a CAST is used with an older MySQL version (< 4) that doesn't support
+        CAST at all, it's skipped in this case as well.
+
+    .. change::
         :tags: feature, sql
         :tickets: 3087
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index cbb108f5e..8d62bae02 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1710,10 +1710,17 @@ class MySQLCompiler(compiler.SQLCompiler):
     def visit_cast(self, cast, **kwargs):
         # No cast until 4, no decimals until 5.
         if not self.dialect._supports_cast:
+            util.warn(
+                "Current MySQL version does not support "
+                "CAST; the CAST will be skipped.")
             return self.process(cast.clause.self_group())
 
         type_ = self.process(cast.typeclause)
         if type_ is None:
+            util.warn(
+                "Datatype %s does not support CAST on MySQL; "
+                "the CAST will be skipped." %
+                self.dialect.type_compiler.process(cast.typeclause.type))
             return self.process(cast.clause.self_group())
 
         return 'CAST(%s AS %s)' % (self.process(cast.clause), type_)
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 8108a0196..23341b9d2 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -1,6 +1,6 @@
 # coding: utf-8
 
-from sqlalchemy.testing import eq_, assert_raises_message
+from sqlalchemy.testing import eq_, assert_raises_message, expect_warnings
 from sqlalchemy import sql, exc, schema, types as sqltypes
 from sqlalchemy import Table, MetaData, Column, select, String, \
     Index, Integer, ForeignKey, PrimaryKeyConstraint, extract, \
@@ -13,7 +13,7 @@ from sqlalchemy import Table, MetaData, Column, select, String, \
 from sqlalchemy.dialects.mysql import base as mysql
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL
 from sqlalchemy.sql import table, column
-
+import re
 
 class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
@@ -339,7 +339,6 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             (m.MSBigInteger(unsigned=False), "CAST(t.col AS SIGNED INTEGER)"),
             (m.MSBigInteger(unsigned=True),
              "CAST(t.col AS UNSIGNED INTEGER)"),
-            (m.MSBit, "t.col"),
 
             # this is kind of sucky.  thank you default arguments!
             (NUMERIC, "CAST(t.col AS DECIMAL)"),
@@ -348,12 +347,6 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             (m.MSNumeric, "CAST(t.col AS DECIMAL)"),
             (m.MSDecimal, "CAST(t.col AS DECIMAL)"),
 
-            (FLOAT, "t.col"),
-            (Float, "t.col"),
-            (m.MSFloat, "t.col"),
-            (m.MSDouble, "t.col"),
-            (m.MSReal, "t.col"),
-
             (TIMESTAMP, "CAST(t.col AS DATETIME)"),
             (DATETIME, "CAST(t.col AS DATETIME)"),
             (DATE, "CAST(t.col AS DATE)"),
@@ -365,9 +358,6 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             (Date, "CAST(t.col AS DATE)"),
             (m.MSTime, "CAST(t.col AS TIME)"),
             (m.MSTimeStamp, "CAST(t.col AS DATETIME)"),
-            (m.MSYear, "t.col"),
-            (m.MSYear(2), "t.col"),
-            (Interval, "t.col"),
 
             (String, "CAST(t.col AS CHAR)"),
             (Unicode, "CAST(t.col AS CHAR)"),
@@ -402,8 +392,29 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             (m.MSVarBinary, "CAST(t.col AS BINARY)"),
             (m.MSVarBinary(32), "CAST(t.col AS BINARY)"),
 
-            # maybe this could be changed to something more DWIM, needs
-            # testing
+        ]
+
+        for type_, expected in specs:
+            self.assert_compile(cast(t.c.col, type_), expected)
+
+    def test_unsupported_casts(self):
+
+        t = sql.table('t', sql.column('col'))
+        m = mysql
+
+        specs = [
+            (m.MSBit, "t.col"),
+
+            (FLOAT, "t.col"),
+            (Float, "t.col"),
+            (m.MSFloat, "t.col"),
+            (m.MSDouble, "t.col"),
+            (m.MSReal, "t.col"),
+
+            (m.MSYear, "t.col"),
+            (m.MSYear(2), "t.col"),
+            (Interval, "t.col"),
+
             (Boolean, "t.col"),
             (BOOLEAN, "t.col"),
 
@@ -414,7 +425,10 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         ]
 
         for type_, expected in specs:
-            self.assert_compile(cast(t.c.col, type_), expected)
+            with expect_warnings(
+                "Datatype .* does not support CAST on MySQL;"
+            ):
+                self.assert_compile(cast(t.c.col, type_), expected)
 
     def test_no_cast_pre_4(self):
         self.assert_compile(
@@ -423,26 +437,29 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
         )
         dialect = mysql.dialect()
         dialect.server_version_info = (3, 2, 3)
-        self.assert_compile(
-            cast(Column('foo', Integer), String),
-            "foo",
-            dialect=dialect
-        )
+        with expect_warnings("Current MySQL version does not support CAST;"):
+            self.assert_compile(
+                cast(Column('foo', Integer), String),
+                "foo",
+                dialect=dialect
+            )
 
     def test_cast_grouped_expression_non_castable(self):
-        self.assert_compile(
-            cast(sql.column('x') + sql.column('y'), Float),
-            "(x + y)"
-        )
+        with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
+            self.assert_compile(
+                cast(sql.column('x') + sql.column('y'), Float),
+                "(x + y)"
+            )
 
     def test_cast_grouped_expression_pre_4(self):
         dialect = mysql.dialect()
         dialect.server_version_info = (3, 2, 3)
-        self.assert_compile(
-            cast(sql.column('x') + sql.column('y'), Integer),
-            "(x + y)",
-            dialect=dialect
-        )
+        with expect_warnings("Current MySQL version does not support CAST;"):
+            self.assert_compile(
+                cast(sql.column('x') + sql.column('y'), Integer),
+                "(x + y)",
+                dialect=dialect
+            )
 
     def test_extract(self):
         t = sql.table('t', sql.column('col1'))