Bug #18570 Unsigned tinyint (NET byte) incorrectly determined param type from param val
Submitted: 28 Mar 2006 8:21 Modified: 7 Aug 2006 8:36
Reporter: Stefan Burwitz Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 (with MySql 4.1.16) OS:Windows (Windows XP, 2003 Server)
Assigned to: CPU Architecture:Any

[28 Mar 2006 8:21] Stefan Burwitz
Description:
unsigned tinyint values don't work properly in prepared statements using SELECT. unsigned tinyint appears to be treated as signed tinyint when run through a prepared statement meaning any value greater than 127 won't be found through the SELECT.

How to repeat:
I have a table TableA with primary key column ColA declared as unsigned tinyint not null.

I have a second table TableB with column ColA declared as foreign key TableA.ColA and is part of the primary key together with a second column ColB declared as unsigned smallint.

Insert rows in both tables with values for ColA = 255, 128 and 127.

Performing a SELECT on either TableA or TableB using a prepared statement with ColA = 128 or 255 always returns no results. Running the SELECT statement through query browser always works for both tables. Performing a SELECT on either TableA or TableB using a prepared statement with ColA = 127 or a lower value always returns a result.

Suggested fix:
....
[5 Apr 2006 12:21] Tonci Grgin
Hi. Thanks for your problem report. Can you please provide info on your IDE version (I presume you're using VS?), NET platform version, sql script that creates tables and data and, if you believe this is Connector/NET issue, a code you used when you hit this problem so we can make a test case.
[11 Apr 2006 9:36] Stefan Burwitz
I am using VS.NET 2003 with .NET 1.1.

The script to create the table is:

DROP TABLE IF EXISTS SYSTEMS
;
CREATE TABLE TEST
(
	ID TINYINT UNSIGNED NOT NULL,
	Name VARCHAR(50) NOT NULL,
	PRIMARY KEY (ID),
	UNIQUE (ID),
	UNIQUE (Name)
)
;

Insert some test data:
insert into test values ('127', 'name1');
insert into test values ('128', 'name2');
insert into test values ('255', 'name3');

And the sample code to reproduce the error:

string connStr = "Data Source=127.0.0.1;Database=SAL;User Id=sal;Password=sal";

IDbConnection conn = new MySqlConnection(connStr);
conn.Open();

string sql = " SELECT count(*) FROM TEST WHERE ID = ?id";

MySqlCommand command = new MySqlCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection = (MySqlConnection)conn;
command.Prepare();

command.Parameters.Add("?id", (byte)127);
object count = command.ExecuteScalar();
Console.WriteLine(count+" rows retrieved");

command.Parameters.Add("?id", (byte)128);
count = command.ExecuteScalar();
Console.WriteLine(count+" rows retrieved");

command.Parameters.Add("?id", (byte)255);
count = command.ExecuteScalar();
Console.WriteLine(count+" rows retrieved");

command.Parameters.Add("?id", "255");
count = command.ExecuteScalar();
Console.WriteLine(count+" rows retrieved");

Output:
1 rows retrieved
0 rows retrieved
0 rows retrieved
1 rows retrieved
[11 Apr 2006 16:23] Tonci Grgin
Thanks for additional info.
Verified as described by reporter:

>> command.Parameters.Add("?id", (byte)128); is not converted to unsigned type.
 private void SetDbType( DbType dbType ), isUnsigned = False.
[11 Apr 2006 16:29] Tonci Grgin
Changing synopsis to:
Unsigned tinyint (NET byte) incorrectly determined param type from param val
[18 May 2006 19:08] 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/6590
[29 Jun 2006 7:52] Tonci Grgin
http://bugs.mysql.com/bug.php?id=20707 seems to report the same problem. Set to duplicate of this report.
[7 Aug 2006 8:36] MC Brown
An entry for this bug has been added to the C/NET changelog: 

Unsigned <literal>tinyint</literal> (NET byte) would lead to and incorrectly determined parameter type from the parameter value. (Bug #18570)