A bit(1) field from a subquery returns 48 for false and 49 for true

[lauxjpn]

While fixing the last Pomelo.MySql tests, I came across this strange bug.

The following schema and query will run fine in MySQL Workbench for MySQL 8.0.17:

CREATE TABLE `Weapons` (
  `IsAutomatic` bit(1) NOT NULL
) ENGINE=InnoDB;

insert into `Weapons` (`IsAutomatic`) values (0);
insert into `Weapons` (`IsAutomatic`) values (1);

CREATE TABLE `Gears` (
  `Nickname` varchar(255) NOT NULL
) ENGINE=InnoDB;

insert into `Gears` (`Nickname`) values ('Baird');
insert into `Gears` (`Nickname`) values ('Dom');

SELECT (
    SELECT `w`.`IsAutomatic`
    FROM `Weapons` AS `w`
    LIMIT 1
) as `MyColumn`
FROM `Gears` AS `g`;

The query returns the following result set, which is correct:

MyColumn
0
0

Using the same schema to execute the same query with MySqlConnector, the following result set is returned, which is incorrect:

MyColumn
48
48

I used the following console project for the MySqlConnector test:

using System;
using System.Diagnostics;
using MySql.Data.MySqlClient;

namespace CustomConsoleTest
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            using var connection = new MySqlConnection(
                "server=127.0.0.1;user id=root;password=;port=3306;database=SubqueryBug");
            connection.Open();

            using var command = connection.CreateCommand();
            command.CommandText = @"
SELECT (
    SELECT `w`.`IsAutomatic`
    FROM `Weapons` AS `w`
    LIMIT 1
) as `MyColumn`
FROM `Gears` AS `g`";

            using var reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // The bit(1) field `Weapons`.`IsAutomatic` somehow
                    // returns 48 (as false) or 49 (as true).
                    var value = reader.GetValue(0);

                    Console.WriteLine($"Value: {value}");
                    Debug.Assert(value is bool ||
                                 Convert.ToUInt64(value) == 0 ||
                                 Convert.ToUInt64(value) == 1);
                }
            }
        }
    }
}

It appears, that if a bit(1) column is returned from a subquery within a select statement, the returned value is off by 48. So 48 should actually be 0 and 49 should actually be 1.
Maybe some bit shifting bug?

Explicitly casting the value as another type like decimal, returns the correct result set in MySqlConnector:

SELECT (
	SELECT cast(`w`.`IsAutomatic` as decimal)
	FROM `Weapons` AS `w`
	LIMIT 1
)
FROM `Gears` AS `g`

Removing the outer query entirely and just executing the sub query, will also return a correct result set (in this case just one 0):

SELECT `w`.`IsAutomatic`
FROM `Weapons` AS `w`
LIMIT 1

[bgrainger]

The ASCII values of '0' and '1' are 0x30 == 48 and 0x31 == 49 respectively, which seems very likely to be where the value is coming from.

[bgrainger]

BIT columns are currently read as raw binary data:

MySqlConnector/src/MySqlConnector/Core/TextRow.cs

Lines 52 to 57 in db3af37

	case ColumnType.Bit:
	// BIT column is transmitted as MSB byte array
	ulong bitValue = 0;
	for (int i = 0; i < data.Length; i++)
	bitValue = bitValue * 256 + data[i];
	return bitValue;

However, the protocol documentation says:

everything else is converted into a string and is sent as Protocol::LengthEncodedString.

This is not always true, because BIT(1) columns are currently being read successfully (as binary).

The only difference I can see in this query compared to SELECT Bit1 FROM datatypes_bits; is the following:

This query:

• CharacterSet.Binary
• Length: 1
• ColumnType.Bit
• ColumnFlags.Unsigned | ColumnFlags.Binary
• Data: 01 30

SELECT Bit1:

• CharacterSet.Binary
• Length: 1
• ColumnType.Bit
• ColumnFlags.Unsigned
• Data: 01 00

Apparently, when the ColumnFlags.Binary flag is specified, the data is sent as a number converted to an ASCII string, but when ColumnFlags.Binary isn't specified, the data is sent as binary?

I'll have to try to find if there's any documentation on this.

Finally, SELECT COALESCE(Bit1, FALSE) (from #707) returns something different again:

• CharacterSet.Binary
• Length: 2
• ColumnType.VarString
• ColumnFlags.Binary
• Data: 01 30

This is currently interpreted (by MySqlConnector) as a byte[] and 0x30 is read as the byte value 48.

[bgrainger]

MySql.Data 8.0.17 returns the same results as MySqlConnector.

[bgrainger]

It would be interesting to see:

• how other MySQL clients handle this
• how this is returned from other MySQL Servers

[bgrainger]

MySQL Workbench reads 0 and 1 for the first query, and this appears to be numeric data, not a string containing that decimal digit. It reads a BLOB containing the single value 0 or 1 for the third query.

MySQL 8.0.17 (I was testing with 5.7) and MariaDB 10.4 returns a DECIMAL (not a BLOB) for the third query, containing a 0 or 1. The BLOB behaviour must have been a MySQL Server 5.7.x bug. FWIW, COALESCE with a BOOL (aka TINYINT(1)) column returns a long, as expected. This is probably just another reason to prefer BOOL over BIT(1) for representing Boolean values in MySQL. See also recommendations here: https://faithlife.codes/blog/2017/10/mysql_best_practices_for_dotnet/

[bgrainger]

I found a simpler repro and reported the bug to Oracle: https://bugs.mysql.com/bug.php?id=97067

[lauxjpn]

Awesome!!