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:Microsoft Windows
Assigned to: CPU Architecture:Any

[19 Apr 2007 18:35] Simone Giordano
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
[2 May 2007 12:35] Tonci Grgin
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.
[6 Nov 2007 17:24] Bugs System
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
[6 Nov 2007 17:24] Reggie Burnett
Fixed in 5.1.4
[13 Nov 2007 11:43] MC Brown
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.
[24 Jan 2008 22:10] David Low
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 |
+------+
[25 Jan 2008 9:13] Tonci Grgin
New bug report opened for this: Bug#34052
[1 Feb 2008 21:05] Maxim Mass
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.