MySQL Bugs: #3987: avg() bug

Bug #3987	avg() bug
Submitted:	3 Jun 2004 17:56	Modified:	7 Jun 2004 12:00
Reporter:	Mark Nozz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Windows (windows)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
avg() returns rounded numbers in this example..see below

How to repeat:
CREATE TABLE `rating` (
  `user_id` tinyint(4) NOT NULL default '0',
  `object_id` tinyint(4) NOT NULL default '0',
  `rate` tinyint(4) NOT NULL default '0'
);

INSERT INTO `rating` (`user_id`, `object_id`, `rate`) VALUES (1, 1, 2),
(2, 1, 5),
(2, 2, 3),
(2, 3, 3),
(3, 3, 1);

DROP TABLE IF EXISTS `object`;
CREATE TABLE `object` (
  `id` tinyint(1) NOT NULL default '0',
  `name` tinyint(10) NOT NULL default '0'
);

INSERT INTO `object` (`id`, `name`) VALUES (1, 10),
(2, 20),
(3, 30);

These my 2 tables..one with N:N ratings and second with object which are rated.
My query is:

SELECT IF(r1.rate>0,r1.rate,AVG(r2.rate)) as xrate, object.id
FROM object LEFT JOIN rating r1 ON r1.object_id=object.id AND r1.user_id=3 LEFT JOIN rating r2 ON r2.object_id=object.id AND r2.user_id!=3
GROUP BY object.id ORDER BY xrate DESC

if user has assigned object so this rate is used if not avg rate of other users is used. In this example im using user_id 3 wich is variable.

This all went fine but it returns '3' for object 1 instead of 3.5. The input numbers are 2 and 5. If im using group_concat() instead of avg() it returns '5,2' and if im using sum() instead of avg() it returns 7.

With other numbers its always same - avg() returns rounded numbers

Hi Mark,

the problem is the if statement, since the first possible value is not a REAL avg is deciding
to convert the second value.  If you put +0.0 on the end of both possible items you should get the right 
value.

root@localhost:bugs~> SELECT IF(r1.rate>0,r1.rate +0.0,AVG(r2.rate +0.0)) as xrate, object.id FROM 
object LEFT JOIN rating r1 ON r1.object_id=object.id AND r1.user_id=3 LEFT JOIN rating r2 ON 
r2.object_id=object.id AND r2.user_id!=3 GROUP BY object.id ORDER BY xrate DESC;
+---------+----+
| xrate   | id |
+---------+----+
| 3.50000 |  1 |
| 3.00000 |  2 |
| 1.00000 |  3 |
+---------+----+
3 rows in set (0.00 sec)

The results of the statement has changed from 4.0 to 4.1 and 5.0.  The reason for this should at least 
be posted in the manual and release notes if it's not a bug.

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:

no, as far as I see the bug is not fixed in 4.1 or 5.0.
I'm fixing it now in 4.0.21
test case:

create table t1 (a int);
insert t1 values (1),(2);
select if(1>2,a,avg(a)) from t1;
drop table t1;