MySQL Bugs: #112390: The group by combined with GROUP_CONCAT will return undefined result

Bug #112390	The group by combined with GROUP_CONCAT will return undefined result
Submitted:	19 Sep 2023 14:58	Modified:	21 Sep 2023 11:55
Reporter:	karry zhang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When group by is used in combination with GROUP_CONCAT, it will cause undefined results to be returned.

How to repeat:
CREATE TABLE `t1` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `DID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB;

CREATE TABLE `t2` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `DID` bigint(20) DEFAULT NULL,
  `PID` bigint(20) DEFAULT NULL,
  `BZ` varchar(200) DEFAULT '',
  PRIMARY KEY (`UID`),
  KEY `idx_DID` (`DID`),
  KEY `IDX_PID` (`PID`)
) ENGINE=InnoDB;

INSERT INTO `t1` VALUES(294, 251);
INSERT INTO `t2` VALUES(863, 294, 395, '');
INSERT INTO `t2` VALUES(864, 294, 395, '');

SELECT
    `t1`.`DID`,
    `t2`.`PID`,
    `t2`.`UID` AS `XSDMXID`,
    GROUP_CONCAT( t2.BZ ORDER BY t2.UID SEPARATOR " " ) AS BZ
FROM
    `t1` INNER JOIN `t2` ON `t1`.`UID` = `t2`.`DID` 
WHERE
    `t1`.`DID` = 251 
GROUP BY
    `DID`,
    `PID`;

We will get the following random results:
+------+------+---------+------+
| DID  | PID  | XSDMXID | BZ   |
+------+------+---------+------+
|  251 |  395 |     863 |      |
+------+------+---------+------+

+------+------+---------+------+
| DID  | PID  | XSDMXID | BZ   |
+------+------+---------+------+
|  251 |  395 |     864 |      |
+------+------+---------+------+
Because the results are random, you need to execute the above sql several times.

However when we don't use the GROUP_CONCAT function, the result is deterministic:

SELECT
    `t1`.`DID`,
    `t2`.`PID`,
    `t2`.`UID` AS `XSDMXID`
FROM
    `t1` INNER JOIN `t2` ON `t1`.`UID` = `t2`.`DID` 
WHERE
    `t1`.`DID` = 251 
GROUP BY
    `DID`,
    `PID`;

We will get the following results:
+------+------+---------+
| DID  | PID  | XSDMXID |
+------+------+---------+
|  251 |  395 |     863 |
+------+------+---------+

Suggested fix:
When group by is used in combination with GROUP_CONCAT, we also hope to get a deterministic result.

And I found the join operation is necessary to reproduce this problem.

Hi Mr. zhang,

Thank you for your bug report.

However, this is not a bug.

MySQL follows SQL standard strictly. Hence, if any field in the SELECT list is not included in the GROUP BY statement, then any random value can be selected  from the subset of values originating from columns in the GROUP BY clause.

Hence, the only solution of your problem is to include:

`t2`.`UID` AS `XSDMXID`

in the GROUP BY clause.

Not a bug.