- 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

6 files changed, 47 insertions, 9 deletions

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.