| 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: | |
| 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 | ||
[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
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.