Bug #42810 min() messes up
Submitted: 12 Feb 2009 22:31 Modified: 13 Feb 2009 0:02
Reporter: Edward Strinden Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.31 OS:Windows (NT)
Assigned to: CPU Architecture:Any
Tags: GROUP BY, min(), three columns

[12 Feb 2009 22:31] Edward Strinden
Description:
This is from an example in the reference manual.  Please note: output on the third line should be |    0003 |  1.25 | D     |

mysql> select article, min(price) as price, dealer
    -> from shop
    -> group by article;
+---------+-------+--------+
| article | price | dealer |
+---------+-------+--------+
|    0001 |  3.45 | A      |
|    0002 | 10.99 | A      |
|    0003 |  1.25 | B      |  <--NOTE!!!
|    0004 | 19.95 | D      |
+---------+-------+--------+
4 rows in set (0.00 sec)

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |  <-- NOTE!!!
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |  <-- NOTE!!!
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.31, for Win32 (ia32)

Connection id:          5
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.31-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 1 hour 59 min 0 sec

How to repeat:
CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT   article, min(price) as price, dealer
FROM     shop
GROUP BY article;

Suggested fix:
I don't know.
[13 Feb 2009 0:02] MySQL Verification Team
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=13160:

Additional Info:

http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html

"Do not use this feature if the columns you omit from the GROUP BY part
are not constant in the group. The server is free to return any value
from the group, so the results are indeterminate unless all values are
the same."

mysql> set sql_mode = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT   article, min(price) as price, dealer
    -> FROM     shop
    -> GROUP BY article;
ERROR 1055 (42000): 'test.shop.dealer' isn't in GROUP BY