MySQL Bugs: #78181: Group by different behavior in mysql 5.6.21

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

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.

Hello Zafar Malik,

Thank you for the report.

Thanks,
Umesh

// 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)

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