Bug #84072 Wrongly returned dataset
Submitted: 6 Dec 2016 8:03 Modified: 6 Dec 2016 10:19
Reporter: Toshko Andreev Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.9 OS:Any
Assigned to: CPU Architecture:Any

[6 Dec 2016 8:03] Toshko Andreev
Description:
We've hit upon a bug with the .NET connector where it would return WRONG data. Specifically when you do a SELECT and have a sub-query that returns a bit field, the returned value is always true even for ones that should be false.

In the how to repeat I've added database setup steps and a small program that demonstrates the problem, this only occurs with the .NET Connector (have tried running the same query with Navicat and the result is correct).

How to repeat:
#Database setup:
CREATE TABLE test.t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  BoolColumn bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 2
CHARACTER SET utf8
COLLATE utf8_bin;

SET NAMES 'utf8';

INSERT INTO test.t1(id, BoolColumn) VALUES
(1, True);
INSERT INTO test.t1(id, BoolColumn) VALUES
(2, False);
INSERT INTO test.t1(id, BoolColumn) VALUES
(3, True);

#Client program in C# with .NET Connector 6.9.9.
using MySql.Data.MySqlClient;
using System;
using System.Data;

namespace TestConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder bld = new MySqlConnectionStringBuilder();
            bld.Server = "<SERVER ADDRESS>";
            bld.Port = 3306;
            bld.UserID = "<USERNAME>";
            bld.Password = "<PASSWORD>";
            bld.AllowUserVariables = true;
            string sql = @"
                SELECT
                  id,
                  BoolColumn,
                  (SELECT
                      t.BoolColumn
                    FROM test.t1 sub
                    WHERE sub.id = t.id) AS SubColumn
                FROM test.t1 t";

            using (MySqlConnection con = new MySqlConnection(bld.ToString()))
            using (MySqlCommand cmd = new MySqlCommand(sql, con))
            using (MySqlDataAdapter adp = new MySqlDataAdapter(cmd))
            {
                con.Open();
                DataTable data = new DataTable("data");
                adp.Fill(data);

                Console.WriteLine("== Raw ==");
                foreach (DataRow row in data.Rows)
                {
                    Console.WriteLine("ID: {0} BoolColumn: {1} SubColumn: {2}", row["id"], row["BoolColumn"], row["SubColumn"]);
                }

                Console.WriteLine();
                Console.WriteLine("== Converted ==");
                foreach (DataRow row in data.Rows)
                {
                    Console.WriteLine("ID: {0} BoolColumn: {1} SubColumn: {2}", Convert.ToUInt32(row["id"]), Convert.ToBoolean(row["BoolColumn"]), Convert.ToBoolean(row["SubColumn"]));
                }
            }

            Console.ReadKey();
        }
    }
}

Suggested fix:
None
[6 Dec 2016 10:19] Chiranjeevi Battula
Hello  Toshko Andreev,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.
[6 Dec 2016 10:22] Chiranjeevi Battula
== Raw ==
ID: 1 BoolColumn: 1 SubColumn: 49
ID: 2 BoolColumn: 0 SubColumn: 48
ID: 3 BoolColumn: 1 SubColumn: 49

== Converted ==
ID: 1 BoolColumn: True SubColumn: True
ID: 2 BoolColumn: False SubColumn: True
ID: 3 BoolColumn: True SubColumn: True