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