Here s the problem. In MySQL s Connector/NET a TINYINT(1) field properly translates back and forth into a .NET bool value. If I select from a table with a TINYINT(1) column, everything is golden. However, when you use built-in MySQL v5.0 functions like:
SELECT (3 BETWEEN 2 AND 4) AS oddly_not_boolean;
The actual return type from the database registers this field as INT or BIGINT, which Connector/.NET obviously doesn t convert to bool. MySQL CAST() and CONVERT() do not allow casting to TINYINT(1).
I ve even gone so far as to try a user function to do this, but this doesn t work either (EDIT: this does work):
CREATE FUNCTION `to_bool`(var_num BIGINT)
RETURNS TINYINT(1) RETURN var_num;
How do I convert an INT to a TINYINT(1) in a query in MySQL?
EDIT: The above function DOES actually work to convert the value to a TINYINT(1), but my Connector/NET is just bugged and doesn t properly convert the values from functions.
UPDATE 2009-11-03: Updated my connector and it s still giving me back Int32. Further testing reveals that this is an InnoDB bug in MySQL 5.0.x that only shows under specific circumstances.