Bug #70894 GROUP BY HAVING with MAX returns not all results

• Submitted: 13 Nov 2013 9:02
• Modified: 13 Nov 2013 11:41
• Reporter: Nico Huene
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.5, 5.6
• OS: Any
• CPU Architecture: Any

[13 Nov 2013 9:02] Nico Huene

Description:
mysql> create table A (id int primary key, a int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into A values (1,1),(2,2),(3,2),(4,2),(5,3);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from A;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    3 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from A group by a having id=Max(id);
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  5 |    3 |
+----+------+
2 rows in set (0.06 sec)

This result is wrong, it should be:
mysql> select * from A where id=(SELECT MAX(b.id) FROM A b WHERE b.a = a.a);
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  4 |    2 |
|  5 |    3 |
+----+------+
3 rows in set (0.00 sec)

I tested on the following versions:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.10    |
+-----------+
mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.31-log |
+------------+
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.13    |
+-----------+

How to repeat:
create table A (id int primary key, a int);
insert into A values (1,1),(2,2),(3,2),(4,2),(5,3);
select * from A group by a having id=Max(id);

[13 Nov 2013 11:41] MySQL Verification Team

Thank you for the bug report. Please read the Manual:

http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html

"MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means ....
for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

"A similar MySQL extension applies to the HAVING clause. In standard SQL....
calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate."

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.16 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > select * from A group by a having id=Max(id);
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  5 |    3 |
+----+------+
2 rows in set (0.00 sec)

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

mysql 5.6 > select * from A group by a having id=Max(id);
ERROR 1055 (42000): 'test.A.id' isn't in GROUP BY
mysql 5.6 >