MySqlDataReader returns different type for same column if UNION ALL is used

Question

I have a loop through a DataReader generated from a MySqlCommand where its query selects from some fields including 2 TINYINT(1) fields that are mapped to bools on C# and this is what I'm expecting it to be.

The problem arose when I changed the query to perform an UNION ALL with the same table.

After I changed the query, I started to get an invalid conversion error. The TINYINT(1) columns now return SBytes instead of Boolean.

Is this a MySql Server problem? A MySql Net/Connector problem? Is this an expected behaviour?

Example query:

string sql = @"SELECT tinyint1column FROM mytable WHERE id = 1";

command.CommandText = sql;

using(var reader = command.ExecuteReader())
{
    while(reader.Read())
    {
        bool flag = (bool)reader["tinyint1column"]; // OK - No error
    }
}

sql = @"SELECT tinyint1column FROM mytable WHERE id = 1
        UNION ALL
        SELECT tinyint1column FROM mytable WHERE id = 2";

command.CommandText = sql;

using(var reader = command.ExecuteReader())
{
    while(reader.Read())
    {
        bool flag = (bool)reader["tinyint1column"]; // Invalid cast error???
    }
}

More context:

• I have a MySql Server 5.1 installed on a shared host so I CAN'T upgrade the server.
• I'm using MySql.Data version 6.5.4 because it's the only one I could compile to run on this shared host with medium/partial thrust.
• I'm actually "casting" the reader to a List<DbDataRecord> with the .Cast<>() extension method to get a "disconnected reader", but it does not change the underlying data in any way and the issue is already there before the casting.

Answer 1

I'm not sure if this is a MySql issue or a connector issue, but it's a problem.

After getting some inspiration from @Steve's answer I've came with a solution:

public static class DbDataRecordExtensions
{
    public static bool GetBoolean(this DbDataRecord rec, string fieldName)
    {
        var index = rec.GetOrdinal(fieldName);
        var value = rec.GetValue(index);

        if (value is bool || value is Boolean)
        {
            return (bool)value;
        }
        else if (value is SByte || value is sbyte)
        {
            return (sbyte)value != 0;
        }
        else
        {
            return rec.GetInt64(index) != 0;
        }
    }
}

There is not way I can do a universal cast because it changes the type when both queries on the union return rows, so I've wrote some conditions to overcome this.

Please note that for MySqlDataReader you will have to change the extension method signature and method name:

GetBooleanEx(this MySqlDataReader rec, string fieldName)