summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/build/changelog/changelog_10.rst23
-rw-r--r--doc/build/changelog/migration_10.rst31
-rw-r--r--doc/build/core/types.rst3
-rw-r--r--lib/sqlalchemy/dialects/mysql/base.py9
-rw-r--r--lib/sqlalchemy/sql/compiler.py9
-rw-r--r--lib/sqlalchemy/sql/default_comparator.py21
-rw-r--r--lib/sqlalchemy/sql/elements.py2
-rw-r--r--lib/sqlalchemy/sql/operators.py5
-rw-r--r--lib/sqlalchemy/sql/sqltypes.py17
-rw-r--r--lib/sqlalchemy/sql/type_api.py2
-rw-r--r--lib/sqlalchemy/types.py1
-rw-r--r--test/dialect/mysql/test_query.py39
-rw-r--r--test/dialect/postgresql/test_query.py6
-rw-r--r--test/sql/test_operators.py28
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'
View Lorry Controller Status