Bug #98705 MySqlDataReader returns TINYINT(1) values as SByte if query contains UNION(S)

• Submitted: 21 Feb 2020 10:02
• Modified: 21 Feb 2020 11:11
• Reporter: Xavier Mawet
• Status: Verified
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 6.10.8, 8.0.19
• OS: Any
• CPU Architecture: Any

[21 Feb 2020 10:02] Xavier Mawet

Description:
Connector/NET (MySqlDataReader) treats values of BOOLEAN (TINYINT(1)) columns as boolean or SBytes values depending on the query executed:
- If the query DOESN'T contain union, retrieving column values using MySqlDataReader will return boolean true or false values (expected behavior)
- If the query contains an union, retrieving column values using MySqlDataReader will return (sbyte)1 and (sbyte)0 values (unexpected behavior).

So, the mapped type in C# is not always the same for values from the same BOOLEAN (TINYINT(1)) column.

Similar issues:
- https://bugs.mysql.com/bug.php?id=79196
- https://bugs.mysql.com/bug.php?id=78917

Issue 79196 is very similar: the type of values returned by the Connector/NET switched from bool to SByte after first NULL found in BOOLEAN (TINYINT(1)) column. This issue is fixed in 6.10.8 release. But It seems that similar issue still occurs with the connector to 8.0.19 when using UNION in queries.

See also: https://stackoverflow.com/questions/27808304/mysqldatareader-returns-different-type-for-sa...

How to repeat:
Create table with TINYINT(1) column. Fill column with 0 and 1

CREATE TABLE  `testbool` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `testcol` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
);

id      | testcol
------------------
1       | 0
2       | 1
3       | 1
4       | 1
5       | 0
6       | 0
7       | 1

MySqlConnection conn = new MySqlConnection("<connection string>");
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT * FROM `testbool`";

using (MySqlDataReader reader = comm.ExecuteReader())
{
    while (reader.Read())
    {
        Type fieldType = reader.GetFieldType(ordinal); // return System.Bool (expected behavior)
        bool value = reader.GetFieldValue<bool>(1) // Success, no invalid cast exception
    }
}
conn.Close();

MySqlConnection conn = new MySqlConnection("<connection string>");
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT * FROM `testbool` WHERE id = 1 UNION SELECT * FROM `testbool` WHERE id = 3";

using (MySqlDataReader reader = comm.ExecuteReader())
{
    while (reader.Read())
    {

        Type fieldType = reader.GetFieldType(ordinal); // return System.SByte (unexpected behavior)
        bool value = reader.GetFieldValue<bool>(1) // fails with an invalid cast exception
    }
}
conn.Close();

[21 Feb 2020 11:11] MySQL Verification Team

Hello Xavier Mawet,

Thank you for the report and feedback.

regards,
Umesh

[23 Feb 2020 5:06] Bradley Grainger

I don't think this is a strictly a Connector/NET issue.

For the first query, the MySQL Server (I'm running 5.7.29) returns the field typed as TINYINT(1) and Connector/NET treats this as System.Boolean (by design).

For the second (UNION) query, the MySQL Server returns the field typed as TINYINT(4). Connector treats this as a regular TINYINT field, i.e., System.SByte.

(You can determine the above with a Wireshark packet capture of the two queries. You can also see it in MySQL Workbench by executing the queries, switching to the Field Types view, and looking at the Display Size column.)

I would consider this a MySQL Server bug, as it's widening the display width of an integer column in a UNION query. However, from the Server's point of view, it's probably not considered a bug as the display width is just informational and not really supposed to control behaviour; that's special handling on Connector/NET's part (to distinguish bool from sbyte). Furthermore, display width is being deprecated: https://dev.mysql.com/worklog/task/?id=13127

You could try reporting this as a bug against MySQL Server. It will need to be fixed on the server for Connector/NET to report the results of a UNION query as a bool.

I can't think of a workaround, since I don't know of any way to forcibly cast a MySQL result column to TINYINT(1) (or even TINYINT); see https://dba.stackexchange.com/a/192827.