ValueError on fulltext match in MySQL

[Ian Carroll]

Column.match() implements the MATCH...AGAINST query on a MySQL FullText index incorrectly, in that it expects a Boolean value (but only in some uses). The attached, annotated codelet generates two errors (protected by try...except statements). The first one is only loosely related. The second one is the ValueError raised when returning results from a query with Column.match().

SQLAlchemy (0.9.8) MySQL (5.6.22) mysql-connector-python (2.0.2)

[Mike Bayer]

Please always provide stack traces. I was about to close this when it was revealed that the ValueError is local to the C processors.

What result would you like? a boolean or a number? You can get the number like this:

result = session.query(Book, type_coerce(score, Float)).all()  # FIXME
print result

output:

[(<__main__.Book object at 0x1046fcf50>, 2.0), (<__main__.Book object at 0x1046fcfd0>, 0.0), (<__main__.Book object at 0x1046fc590>, 0.0), (<__main__.Book object at 0x1046fd090>, 0.0), (<__main__.Book object at 0x1046fd110>, 0.0), (<__main__.Book object at 0x1046fd190>, 0.0), (<__main__.Book object at 0x1046fd210>, 0.0), (<__main__.Book object at 0x1046fd290>, 2.0), (<__main__.Book object at 0x1046fd350>, 0.0), (<__main__.Book object at 0x1046fd410>, 0.0), (<__main__.Book object at 0x1046fd4d0>, 0.0), (<__main__.Book object at 0x1046fd590>, 0.0), (<__main__.Book object at 0x1046fd650>, 0.0), (<__main__.Book object at 0x1046fd710>, 0.0), (<__main__.Book object at 0x1046fd7d0>, 2.0), (<__main__.Book object at 0x1046fd890>, 0.0),  .... etc

the expression produced by MATCH for MySQL, as documented, is MATCH (%s) AGAINST (%s IN BOOLEAN MODE). That this would suddenly return a numeric value based on an index is extremely surprising. I don't have a strong feeling that SQLAlchemy should be trying to guess this and if it were today, I'd probably not even have a "match" operator that attempts to be generic.

[Mike Bayer]

yeah i dont have anything on this one, this is a total crapshow. MySQL basically has designed this to be used as a floating-point boolean-evaluated value. terrible design. SQLAlchemy's expression design is not really like this, an expression has a single type and that's it. Anything that's WHERE clause must return a boolean, i don't know exactly if that can be changed in this case. it seems like we'd have to build some object that is a floating point but then implicitly coerces itself to boolean in this case.

or we can just document the above workaround in the MySQL docs.

[Ian Carroll]

Sorry about the stack trace, and thanks for type_coerce.

[Mike Bayer]

• 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

→ <<cset f5ff86983f9c>>