Bug #109212 Incorrect Hash Semijoin Execution

• Submitted: 27 Nov 2022 8:34
• Modified: 27 Nov 2022 9:12
• Reporter: Xiu Tang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.30, 8.0.31
• OS: Any
• CPU Architecture: Any

[27 Nov 2022 8:34] Xiu Tang

Description:
Incorrect Hash Semijoin Execution when transforming semijoin condition.

How to repeat:
SELECT ALL t0.c2 FROM t0 RIGHT OUTER JOIN t1 ON t1.c2 = t0.c2 WHERE t1.c0 IN 
(SELECT t1.c0 FROM t1 
WHERE (t0.c1 IN (SELECT t0.c1 FROM t0 )) 
XOR (t1.c2 IN (SELECT t1.c2 FROM t1 )));

CREATE TABLE `t0` (
  `c1` float unsigned zerofill DEFAULT NULL,
  `c2` float DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`)
);

CREATE TABLE `t1` (
  `c0` longtext,
  `c2` float DEFAULT NULL
);

INSERT INTO `t1` VALUES ('-158770278',1188150000);

mysql> set optimizer_switch = 'semijoin=on';

mysql> SELECT ALL t0.c2 FROM t0 RIGHT OUTER JOIN t1 ON t1.c2 = t0.c2 WHERE t1.c0 IN  (SELECT t1.c0
FROM t1  WHERE (t0.c1 IN (SELECT t0.c1 FROM t0 ))  XOR (t1.c2 IN (SELECT t1.c2 FROM t1 )));
Empty set (0.00 sec)

mysql> set optimizer_switch = 'semijoin=off';

mysql> SELECT ALL t0.c2 FROM t0 RIGHT OUTER JOIN t1 ON t1.c2 = t0.c2 WHERE t1.c0 IN  (SELECT t1.c0
FROM t1  WHERE (t0.c1 IN (SELECT t0.c1 FROM t0 ))  XOR (t1.c2 IN (SELECT t1.c2 FROM t1 )));
+------+
| c2   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

[27 Nov 2022 9:12] MySQL Verification Team

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh