您好,这个不是 bug,mysql 8.0 也有一样的表现,是为了支持关联子查询引入的,子查询中支持外部 table 列,这里因为 col2 在 test1 上不存在,就被自动匹配到了 test2,详细可参考文档 https://docs.pingcap.com/zh/tidb/stable/subquery-optimization 的说明。
从下面的执行计划可以看出:
mysql> explain select * from t1 where id in (select id from t2 where c1="xxxxx");
+------------------------------+----------+-----------+---------------+--------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+--------------------------------------------------+
| HashJoin_9 | 7.99 | root | | semi join, equal:[eq(test.t1.id, test.t2.id)] |
| ├─TableReader_15(Build) | 9990.00 | root | | data:Selection_14 |
| │ └─Selection_14 | 9990.00 | cop[tikv] | | not(isnull(test.t2.id)) |
| │ └─TableFullScan_13 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 9.99 | root | | data:Selection_11 |
| └─Selection_11 | 9.99 | cop[tikv] | | eq(test.t1.c1, "xxxxx"), not(isnull(test.t1.id)) |
| └─TableFullScan_10 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+--------------------------------------------------+
7 rows in set (0.00 sec)
建议在子查询中明确指定 table name,避免一些异议。比如如下使用:
delete from test1 where id in (select id from test2 t2 where t2.col2=‘11’)