MySQL Bugs: #114886: Incorrect semi-join with group by execution

Bug #114886	Incorrect semi-join with group by execution
Submitted:	5 May 2024 9:16	Modified:	6 May 2024 6:29
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

Description:
The semi-join with group by execution produces wrong results.

How to repeat:
CREATE TABLE `t0` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` text
);

INSERT INTO `t0` VALUES (NULL,''),(292269042,NULL),(NULL,'537509512'),(NULL,'1992'),(NULL,'-698875896'),(971298274,NULL),(1979,NULL),(0,NULL),(NULL,'1990'),(NULL,NULL),(-719927610,NULL),(0,NULL),(2001,NULL),(-182197347,'292269042'),(NULL,NULL),(1947766166,NULL),(0,'26614716'),(292269042,'750631496'),(1984,'0V | 0'),(NULL,NULL);

mysql> set session sql_buffer_result=on;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT t0.c1 FROM t0 WHERE t0.c1 IN (SELECT t0.c0 FROM t0) GROUP BY t0.c1;

Result：
+------------+
| c1         |
+------------+
|            |
| 292269042  |
| 0V | 0     |
+------------+
3 rows in set (0.00 sec)

Expected Result：
+-----------+
| c1        |
+-----------+
|           |
| 292269042 |
+-----------+

I'm sorry, I made a mistake. SQL corrected to the following:

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

mysql> SELECT t0.c1 FROM t0 WHERE t0.c1 IN (SELECT t0.c0 FROM t0) GROUP BY t0.c1;
Result:
+-----------+
| c1        |
+-----------+
|           |
| 292269042 |
+-----------+
2 rows in set, 2 warnings (0.00 sec)

Expected Result:
+------------+
| c1         |
+------------+
|            |
| 292269042  |
| 0V | 0     |
+------------+

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh