造了个示例，没有复现问题

CREATE TABLE t1(id INT);
CREATE TABLE t2(id INT);
CREATE TABLE t3(id INT);

INSERT INTO t1 VALUES(1),(2);
INSERT INTO t2 VALUES(1),(3);
INSERT INTO t3 VALUES(1),(4);

mysql> SELECT * FROM t1 
    -> LEFT JOIN(
    -> SELECT t2.id FROM t2 JOIN t3 ON t2.id=t3.id
    -> ) t  ON t1.id=t.id;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 
    -> LEFT JOIN(
    -> SELECT t2.id FROM t2 JOIN t3 ON t2.id=t3.id
    -> ) t  ON t1.id=t.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> SELECT * FROM t1 
    -> JOIN(
    -> SELECT t2.id FROM t2 JOIN t3 ON t2.id=t3.id
    -> ) t  ON t1.id=t.id;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 
    -> JOIN(
    -> SELECT t2.id FROM t2 JOIN t3 ON t2.id=t3.id
    -> ) t  ON t1.id=t.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

复制

你可以提供一下测试数据，以及执行计划看看

@文成
你这个和我写的不一样。
你这种我试过，没问题。
我把sql文件上传了。
我用的是mysql5.7.20，用mysql5.7.30也有问题。用mysql8就没问题了。应该是mysql的一个bug

嗯，看起来是where子句中or的问题，可能他转换有问题
如果写成 union 则结果正常

SELECT
 *
FROM
 t_host h
LEFT JOIN (
 SELECT
  r.relation_id,
  tag_name
 FROM
  t_tag t
 JOIN t_tag_relation r ON t.id = r.tag_id
 AND r.type = 2
) tag ON h.id = tag.relation_id
WHERE  
 tag.tag_name IN ('ggg')
 UNION   SELECT
 *
FROM
 t_host h
LEFT JOIN (
 SELECT
  r.relation_id,
  tag_name
 FROM
  t_tag t
 JOIN t_tag_relation r ON t.id = r.tag_id
 AND r.type = 2
) tag ON h.id = tag.relation_id 
  WHERE h.host_name IN ('0000000')
 
复制