Bug #114885 Incorrect group by execution
Submitted: 5 May 2024 9:07 Modified: 6 May 2024 6:23
Reporter: Xiu Tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.2.0, 8.0.37 OS:Any
Assigned to: CPU Architecture:Any

[5 May 2024 9:07] Xiu Tang 
Description:
The group by execution produces wrong results

How to repeat:
CREATE TABLE `t0` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` bigint(212) unsigned zerofill DEFAULT NULL COMMENT 'asdf',
  `c2` float DEFAULT NULL COMMENT 'asdf',
  UNIQUE KEY `c2` (`c2`),
  UNIQUE KEY `i81` (`c0` DESC,`c2` DESC,`c1`),
  KEY `i0` (`c2` DESC,`c0`)
);

INSERT INTO `t0` VALUES (279627978,NULL,1987),(1989,0,NULL),(-872199357,NULL,NULL),(NULL,NULL,-1081950000),(NULL,NULL,NULL),(NULL,0,NULL);

mysql> SET optimizer_switch='index_merge_union=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t0.c0 FROM t0 WHERE t0.c0 OR t0.c2 GROUP BY t0.c0;
Result:
Empty set (0.00 sec)

Expected Result:
+------------+
| c0         |
+------------+
|  279627978 |
|       NULL |
| -872199357 |
|       1989 |
+------------+
[6 May 2024 6:23] MySQL Verification Team 
Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh
[14 Oct 2024 15:24] OCA Admin 
Contribution submitted via Github - bug-114885/115353 
(*) Contribution by c q (Github hotdb-cq, mysql-server/pull/570#issuecomment-2408501589): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2120663846.txt (text/plain), 2.00 KiB.