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:
None 
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
Description:
Query with HAVING count(*) > 1 but not GROUP BY can incorrectly return unique rows. The documentation does not prohibit HAVING without GROUP BY.

In the example:
Adding  "group by MyID" remove the error.
The error does not appear if you have just the first two rows of data.

How to repeat:
CREATE TABLE `testhaving` (
  `MyID` int(11) NOT NULL,
  PRIMARY KEY  (`MyID`)
) ENGINE=InnoDB;

insert  into `testhaving`(`MyID`) values (2),(3),(4);

select MyID
from testhaving
having count(*) > 1

returns:
MyID
-------
2
[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 >