Bug #106715 Incorrect left hash join with subquery in condition
Submitted: 12 Mar 2022 11:19 Modified: 14 Mar 2022 10:28
Reporter: Xiu Tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2022 11:19] Xiu Tang
Description:
Incorrect left hash join with subquery in condition when transforming subquery_to_derived condition.

How to repeat:
CREATE TABLE `t0` (
  `c0` text
);

CREATE TABLE `t1` (
  `c0` tinyint(3) unsigned zerofill,
  `c1` float DEFAULT NULL,
  UNIQUE KEY `c0` (`c0`)
);

INSERT INTO `t0` VALUES ('292269042'),(''),(NULL),('IcK5A'),(NULL),('z)]s'),('292269042'),('4f*T');

INSERT INTO `t1` VALUES (255,NULL),(NULL,0),(NULL,292269000),(000,NULL),(NULL,NULL);

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

mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

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

mysql> SELECT t1.c0 AS ref0 FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 WHERE (t1.c1 IN (SELECT t0.c0 AS ref1 FROM t0 )) OR (t0.c0);
+------+
| ref0 |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
9 rows in set (0.00 sec)
[14 Mar 2022 10:28] MySQL Verification Team
Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh