Bug #114887 Incorrect Muti-table join with group by execution
Submitted: 5 May 2024 9:20 Modified: 6 May 2024 6:31
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:20] Xiu Tang 
Description:
Incorrect Muti-table join with group by execution.

How to repeat:
CREATE TABLE `t0` (
  `c0` varchar(500),
  `c1` mediumint(8) unsigned zerofill NOT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c1` (`c1`)
) ;

INSERT INTO `t0` VALUES (NULL,00000000),(NULL,16777215);

CREATE TABLE `t1` (
  `c0` varchar(500),
   `c1` mediumint(8) unsigned zerofill NOT NULL 
);

INSERT INTO `t1` VALUES (NULL,00000000),('292269042',00001971),('-1552922826',00001982),(NULL,16777215);

CREATE TABLE `t2` (
  `c0` varchar(500),
  `c1` mediumint(8) unsigned zerofill NOT NULL,
  PRIMARY KEY (`c1`)
) ;

INSERT INTO `t2` VALUES ('1844712082',00000000),('292269042',16777215);

mysql> SET SESSION sql_buffer_result = ON; 

mysql> SET optimizer_switch='block_nested_loop=off';

mysql> SELECT t0.c1  FROM t1 RIGHT OUTER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t0 ON t2.c1 = t0.c1 WHERE (t2.c0 IN (SELECT t1.c0 FROM t1 )) = TRUE GROUP BY t0.c1;

Result:
Empty set (0.00 sec)

Expected Result:
+----------+
| c1       |
+----------+
| 16777215 |
+----------+
[6 May 2024 6:31] MySQL Verification Team 
Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh