Bug #79196 After null found, MySqlDataReader returns TINYINT(1) values as SByte, not bool
Submitted: 9 Nov 2015 23:28 Modified: 10 Nov 2015 23:10
Reporter: Golovenko Bob Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: BOOL, boolean, MySqlByte, TinyInt(1), TreatAsBoolean

[9 Nov 2015 23:28] Golovenko Bob
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.
[9 Nov 2015 23:31] Golovenko Bob
Proposed corrections

Attachment: MySqlByte.cs (text/plain), 4.47 KiB.

[9 Nov 2015 23:32] Golovenko Bob
Test code that reproduces error

Attachment: Program.cs (text/plain), 724 bytes.

[10 Nov 2015 10:02] Chiranjeevi Battula
Hello  Golovenko Bob,

Thank you for the bug report.
This is duplicate of Bug #78917, please see Bug #78917.

Thanks,
Chiranjeevi.
[10 Nov 2015 23:10] Golovenko Bob
Thanks for quick responce. Before posting, I reviewed all bugs with MySqlByte but missed to look for bugs with TinyInt(1) tag. Found nothing, I added new bug.

Anyway, I proposed solution, believe it helps closing bug in one of next versions.