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
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