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 10:21	Modified:	7 Aug 2006 10:36
Reporter:	Stefan Burwitz
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	1.0.7 (with MySql 4.1.16)	OS:	Microsoft Windows (Windows XP, 2003 Server)
Assigned to:	Tonci Grgin	Target Version:

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)