Bug #39905 | Incorrect result with HAVING and no GROUP BY | ||
---|---|---|---|
Submitted: | 7 Oct 2008 10:43 | Modified: | 7 Oct 2008 11:31 |
Reporter: | michael last name | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | count(*), GROUP BY, having, innodb |
[7 Oct 2008 10:43]
michael last name
[7 Oct 2008 10:45]
michael last name
The error does not appear if there is only the row with MyID=2 in the table. The comment re "first two rows" is incorrect.
[7 Oct 2008 11:31]
Valeriy Kravchuk
This is NOT a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html. We have ONLY_FULL_GROUP_BY SQL mode to prevent this problem: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `testhaving` ( -> `MyID` int(11) NOT NULL, -> PRIMARY KEY (`MyID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.19 sec) mysql> mysql> insert into `testhaving`(`MyID`) values (2),(3),(4); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> select MyID -> from testhaving -> having count(*) > 1; +------+ | MyID | +------+ | 2 | +------+ 1 row in set (0.03 sec) mysql> set sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select MyID -> from testhaving -> having count(*) > 1; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR OUP columns is illegal if there is no GROUP BY clause
[7 Oct 2008 11:32]
MySQL Verification Team
Thank you for the bug report. Please read the Manual about sql_mode: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html mysql 5.0 > select MyID -> from testhaving -> having count(*) > 1; +------+ | MyID | +------+ | 2 | +------+ 1 row in set (0.06 sec) mysql 5.0 > set sql_mode=ONLY_FULL_GROUP_BY; Query OK, 0 rows affected (0.02 sec) mysql 5.0 > select MyID -> from testhaving -> having count(*) > 1; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY cla use mysql 5.0 >