Description:
If TreatAsBoolean is true (default behaviour), Connector/NET treats TINYINT(1) columns as boolean columns. So, retrieving column values using MySqlDataReader will return boolean true or false values.
But after first NULL found in column, all next returned values are (sbyte)1 and (sbyte)0
This happens because on retrieving next row in MySqlDataReader TreatAsBoolean flag losts in MySqlByte (as found in Connector/NET sources).
How to repeat:
Create table with TINYINT(1) column. Fill column with 0 and 1 and put NULL somewhere in the middle of the column. For example:
CREATE TABLE `testbool` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`testcol` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
);
id | testcol
------------------
1 | 0
2 | 1
3 | 1
4 | null
5 | 0
6 | 0
7 | 1
Call next code:
MySqlConnection conn = new MySqlConnection("Server=localhost;Database=test;Uid=root;Pwd=wtvcbwebweuycvwetyx");
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT * FROM `testbool`";
using (MySqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
Console.WriteLine(reader["testcol"].ToString());
}
conn.Close();
Console.ReadKey();
You will see next result:
False
True
True
0
0
1
But correct should be:
False
True
True
False
False
True
Suggested fix:
I propose next fix in MySqlByte.cs:
1) Change constructors:
public MySqlByte(bool isNull)
{
this.isNull = isNull;
mValue = 0;
treatAsBool = false;
}
public MySqlByte(sbyte val)
{
this.isNull = false;
mValue = val;
treatAsBool = false;
}
to:
public MySqlByte(bool isNull, bool TreatAsBoolean)
{
this.isNull = isNull;
mValue = 0;
treatAsBool = TreatAsBoolean;
}
public MySqlByte(sbyte val, bool TreatAsBoolean)
{
this.isNull = false;
mValue = val;
treatAsBool = TreatAsBoolean;
}
2) Change ReadValue method:
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
if (nullVal)
return new MySqlByte(true);
if (length == -1)
return new MySqlByte((sbyte)packet.ReadByte());
else
{
string s = packet.ReadString(length);
MySqlByte b = new MySqlByte(SByte.Parse(s, NumberStyles.Any, CultureInfo.InvariantCulture));
b.TreatAsBoolean = TreatAsBoolean;
return b;
}
}
to:
IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
if (nullVal)
return new MySqlByte(true, TreatAsBoolean);
if (length == -1)
return new MySqlByte((sbyte)packet.ReadByte(), TreatAsBoolean);
else
{
string s = packet.ReadString(length);
return new MySqlByte(SByte.Parse(s, NumberStyles.Any, CultureInfo.InvariantCulture), TreatAsBoolean);
}
}
I have attached proposed corrected version.