MySQL Bugs: #27959: Bool datatype is not returned as System.Boolean by MySqlDataAdapter

Bug #27959	Bool datatype is not returned as System.Boolean by MySqlDataAdapter
Submitted:	19 Apr 2007 18:35	Modified:	13 Nov 2007 11:43
Reporter:	Simone Giordano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.0.6	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When using MySqlDataAdapter to fill a DataTable from a query, boolean field are not returned as System.Boolean. Them are returned as integer.

I've searched the bugs database, and I found only a workaround: use Convert.ToBoolean to returned field.

The problem is that I use column type to synchronize two different databases and one of them is not MySql. In this way I cannot establish if MySql field is truly boolean or is integer instead.

With MySqlConnection.GetSchema("DataTypes") I've found that System.Boolean there is not on mapped datatypes. Probably is that the cause of problem.

How to repeat:
On MySql:

CREATE TABLE test (f_bool BOOLEAN);
INSERT INTO test VALUES (1);

In C#:

MySqlDataAdapter mda = new MySqlDataAdapter("SELECT * FROM test", myCon);
DataTable tab = new DataTable();
mda.Fill(tab);

if (tab.Rows[0]["f_bool"].GetType() != typeof(bool))
  throw new Exception("There is an error on MySql connector!");

Suggested fix:
Add a column mapping on the connector for tinyint(1) => System.Boolean

Hi Simone and thanks for your report. It is actually S4 (feature request):
"Add a column mapping on the connector for tinyint(1) => System.Boolean"

I am not sure what connector can do. (1) is just display length and can't be used to distinguish type... What you can do is
 - use ENUM ("TRUE","FALSE") and / or
 - use MySqlDataReader.GetBoolean Method: 
GetBoolean:
Gets the value of the specified column as a Boolean.

I will set this to "Verified" and S4 and see if connectors team can come up with something.

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

Fixed in 5.1.4

A note has been added to the 5.1.4 changelog: 

Column types with only 1-bit (such as BOOLEAN and TINYINT(1) were not returned as boolean fields.

GetValue() on a field tinyint(1) returning a Boolean is causing a bug in our software where we expect an int object returned. Please don't interrupted tinyint(1) as a Boolean. Please reverse this change to the Connector.

A tinyint(1) is not a one bit field but can contain values -128 to 127. The (1) concerns how the field is displayed in a report and does nothing to limit the value. For some reason the MySqlConnector code has decided to limit the values. 

Int(10) means display ten digits in a column report not that it’s a ten bit integer. Look at the MySql documentation http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

"Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces."

Try it:

Create table test ( foo tinyint(1));
Insert into test (foo) values(127);
Insert into test (foo) values(-128);
Insert into test (foo) values(9);
Insert into test (foo) values(1);
Insert into test (foo) values(0);
Insert into test (foo) values(42);

Select * from test;
+------+
| foo  |
+------+
|  127 |
| -128 |
|    9 |
|    1 |
|    0 |
|   42 |
+------+

New bug report opened for this: Bug#34052

This patch caused problems for our application as well. Per spec we had unsigned tinyint(1) fields storing numbers from 0 to 255. With this patch, any value > 0 is treated as a bool and converted to the value 1.

We're forced to remove this change from our MySql.Data.dll and we ask that it's removed in the next version. 

If this behavior is desired, it could be a connection string setting where by default tinyint(1) are not treated as bools.