MySQL Bugs: #18570: Unsigned tinyint (NET byte) incorrectly determined param type from param val

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

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:
....

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.

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

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.

Changing synopsis to:
Unsigned tinyint (NET byte) incorrectly determined param type from param val

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

http://bugs.mysql.com/bug.php?id=20707 seems to report the same problem. Set to duplicate of this report.

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)