Bug #97067 Aggregate functions on BIT(n) columns return wrong result
Submitted: 30 Sep 2019 17:56 Modified: 10 Jan 2020 16:52
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.17 OS:Windows (10)
Assigned to: CPU Architecture:Other (x64)

[30 Sep 2019 17:56] Bradley Grainger 
Description:
When an aggregate function (e.g., MIN, MAX) is applied to a BIT(n) column, Connector/NET returns incorrect results.

How to repeat:
Execute the following SQL to create a table and insert values:

	CREATE TABLE test(Bit32 BIT(32) NOT NULL);
	INSERT INTO test(Bit32) VALUES (0),(65535);");

Run the following C# code:

using (var connection = new MySqlConnection("..."))
{
	connection.Open();
	var result = connection.ExecuteScalar(@"SELECT MIN(Bit32) FROM test;");
	Console.WriteLine(result); // prints 48, should print 0
	result = connection.ExecuteScalar(@"SELECT MAX(Bit32) FROM test;");
	Console.WriteLine(result); // prints 232820912949, should print 65535
}
[1 Oct 2019 7:49] MySQL Verification Team 
Hello Bradley,

Thank you for the report and test case.

regards,
Umesh
[10 Jan 2020 16:52] Gustavo Cuatepotzo 
Posted by developer:
 
This is due how bit fields are handled by mysql server, BIT fields are sent as string represented binary data,

In this example, using min() and max() fuctions, we are retrieving the values 48 and 232820912949,

48, is the ASCII code for 0, 

65535, in ASCII code digit by digit is:
54 53 53 51 53, these values in binary bytes are:
00110110 00110101 00110101 00110011 00110101, which converted to decimal is 232820912949, what we get with MAX() function.

If we add 0 to the result or to the original data then the result is returned as numeric data and we decode it as numbers.

SELECT MIN(Bit32+0) FROM test; //Returns 0
SELECT MAX(Bit32+0) FROM test; //Returns 65535