MySQL Bugs: #14923: SUM(), AVG(), VARIANCE() function bug

Bug #14923	SUM(), AVG(), VARIANCE() function bug - INTEGER
Submitted:	14 Nov 2005 13:19	Modified:	21 Dec 2005 9:13
Reporter:	Gabor Gosztola	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any

Description:
We have had a type bug observation, which confused us that we have to store our DW data different type what it be.
The main problem is that we don't able to handle the result of AVG(), VARIANCE(), SUM() functions as numeric data type. It seems the MySQL Server handles the result as string when the type of the field is INTEGER, but it handles it num, when the field is DOUBLE or FLOAT. It could be dangerous if anyone doesn't recognize it in his system.

How to repeat:
/* STEP 1 - Bad result*/
CREATE TABLE  integer_test_step_1 (
  id int(10) unsigned NOT NULL auto_increment,
  amount int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into integer_test_step_1(amount) values (10);
insert into integer_test_step_1(amount) values (20);
select * from integer_test_step_1;
select sum(amount) from integer_test_step_1;

/* STEP 2 - Good result*/
CREATE TABLE  integer_test_step_2 (
  id int(10) unsigned NOT NULL auto_increment,
  amount double unsigned NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into integer_test_step_2(amount) values (10);
insert into integer_test_step_2(amount) values (20);
select * from integer_test_step_2;
select sum(amount) from integer_test_step_2;

Suggested fix:
The values are correct, the process works well, but the type handling is bad.

Thank you for a problem report, but I do not understand, what do you mean by:

"It seems the MySQL Server handles the result as string when the type of the field is INTEGER..."

I performed the following actions both with 5.0.15-nt and 5.0.17-BK on Linux:

mysql> use test;
Database changed
mysql> CREATE TABLE  integer_test_step_1 (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   amount int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.55 sec)

mysql> insert into integer_test_step_1(amount) values (10);
Query OK, 1 row affected (0.11 sec)

mysql> insert into integer_test_step_1(amount) values (20);
Query OK, 1 row affected (0.03 sec)

mysql> select * from integer_test_step_1;
+----+--------+
| id | amount |
+----+--------+
|  1 |     10 |
|  2 |     20 |
+----+--------+
2 rows in set (0.01 sec)

mysql> select sum(amount) from integer_test_step_1;
+-------------+
| sum(amount) |
+-------------+
|          30 |
+-------------+
1 row in set (0.05 sec)

mysql> create table vvv1 as select sum(amount) from integer_test_step_1;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc vvv1;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| sum(amount) | decimal(33,0) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
1 row in set (0.13 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.02 sec)

So, for me it looks like the result of the SUM() aggregate function is decimal really. Why do you think it is string?

Valeriy,
Really...so
I'm sorry, I didn't change the mysql.exe in my connector folder...and I had been connected to the server on with the old version
BUT,
Please try to run a Select query for your vvv1 table over Query Browser (1.1.17)... It doesn't works well I think...

What do you mean? The value is left-indented in the Query Browser? Yes, I see this. But it is not a problem of MySQL server, in any case. Please, clarify what IS a problem from your point of view.

OK. My concrete problem is:
When I run a query "SELECT sum(amount)/60 from vvv1" and if I try to run this query simply in VBA with SQLRetrive over ODBC driver I'll get a string value. That's true the result is not an integer like the type of field but I think it should be back with "recognized double" on excel sheet like a double type column. I have a special regional setting (Hungarian) and it takes that my standard floating point setting is ",". 
"Select" Query result for "Double" column contains "," floating point, but this divided integer contains "." inside resulted cells. In Excel it is a sting.
The elder version of MySQL can do this...

Still I see no server bugs:

mysql> select `sum(amount)`/60 from vvv1;
+------------------+
| `sum(amount)`/60 |
+------------------+
|           0.5000 |
+------------------+
1 row in set (0.07 sec)

mysql> create table ttt2 as select `sum(amount)`/60 c1 from vvv1;
Query OK, 1 row affected (0.47 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc ttt2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| c1    | decimal(37,4) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

So, at the server level we got decimal, as expected.

If it is a ODBC-related problem then, please, change category accordingly, inform about the driver version and provide your SQL. Is it those used above?

And, please, send the results of the following statement:

mysql> show variables like 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

They can explain you something about ',' and '.' used in decimal values when represented as strings...

Yes, the problem maybe not server bug...it'll QB or Connector.

It looks like a QueryBrowser problem indeed, not a server bug. Same problem here (German ennvironment, Win XP, QueryBrowser 1.1.17).
The problem exists for types integer and decimal, and I see it for sum(), avg(), variance(), but not std(), stddev(), min(), max().
Another facet: sum(i) and sum(i)*0.1 will both appear as strings; but sum(i)*1e-1 will appear as a number. (Gabor, this might be used as a workaround, if you need one.)
The bug hits the display (left-adjusted), but also the "copy row values" functions: these values will be quoted (with single quotes) in the clipboard.
Another simple example for reproducing this:
create table t (i int, d decimal)
insert into t values(42,42)
SELECT sum(i), avg(i), stddev(i), variance(i), sum(d) from  t

then right click on the single result row and "copy row values".
Go to any old editor and insert.
Expect:
42, 42.0000, 0.0000, 0.0000, 42
But get:
'42', '42.0000', 0.0000, '0.0000', '42'

To the original reporter:

So, please, execute that query:

show variables like 'coll%';

in the QB and send the results here. Or you already solved that '.' or ',' locale problem?

To Gisbert Selke:

And how the results of select * from t looks like in the old editor when pasted? Just to be sure...

I expect to see in any editor (notepad, kedit, emacs,...):
42, 42.0000, 0.0000, 0.0000, 42
But I get:
'42', '42.0000', 0.0000, '0.0000', '42'

It's not the string, but decimal - exact numeric type, that was implemented in 5.0
In 4.1 you was getting double - approximate number, so you may get precision loss when calculating SUM or AVG of integers. In 5.0 you always get exact result.
There is no standard C type for fixed point numeric type, so MySQL server returns decimal as a string. The client could convert it then to whatever representation it wants. It's QB bug if it does not.

As a workaround, you can cast the expression in your query:

  SELECT CAST(SUM(i) as SIGNED INTEGER)

a) It is still strange that std(), stddev() yield (float) numbers, but avg(), variance() yield decimal.
b) What does "exact results" mean? If it comes across as a string of four decimal places (see my earlier examples), then it surely isn't more exact than a double (even than a float), so what's the advantage of sending a string?
b) Whether it's the server or QB doesn't make much difference to the end user, I guess. I think a way is needed to clear up communications in this case between the server and the client.

A possibly related report from msql-mysql-modules that may (or may not) be related (I am posting this here by permission of the author, Ray Zimmerman [rz10@cornell.edu]):

I believe I have discovered a bug (I'm guessing it's in DBD::mysql).  
When connecting to mysql 5, it returns the type of a decimal column  
as SQL_VARCHAR, as opposed to SQL_DECIMAL returned when the mysql  
server is version 4.1.

The details of my setup:

	Mac OS X Tiger 10.4.3
	perl 5.8.6
	DBI 1.47
	DBD::mysql 3.0002 (and 3.0002_04) (built with mysql-5.0.16 libraries)
	mysql 5.0.16
	mysql 4.1.15
The script below demonstrates the bug, outputing ...
    value   12  SQL_VARCHAR
... when connecting to a mysql 5.0.16 server on localhost, vs ...
    value    3  SQL_DECIMAL
... when connecting to a mysql 4.1.15 server on localhost.
[...]
Whether or not the server returns values for decimal columns as  
strings is not the issue. It's the fact that when I query for the  
column type I'm being told it's a VARCHAR column, when it's actually  
a DECIMAL column. I still believe this is a bug, but without knowing  
how DBI sets these values, I don't know if it's a bug in the driver  
or in the server.

See also bug #15556

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

reopened as wrongly closed

I think, it is really a duplicate of bug #15556. There is no "client libraries" category, so I put it back as server bug.