MySQL Bugs: #11294: Wrong data type getting values from grouping functions on integers with mysql-5

Bug #11294	Wrong data type getting values from grouping functions on integers with mysql-5
Submitted:	13 Jun 2005 16:58	Modified:	18 Jul 2005 21:52
Reporter:	Jorge Aguilar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.4	OS:	m$ windows xp
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I have been working with mysql-4.1.12 and mysql connector-net 1.0.4, and everything works ok, but for using some new features i updated to mysql-5.0.6-beta. While testing my code i found that when i use ExecuteScalar, or fill a dataset with a MySqlDataAdapter, for getting the result of grouping functions on integers it returns me a byte system array object, and this is not the value i expect for. The same problem occurs if i try to get the results from a temporary query like: "select sum(valor) from (select 1 valor) a" 

How to repeat:
CREATE TABLE `test` ( 
`field1` mediumint(9) default '0', 
`field2` float(9,3) default '0.000', 
`field3` double(15,3) default '0.000' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `test` (`field1`, `field2`, `field3`) VALUES 
(1, 1, 1); 

string connString = String.Format("server={0};user id={1};password={2};database={3};persist security info=true;","localhost", "root", "", "cimerpos" ); 

MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnection( connString ); 
conn.Open(); 

MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("select sum(field1) from test", conn); 

object valor = cmd.ExecuteScalar();

"select field1 from test" => It returns {1} System.Int32, its OK for me 

"select sum(field1) from test" => It returns {System.Array}, where [0] is byte type equal to 49, WRONG for me because i expect {1}. 

"select sum(field2) from test" => It returns {1.0} is System.Double, its OK 

"select sum(field3) from test" => It returns {1.0} is System.Double, its OK

Tested on Win 2000 Sp4, Microsoft Visual C# .NET , Connector .NET 1.0.4, mysql 5.0.7. Test case is attached.

Test case

Attachment: 11294.zip (application/x-zip-compressed, text), 3.85 KiB.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Here is the unit test that I am using to test this against 5.0.7 of the server.
With 5.0.7, sum of a mediumint column may return an int or a decimal type.  In my test, it is not returning a byte array.  If you have additional info, please let me know and I'll reopen the bug and analyze further.

execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (field1 mediumint(9) default '0', field2 float(9,3) " +"default '0.000', field3 double(15,3) default '0.000') engine=innodb " +
	"default charset=utf8");
execSQL("INSERT INTO test values (1,1,1)");

MySqlDataReader reader = null;
MySqlCommand cmd2 = new MySqlCommand("SELECT sum(field2) FROM test", conn);
try 
{
        reader = cmd2.ExecuteReader();
	reader.Read();
	object o = reader[0];
	Assert.AreEqual(1, o);
}
catch (Exception ex) 
{
	Assert.Fail(ex.Message);
}
finally 
{
	if (reader != null) reader.Close();
	reader = null;
}

Thanks for your reply.

I'm using mysql-5.0.7-beta-win32, but i'm still getting the same problem, even though, i'm used the unit test you used with your test. Here are the parameters in my configuration file.

[client]

port=3306

default-character-set=utf8

[mysqld]
port=3306
basedir=C:/cimerpos/mysql/
datadir=C:/cimerpos/mysql/data/
default-character-set=utf8
default-storage-engine=INNODB
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=13M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=25M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M
innodb_buffer_pool_size=61M
innodb_log_file_size=5M

innodb_thread_concurrency=8

[WinMySQLAdmin]
Server=C:/cimerpos/mysql/bin/mysqld-max-nt.exe

This is the link where you can see a screenshoot with the problem, even though i used your unit test: http://es.geocities.com/bbesito/bug.jpg

Thanks for your help.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This was the same issue as an earlier different bug and has been fixed.

This is fixed in 1.0.5.  1.0.4 did not support the newdecimal datatype and was defaulting to byte[]

Documented in 1.0.5 changelog.