Bug #109212 Incorrect Hash Semijoin Execution

Submitted: 27 Nov 2022 8:34 Modified: 27 Nov 2022 9:12
Reporter: Xiu Tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.30, 8.0.31 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.