The bug is clearly semijoin related, not repeatable with other strategies. Explain: MariaDB [test]> explain SELECT * FROM t4 WHERE f10 IN ( SELECT t1.f11 FROM t1 LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 ); +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Start temporary | | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 0 | Using where | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 1 | PRIMARY | t4 | ALL | f10 | NULL | NULL | NULL | 3 | Range checked for each record (index map: 0x1); End temporary | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+
The bug is clearly semijoin related, not repeatable with other strategies. ------- -----+- ------+ ------+ ------- ------- -+----- -+----- ----+-- ----+-- ----+-- ------- ------- ------- ------- ------- ------- ------- ------- -----+ ------- -----+- ------+ ------+ ------- ------- -+----- -+----- ----+-- ----+-- ----+-- ------- ------- ------- ------- ------- ------- ------- ------- -----+ ------- -----+- ------+ ------+ ------- ------- -+----- -+----- ----+-- ----+-- ----+-- ------- ------- ------- ------- ------- ------- ------- ------- -----+
Explain:
MariaDB [test]> explain SELECT * FROM t4 WHERE f10 IN ( SELECT t1.f11 FROM t1 LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 );
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Start temporary |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 0 | Using where |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | PRIMARY | t4 | ALL | f10 | NULL | NULL | NULL | 3 | Range checked for each record (index map: 0x1); End temporary |
+----+-