- The :meth:`.Operators.match` operator is now handled such that the

return type is not strictly assumed to be boolean; it now
returns a :class:`.Boolean` subclass called :class:`.MatchType`.
The type will still produce boolean behavior when used in Python
expressions, however the dialect can override its behavior at
result time.  In the case of MySQL, while the MATCH operator
is typically used in a boolean context within an expression,
if one actually queries for the value of a match expression, a
floating point value is returned; this value is not compatible
with SQLAlchemy's C-based boolean processor, so MySQL's result-set
behavior now follows that of the :class:`.Float` type.
A new operator object ``notmatch_op`` is also added to better allow
dialects to define the negation of a match operation.
fixes #3263

14 files changed, 180 insertions, 16 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index ad9eefa09..f90ae40f8 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -1,3 +1,4 @@
+
 ==============
 1.0 Changelog
 ==============
@@ -22,6 +23,28 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 3263
+
+        The :meth:`.Operators.match` operator is now handled such that the
+        return type is not strictly assumed to be boolean; it now
+        returns a :class:`.Boolean` subclass called :class:`.MatchType`.
+        The type will still produce boolean behavior when used in Python
+        expressions, however the dialect can override its behavior at
+        result time.  In the case of MySQL, while the MATCH operator
+        is typically used in a boolean context within an expression,
+        if one actually queries for the value of a match expression, a
+        floating point value is returned; this value is not compatible
+        with SQLAlchemy's C-based boolean processor, so MySQL's result-set
+        behavior now follows that of the :class:`.Float` type.
+        A new operator object ``notmatch_op`` is also added to better allow
+        dialects to define the negation of a match operation.
+
+        .. seealso::
+
+            :ref:`change_3263`
+
+    .. change::
         :tags: bug, postgresql
         :tickets: 3264
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index e148e7d70..929a5fe3d 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -1547,6 +1547,37 @@ again works on MySQL.
 
 :ticket:`3186`
 
+.. _change_3263:
+
+The match() operator now returns an agnostic MatchType compatible with MySQL's floating point return value
+----------------------------------------------------------------------------------------------------------
+
+The return type of a :meth:`.Operators.match` expression is now a new type
+called :class:`.MatchType`.  This is a subclass of :class:`.Boolean`,
+that can be intercepted by the dialect in order to produce a different
+result type at SQL execution time.
+
+Code like the following will now function correctly and return floating points
+on MySQL::
+
+    >>> connection.execute(
+    ...    select([
+    ...        matchtable.c.title.match('Agile Ruby Programming').label('ruby'),
+    ...        matchtable.c.title.match('Dive Python').label('python'),
+    ...        matchtable.c.title
+    ...    ]).order_by(matchtable.c.id)
+    ... )
+    [
+        (2.0, 0.0, 'Agile Web Development with Ruby On Rails'),
+        (0.0, 2.0, 'Dive Into Python'),
+        (2.0, 0.0, "Programming Matz's Ruby"),
+        (0.0, 0.0, 'The Definitive Guide to Django'),
+        (0.0, 1.0, 'Python in a Nutshell')
+    ]
+
+
+:ticket:`3263`
+
 .. _change_3182:
 
 PyODBC driver name is required with hostname-based SQL Server connections
diff --git a/doc/build/core/types.rst b/doc/build/core/types.rst
index 14e30e46d..22b36a648 100644
--- a/doc/build/core/types.rst
+++ b/doc/build/core/types.rst
@@ -67,6 +67,9 @@ Standard Types`_ and the other sections of this chapter.
 .. autoclass:: LargeBinary
   :members:
 
+.. autoclass:: MatchType
+  :members:
+
 .. autoclass:: Numeric
   :members:
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 58eb3afa0..c868f58b2 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -602,6 +602,14 @@ class _StringType(sqltypes.String):
                                  to_inspect=[_StringType, sqltypes.String])
 
 
+class _MatchType(sqltypes.Float, sqltypes.MatchType):
+    def __init__(self, **kw):
+        # TODO: float arguments?
+        sqltypes.Float.__init__(self)
+        sqltypes.MatchType.__init__(self)
+
+
+
 class NUMERIC(_NumericType, sqltypes.NUMERIC):
     """MySQL NUMERIC type."""
 
@@ -1544,6 +1552,7 @@ colspecs = {
     sqltypes.Float: FLOAT,
     sqltypes.Time: TIME,
     sqltypes.Enum: ENUM,
+    sqltypes.MatchType: _MatchType
 }
 
 # Everything 3.23 through 5.1 excepting OpenGIS types.
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index b102f0240..29a7401a1 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -82,6 +82,7 @@ OPERATORS = {
     operators.eq: ' = ',
     operators.concat_op: ' || ',
     operators.match_op: ' MATCH ',
+    operators.notmatch_op: ' NOT MATCH ',
     operators.in_op: ' IN ',
     operators.notin_op: ' NOT IN ',
     operators.comma_op: ', ',
@@ -862,14 +863,18 @@ class SQLCompiler(Compiled):
         else:
             return "%s = 0" % self.process(element.element, **kw)
 
-    def visit_binary(self, binary, **kw):
+    def visit_notmatch_op_binary(self, binary, operator, **kw):
+        return "NOT %s" % self.visit_binary(
+            binary, override_operator=operators.match_op)
+
+    def visit_binary(self, binary, override_operator=None, **kw):
         # don't allow "? = ?" to render
         if self.ansi_bind_rules and \
                 isinstance(binary.left, elements.BindParameter) and \
                 isinstance(binary.right, elements.BindParameter):
             kw['literal_binds'] = True
 
-        operator_ = binary.operator
+        operator_ = override_operator or binary.operator
         disp = getattr(self, "visit_%s_binary" % operator_.__name__, None)
         if disp:
             return disp(binary, operator_, **kw)
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 4f53e2979..d26fdc455 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -68,8 +68,12 @@ class _DefaultColumnComparator(operators.ColumnOperators):
 
     def _boolean_compare(self, expr, op, obj, negate=None, reverse=False,
                          _python_is_types=(util.NoneType, bool),
+                         result_type = None,
                          **kwargs):
 
+        if result_type is None:
+            result_type = type_api.BOOLEANTYPE
+
         if isinstance(obj, _python_is_types + (Null, True_, False_)):
 
             # allow x ==/!= True/False to be treated as a literal.
@@ -80,7 +84,7 @@ class _DefaultColumnComparator(operators.ColumnOperators):
                 return BinaryExpression(expr,
                                         _literal_as_text(obj),
                                         op,
-                                        type_=type_api.BOOLEANTYPE,
+                                        type_=result_type,
                                         negate=negate, modifiers=kwargs)
             else:
                 # all other None/True/False uses IS, IS NOT
@@ -103,13 +107,13 @@ class _DefaultColumnComparator(operators.ColumnOperators):
             return BinaryExpression(obj,
                                     expr,
                                     op,
-                                    type_=type_api.BOOLEANTYPE,
+                                    type_=result_type,
                                     negate=negate, modifiers=kwargs)
         else:
             return BinaryExpression(expr,
                                     obj,
                                     op,
-                                    type_=type_api.BOOLEANTYPE,
+                                    type_=result_type,
                                     negate=negate, modifiers=kwargs)
 
     def _binary_operate(self, expr, op, obj, reverse=False, result_type=None,
@@ -125,7 +129,8 @@ class _DefaultColumnComparator(operators.ColumnOperators):
             op, result_type = left.comparator._adapt_expression(
                 op, right.comparator)
 
-        return BinaryExpression(left, right, op, type_=result_type)
+        return BinaryExpression(
+            left, right, op, type_=result_type, modifiers=kw)
 
     def _conjunction_operate(self, expr, op, other, **kw):
         if op is operators.and_:
@@ -216,11 +221,16 @@ class _DefaultColumnComparator(operators.ColumnOperators):
 
     def _match_impl(self, expr, op, other, **kw):
         """See :meth:`.ColumnOperators.match`."""
+
         return self._boolean_compare(
             expr, operators.match_op,
             self._check_literal(
                 expr, operators.match_op, other),
-            **kw)
+            result_type=type_api.MATCHTYPE,
+            negate=operators.notmatch_op
+            if op is operators.match_op else operators.match_op,
+            **kw
+        )
 
     def _distinct_impl(self, expr, op, **kw):
         """See :meth:`.ColumnOperators.distinct`."""
@@ -282,6 +292,7 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "isnot": (_boolean_compare, operators.isnot),
         "collate": (_collate_impl,),
         "match_op": (_match_impl,),
+        "notmatch_op": (_match_impl,),
         "distinct_op": (_distinct_impl,),
         "between_op": (_between_impl, ),
         "notbetween_op": (_between_impl, ),
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 734f78632..30965c801 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2763,7 +2763,7 @@ class BinaryExpression(ColumnElement):
                 self.right,
                 self.negate,
                 negate=self.operator,
-                type_=type_api.BOOLEANTYPE,
+                type_=self.type,
                 modifiers=self.modifiers)
         else:
             return super(BinaryExpression, self)._negate()
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 945356328..b08e44ab8 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -767,6 +767,10 @@ def match_op(a, b, **kw):
     return a.match(b, **kw)
 
 
+def notmatch_op(a, b, **kw):
+    return a.notmatch(b, **kw)
+
+
 def comma_op(a, b):
     raise NotImplementedError()
 
@@ -834,6 +838,7 @@ _PRECEDENCE = {
 
     concat_op: 6,
     match_op: 6,
+    notmatch_op: 6,
 
     ilike_op: 6,
     notilike_op: 6,
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 7bf2f337c..94db1d837 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -1654,10 +1654,26 @@ class NullType(TypeEngine):
     comparator_factory = Comparator
 
 
+class MatchType(Boolean):
+    """Refers to the return type of the MATCH operator.
+
+    As the :meth:`.Operators.match` is probably the most open-ended
+    operator in generic SQLAlchemy Core, we can't assume the return type
+    at SQL evaluation time, as MySQL returns a floating point, not a boolean,
+    and other backends might do something different.    So this type
+    acts as a placeholder, currently subclassing :class:`.Boolean`.
+    The type allows dialects to inject result-processing functionality
+    if needed, and on MySQL will return floating-point values.
+
+    .. versionadded:: 1.0.0
+
+    """
+
 NULLTYPE = NullType()
 BOOLEANTYPE = Boolean()
 STRINGTYPE = String()
 INTEGERTYPE = Integer()
+MATCHTYPE = MatchType()
 
 _type_map = {
     int: Integer(),
@@ -1685,6 +1701,7 @@ type_api.BOOLEANTYPE = BOOLEANTYPE
 type_api.STRINGTYPE = STRINGTYPE
 type_api.INTEGERTYPE = INTEGERTYPE
 type_api.NULLTYPE = NULLTYPE
+type_api.MATCHTYPE = MATCHTYPE
 type_api._type_map = _type_map
 
 # this one, there's all kinds of ways to play it, but at the EOD
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index 77c6e1b1e..d3e0a008e 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -19,7 +19,7 @@ BOOLEANTYPE = None
 INTEGERTYPE = None
 NULLTYPE = None
 STRINGTYPE = None
-
+MATCHTYPE = None
 
 class TypeEngine(Visitable):
     """The ultimate base class for all SQL datatypes.
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index b49e389ac..1215bd790 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -51,6 +51,7 @@ from .sql.sqltypes import (
     Integer,
     Interval,
     LargeBinary,
+    MatchType,
     NCHAR,
     NVARCHAR,
     NullType,
diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py
index e085d86c1..ccb501651 100644
--- a/test/dialect/mysql/test_query.py
+++ b/test/dialect/mysql/test_query.py
@@ -55,7 +55,7 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
         ])
         matchtable.insert().execute([
             {'id': 1,
-             'title': 'Agile Web Development with Rails',
+             'title': 'Agile Web Development with Ruby On Rails',
              'category_id': 2},
             {'id': 2,
              'title': 'Dive Into Python',
@@ -76,7 +76,7 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
         metadata.drop_all()
 
     @testing.fails_on('mysql+mysqlconnector', 'uses pyformat')
-    def test_expression(self):
+    def test_expression_format(self):
         format = testing.db.dialect.paramstyle == 'format' and '%s' or '?'
         self.assert_compile(
             matchtable.c.title.match('somstr'),
@@ -88,7 +88,7 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
     @testing.fails_on('mysql+oursql', 'uses format')
     @testing.fails_on('mysql+pyodbc', 'uses format')
     @testing.fails_on('mysql+zxjdbc', 'uses format')
-    def test_expression(self):
+    def test_expression_pyformat(self):
         format = '%(title_1)s'
         self.assert_compile(
             matchtable.c.title.match('somstr'),
@@ -102,6 +102,14 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
                    fetchall())
         eq_([2, 5], [r.id for r in results])
 
+    def test_not_match(self):
+        results = (matchtable.select().
+                   where(~matchtable.c.title.match('python')).
+                   order_by(matchtable.c.id).
+                   execute().
+                   fetchall())
+        eq_([1, 3, 4], [r.id for r in results])
+
     def test_simple_match_with_apostrophe(self):
         results = (matchtable.select().
                    where(matchtable.c.title.match("Matz's")).
@@ -109,6 +117,26 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
                    fetchall())
         eq_([3], [r.id for r in results])
 
+    def test_return_value(self):
+        # test [ticket:3263]
+        result = testing.db.execute(
+            select([
+                matchtable.c.title.match('Agile Ruby Programming').label('ruby'),
+                matchtable.c.title.match('Dive Python').label('python'),
+                matchtable.c.title
+            ]).order_by(matchtable.c.id)
+        ).fetchall()
+        eq_(
+            result,
+            [
+                (2.0, 0.0, 'Agile Web Development with Ruby On Rails'),
+                (0.0, 2.0, 'Dive Into Python'),
+                (2.0, 0.0, "Programming Matz's Ruby"),
+                (0.0, 0.0, 'The Definitive Guide to Django'),
+                (0.0, 1.0, 'Python in a Nutshell')
+            ]
+        )
+
     def test_or_match(self):
         results1 = (matchtable.select().
                     where(or_(matchtable.c.title.match('nutshell'),
@@ -116,14 +144,13 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
                     order_by(matchtable.c.id).
                     execute().
                     fetchall())
-        eq_([3, 5], [r.id for r in results1])
+        eq_([1, 3, 5], [r.id for r in results1])
         results2 = (matchtable.select().
                     where(matchtable.c.title.match('nutshell ruby')).
                     order_by(matchtable.c.id).
                     execute().
                     fetchall())
-        eq_([3, 5], [r.id for r in results2])
-
+        eq_([1, 3, 5], [r.id for r in results2])
 
     def test_and_match(self):
         results1 = (matchtable.select().
diff --git a/test/dialect/postgresql/test_query.py b/test/dialect/postgresql/test_query.py
index a512b56fa..6841f397a 100644
--- a/test/dialect/postgresql/test_query.py
+++ b/test/dialect/postgresql/test_query.py
@@ -703,6 +703,12 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL):
             matchtable.c.id).execute().fetchall()
         eq_([2, 5], [r.id for r in results])
 
+    def test_not_match(self):
+        results = matchtable.select().where(
+            ~matchtable.c.title.match('python')).order_by(
+            matchtable.c.id).execute().fetchall()
+        eq_([1, 3, 4], [r.id for r in results])
+
     def test_simple_match_with_apostrophe(self):
         results = matchtable.select().where(
             matchtable.c.title.match("Matz's")).execute().fetchall()
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index e8ad88511..f8ac1528f 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -12,7 +12,8 @@ from sqlalchemy import exc
 from sqlalchemy.engine import default
 from sqlalchemy.sql.elements import _literal_as_text
 from sqlalchemy.schema import Column, Table, MetaData
-from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, Boolean
+from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \
+    Boolean, NullType, MatchType
 from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \
     sqlite, mssql
 from sqlalchemy import util
@@ -1619,6 +1620,31 @@ class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL):
                             "CONTAINS (mytable.myid, :myid_1)",
                             dialect=oracle.dialect())
 
+    def test_match_is_now_matchtype(self):
+        expr = self.table1.c.myid.match('somstr')
+        assert expr.type._type_affinity is MatchType()._type_affinity
+        assert isinstance(expr.type, MatchType)
+
+    def test_boolean_inversion_postgresql(self):
+        self.assert_compile(
+            ~self.table1.c.myid.match('somstr'),
+            "NOT mytable.myid @@ to_tsquery(%(myid_1)s)",
+            dialect=postgresql.dialect())
+
+    def test_boolean_inversion_mysql(self):
+        # because mysql doesnt have native boolean
+        self.assert_compile(
+            ~self.table1.c.myid.match('somstr'),
+            "NOT MATCH (mytable.myid) AGAINST (%s IN BOOLEAN MODE)",
+            dialect=mysql.dialect())
+
+    def test_boolean_inversion_mssql(self):
+        # because mssql doesnt have native boolean
+        self.assert_compile(
+            ~self.table1.c.myid.match('somstr'),
+            "NOT CONTAINS (mytable.myid, :myid_1)",
+            dialect=mssql.dialect())
+
 
 class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'