Bug #119492 Inconsistent data types of the IN condition in JOIN queries leads to incorrect query results.

• Submitted: 2 Dec 11:57
• Modified: 3 Dec 3:38
• Reporter: Go Yakult
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[2 Dec 11:57] Go Yakult

Description:
mysql> SELECT t0.c3 FROM t3 LEFT JOIN t1 ON 0 = 0 LEFT JOIN t0 ON 0 WHERE t3.c9 IN(SELECT c5 FROM t1 WHERE c5);
+------+
| c3   |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT t0.c3 FROM t3 LEFT JOIN t0 ON 0 WHERE t3.c9 IN(SELECT c5 FROM t1 WHERE c5);
+------+
| c3   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

It is evident that the first LEFT JOIN in the first query can be removed, and the query results of the two should remain consistent after its removal.

Unlike BUG119454, that bug would disappear by disabling hash join (with the setting SET optimizer_switch='block_nested_loop=off';), whereas my bug still persists even after disabling it.

How to repeat:
CREATE TABLE `t0` (
  `c3` float(61,21)
);
INSERT INTO `t0` VALUES (NULL);

CREATE TABLE `t1` (
  `c5` float(14,14)
);
INSERT INTO `t1` VALUES (0.42204636335373), (1.00000000000000);

CREATE TABLE `t3` (
  `c9` decimal(10,0)
);
INSERT INTO `t3` VALUES (1);

SELECT t0.c3 FROM t3 LEFT JOIN t1 ON 0 = 0 LEFT JOIN t0 ON 0 WHERE t3.c9 IN(SELECT c5 FROM t1 WHERE c5);

SELECT t0.c3 FROM t3 LEFT JOIN t0 ON 0 WHERE t3.c9 IN(SELECT c5 FROM t1 WHERE c5);

[2 Dec 12:08] Go Yakult

accidentally submitting happens! The currently provided query and data contain errors, so you can just close this submission directly.

[3 Dec 3:38] Chaithra Marsur Gopala Reddy

Closed (As per the request).