Bug #78181 Group by different behavior in mysql 5.6.21
Submitted: 24 Aug 2015 6:59 Modified: 24 Aug 2015 9:23
Reporter: Zafar Malik Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.21, 5.6.26, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: DML, GROUP_BY, MySQL

[24 Aug 2015 6:59] Zafar Malik
Description:
Actually till 5.6.16 if we want to get unique records based on some column then we do group by on that column and get unique records but in 5.6.21, we are not getting unique records.

I know that this functionality is not a compatible with sql standards but it was behaving like this till 5.6.16, so so many persons will be using in their queries. 

If now someone upgrade his mysql version old to 5.6.21 then all queries will provide different results which is very dangerous thing. So either keep this feature as it is in 5.6.21 also or at least mention with 5.6.21 and higher versions in bold. 

How to repeat:
Create schema by -

CREATE TABLE `table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `department` VARCHAR(50) NOT NULL,
  `joindate` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

Insert records as per below-

INSERT  INTO `table`(`id`,`name`,`department`,`joindate`) VALUES (1,'bhaskar','it','2015-08-24 00:00:00'),(2,'rajesh','it','2015-08-03 00:00:00'),(3,'abhik','it','2015-08-03 00:00:00'),(4,'virendra','astrick','2015-08-01 00:00:00'),(5,'ansuman','astrick','2015-08-01 00:00:00'),(6,'sunil','it','2015-08-09 00:00:00'),(7,'bhaskar','it','2015-08-30 00:00:00'),(8,'abhik','it','2015-08-20 00:00:00'),(9,'kapil','it','2015-08-16 00:00:00'),(10,'chetan','tester','2015-08-31 00:00:00'),(11,'bharat','ui','2015-08-17 00:00:00'),(12,'gaurav','it','2015-08-23 00:00:00'),(13,'sunil','it','2015-08-18 00:00:00'),(14,'manoj','it','2015-08-30 00:00:00'),(15,'sunil','it','2015-08-26 00:00:00'),(16,'rajesh','tester','2015-08-18 00:00:00');

Execute below query in 5.6.21 and previous version-

SELECT * FROM `table` WHERE id IN (SELECT id FROM `table` GROUP BY CONCAT(`name`,department) ORDER BY id);

You will get different no of rows.

Suggested fix:
You may keep this feature in this version also.
[24 Aug 2015 8:50] MySQL Verification Team
Hello Zafar Malik,

Thank you for the report.

Thanks,
Umesh
[24 Aug 2015 8:51] MySQL Verification Team
// 5.1.77, 5.5.46 with sql_mode='' or default

mysql> SELECT * FROM `table` WHERE id IN (SELECT id FROM `table` GROUP BY CONCAT(`name`,department) ORDER BY id);
+----+----------+------------+---------------------+
| id | name     | department | joindate            |
+----+----------+------------+---------------------+
|  1 | bhaskar  | it         | 2015-08-24 00:00:00 |
|  2 | rajesh   | it         | 2015-08-03 00:00:00 |
|  3 | abhik    | it         | 2015-08-03 00:00:00 |
|  4 | virendra | astrick    | 2015-08-01 00:00:00 |
|  5 | ansuman  | astrick    | 2015-08-01 00:00:00 |
|  6 | sunil    | it         | 2015-08-09 00:00:00 |
|  9 | kapil    | it         | 2015-08-16 00:00:00 |
| 10 | chetan   | tester     | 2015-08-31 00:00:00 |
| 11 | bharat   | ui         | 2015-08-17 00:00:00 |
| 12 | gaurav   | it         | 2015-08-23 00:00:00 |
| 14 | manoj    | it         | 2015-08-30 00:00:00 |
| 16 | rajesh   | tester     | 2015-08-18 00:00:00 |
+----+----------+------------+---------------------+
12 rows in set (0.00 sec)

// 5.6.26, 5.6.27 and 5.7.9 - with sql_mode='' or default

mysql> SELECT * FROM `table` WHERE id IN (SELECT id FROM `table` GROUP BY CONCAT(`name`,department) ORDER BY id);
+----+----------+------------+---------------------+
| id | name     | department | joindate            |
+----+----------+------------+---------------------+
|  1 | bhaskar  | it         | 2015-08-24 00:00:00 |
|  2 | rajesh   | it         | 2015-08-03 00:00:00 |
|  3 | abhik    | it         | 2015-08-03 00:00:00 |
|  4 | virendra | astrick    | 2015-08-01 00:00:00 |
|  5 | ansuman  | astrick    | 2015-08-01 00:00:00 |
|  6 | sunil    | it         | 2015-08-09 00:00:00 |
|  7 | bhaskar  | it         | 2015-08-30 00:00:00 |
|  8 | abhik    | it         | 2015-08-20 00:00:00 |
|  9 | kapil    | it         | 2015-08-16 00:00:00 |
| 10 | chetan   | tester     | 2015-08-31 00:00:00 |
| 11 | bharat   | ui         | 2015-08-17 00:00:00 |
| 12 | gaurav   | it         | 2015-08-23 00:00:00 |
| 13 | sunil    | it         | 2015-08-18 00:00:00 |
| 14 | manoj    | it         | 2015-08-30 00:00:00 |
| 15 | sunil    | it         | 2015-08-26 00:00:00 |
| 16 | rajesh   | tester     | 2015-08-18 00:00:00 |
+----+----------+------------+---------------------+
16 rows in set (0.00 sec)
[24 Aug 2015 9:23] Zafar Malik
one more point which will help to understand the issue that if you just execute sub-query ie. "SELECT id FROM `table` GROUP BY CONCAT(`name`,department)" then it will providing same results as previous version means here group by behaving same as in earlier version.

Thanks,
Zafar