How do I convert a MySQL function result to tinyint(1)

Question

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.

Answer 1

This guy has succeded doing a procedure for casting manually it: http://idiot-howto.blogspot.com/2008/07/mysql-cast-limitation.html

Answer 2

I ended up creating a MySQL Bug ticket because it appears that user functions won't return tinyint(1) even if you tell them to. I'll leave this unanswered for a while in case someone comes up with something or I hear back anything interesting from the bug ticket.