Bug #65808 Minimum value is incorrect.

• Submitted: 4 Jul 2012 11:27
• Modified: 4 Jul 2012 14:05
• Reporter: kamesh bhallamudi
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.1.62
• OS: Linux
• CPU Architecture: Any

[4 Jul 2012 11:27] kamesh bhallamudi

Description:
mysql> select AVG(Sale),MAX(Sale),MIN(Sale),COUNT(Sale),SUM(Sale) from SaleInfo where Country='IND' and Month='July';
+-----------+-----------+-----------+-------------+-----------+
| AVG(Sale) | MAX(Sale) | MIN(Sale) | COUNT(Sale) | SUM(Sale) |
+-----------+-----------+-----------+-------------+-----------+
|      47.5 | 99        | 22        |           4 |       190 |
+-----------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

mysql> select * from SaleInfo where Country='IND' and Month='July';
+-----+---------+---------+------+-------+--------+------+
| id  | Country | Product | Year | Month | Season | Sale |
+-----+---------+---------+------+-------+--------+------+
|   2 | IND     | Bike    | 2007 | July  | Summer | 99   |
| 111 | IND     | Car     | 2009 | July  | Rainy  | 8    |
| 163 | IND     | Car     | 2008 | July  | Rainy  | 22   |
| 173 | IND     | Mobile  | 2008 | July  | Summer | 61   |
+-----+---------+---------+------+-------+--------+------+

How to repeat:
Insert some rows into a table and execute above kind of the query.

Suggested fix:
Among the values, it is returning next min value rather than min value. Should return the minimum value.

[4 Jul 2012 12:54] Valeriy Kravchuk

Please, provide complete repeatable test case, with CREATE TABLE and INSERTs that are enough to reproduce the problem.

[4 Jul 2012 13:25] kamesh bhallamudi

I have attached the sample SQL. Just realized, it is giving even MAX value also wrong. For the attached SQL.

mysql> SELECT AVG(Sale), MAX(Sale), MIN(Sale), COUNT(Sale), SUM(Sale) from SaleInfo where Country='IND' and Month='July';
+-----------+-----------+-----------+-------------+-----------+
| AVG(Sale) | MAX(Sale) | MIN(Sale) | COUNT(Sale) | SUM(Sale) |
+-----------+-----------+-----------+-------------+-----------+
|      23.8 | 8         | 12        |           5 |       119 |
+-----------+-----------+-----------+-------------+-----------+

[4 Jul 2012 14:05] Valeriy Kravchuk

This is the reason of your problem:

  `Sale` varchar(50) DEFAULT NULL,

This column is defined as string, and string "8" is bigger than "12" or "44".