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 20:35	Modified:	13 Nov 2007 12:43
Reporter:	Simone Giordano
Status:	Closed
Category:	Connector/Net	Severity:	S4 (Feature request)
Version:	5.0.6	OS:	Microsoft Windows
Assigned to:		Target Version:
Triage:	D5 (Feature request)

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.