From 1ad236127c060141426c84f446e7b7e773febb31 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 9 Feb 2015 15:29:14 -0500 Subject: [PATCH] - 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 --- doc/build/changelog/changelog_10.rst | 13 +++++ lib/sqlalchemy/dialects/mysql/base.py | 7 +++ test/dialect/mysql/test_compiler.py | 75 ++++++++++++++++----------- 3 files changed, 66 insertions(+), 29 deletions(-) diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index 277fe28f8d..1356c8eba6 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -23,6 +23,19 @@ series as well. For changes that are specific to 1.0 with an emphasis 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 cbb108f5e4..8d62bae026 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 8108a01967..23341b9d28 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')) -- 2.47.2