MySQL Bugs: #99091: Unexpected return value getting integer for TINYINT(1) column

Bug #99091	Unexpected return value getting integer for TINYINT(1) column
Submitted:	27 Mar 2020 2:29	Modified:	28 Nov 2022 18:54
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Windows (10)
Assigned to:		CPU Architecture:	Other (x64)

Description:
When calling the MySqlDataReader methods to get an integral value (e.g., GetInt32, GetInt16, etc.), an unexpected value is returned when reading a TINYINT(1) column that contains a value other than 0 or 1.

GetSByte returns the value from the column.
GetByte returns the value from the column, casted unsafely to an (unsigned) byte, without an exception if the range of a byte is overflowed.
GetInt16, GetInt32, GetInt64 return 1 for all non-zero values.
GetFieldValue<int> etc. throws an InvalidCastException.

Since TINYINT(1) does represent a BOOL value, it could make sense that all values are coerced to either 0 or 1. However, the current set of methods isn't very consistent.

How to repeat:
Execute the following SQL:

drop table if exists tinyint_test;
create table tinyint_test(value tinyint(1));
insert into tinyint_test(value) values(-2);

Run the following C#:

using var connection = new MySqlConnection("...");
connection.Open();

using var command = new MySqlCommand("select value from tinyint_test", connection);
using var reader = command.ExecuteReader();
reader.Read();

Console.WriteLine(reader.GetSByte(0)); // -2
Console.WriteLine(reader.GetByte(0)); // 254, expected OverflowException
Console.WriteLine(reader.GetInt16(0)); // 1
Console.WriteLine(reader.GetInt32(0)); // 1
Console.WriteLine(reader.GetInt64(0)); // 1

// all these throw InvalidCastException
reader.GetFieldValue<sbyte>(0);
reader.GetFieldValue<byte>(0);
reader.GetFieldValue<short>(0);
reader.GetFieldValue<int>(0);
reader.GetFieldValue<long>(0);

Suggested fix:
The various methods should be consistent in what they return (or throw).

Hello Bradley,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Bug verified using Connector/Net version 8.0.30

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.32 release, and here's the proposed changelog entry from the documentation team:

Some methods of the MySqlDataReader class that should retrieve an integer
value instead could return unexpected values. This behavior had multiple
causes. First, the connector was not properly informed by the
TreatTinyAsBoolean property that it should treat type TINYINT(1) as INT or
BOOL. In addition, Connector/NET implemented the conversion to a different
type inconsistently.

Thank you for the bug report.