MySQL Bugs: #46205: MySQL TINYINT(1) boolean conversion fails

Bug #46205	MySQL TINYINT(1) boolean conversion fails
Submitted:	15 Jul 2009 13:52	Modified:	10 Aug 2009 16:13
Reporter:	Attila Farago	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0.4, 6.1.1	OS:	Windows (Vista 32)
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	TINYINT boolean

Description:
After long hours of debugging it seems to me that MySQL TINYINT(1) boolean conversion fails in 6.0.4 version.

Ths source of the problem is that MySQL default charset is UTF8.
default-character-set=utf8
(I suspect that connection charset would yield the same result)

CharacterSetIndex is returned as 63, however it is overwritten to connection.driver.ConnectionCharSetIndex that was 33.

NativeDriver sets field maxlength to 3.
Therefore MaxLength does not equal to 1, that is the criteria for auto-bool conversion in Field.GetValueObject.

ps: explicit connectionstring setting does not help

---
NativeDriver.cs
field.MaxLength = cs.byteCount;

Field.cs
sets 
            if (CharacterSetIndex == 63)
                CharacterSetIndex = connection.driver.ConnectionCharSetIndex;
in
     public void SetTypeAndFlags(MySqlDbType type, ColumnFlags flags)

therefore
binary fields (such as a TINYINT(1))
will be defaulted to UTF8 (connection default) and will yield as MaxLength==3 in this code

        public IMySqlValue GetValueObject()
        {
            IMySqlValue v = GetIMySqlValue(Type);
            if (v is MySqlByte && ColumnLength == 1 && MaxLength == 1 && connection.Settings.TreatTinyAsBoolean)
            {
                MySqlByte b = (MySqlByte)v;
                b.TreatAsBoolean = true;
                v = b;
            }

result: TINYINT(1) fields will be mapped to SByte instead of boolean

introduced in 5.2.4

How to repeat:
- create an utf8 database
- create a table with a TINYINT(1) field
try to read it

Suggested fix:
? do not see the internals of the connector, for first sight I would change the Field.cs implementation to

        public IMySqlValue GetValueObject()
        {
            IMySqlValue v = GetIMySqlValue(Type);
            if (v is MySqlByte && ColumnLength == 1 && 
                /*MaxLength == 1 &&*/ connection.Settings.TreatTinyAsBoolean)
            {

Hi Attila and thanks for interesting report. Looking into this.

Verified as described by looking into latest sources.

To my opinion, IMySqlValue GetValueObject should also respect same logic as described in 5.2.7 change-log entry:
- fixed problem where the connector would incorrectly report the length of utf8 columns on servers 6.0 and later.  This was caused by 6.0 now using 4 bytes for utf8 columns.

Test case:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=**;Database=**;UserID=**;Password=**;PORT=**;Treat Tiny As Boolean = true;logging=True;charset=utf8";
            conn.Open();

            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 50;
            cmd.CommandText = "DROP TABLE IF EXISTS bug46205;";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "CREATE TABLE bug46205 (Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, BCol TINYINT(1));";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO bug46205 VALUES (NULL, 0);";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO bug46205 VALUES (NULL, 1);";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM bug46205;";
            try
            {
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine(dr.GetDataTypeName(1));
                    Console.WriteLine(dr.GetFieldType(1));
                    Console.WriteLine(dr.GetValue(1).GetType().FullName);
                    Console.WriteLine(dr.GetString(1));
                }
                dr.Close();
                cmd.CommandText = "DROP TABLE IF EXISTS bug46205;";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
                throw;
            }
yields following output:
TINYINT
System.SByte
System.SByte
0
TINYINT
System.SByte
System.SByte
1

"respect binary flags" makes no difference.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79014

Bug#46359 was marked as duplicate of this report.

Bug#41953 is a duplicate of this report.

An entry was added to the 5.2.8, 6.0.5, 6.1.1 changelogs:

Conversion of MySQL TINYINT(1) to boolean failed.