# [KB] in 子查询无法转换成普通 join ## Key Words in, subquery, semi join, inner join, hash join, index join, hint 失效, 隐式转换 ## Description 子查询无法被转换成 inner join，查看执行计划发现 eq(eme.test1.cardno, eme.test2.cardno) 关联条件无法下推到 probe 端进行数据过滤，查看 plan 没有异常，也不存在隐式转换问题。添加 hint 有 warning，详细 plan 如下： ``` MySQL [eme]> explain SELECT /*+ INL_JOIN(test1)*/* FROM test1 use index(index_1) WHERE cardno in (select p.cardno from test2 p where p.`enable`=1 and p.audit_status=2 and p.cardno in ( '51018420000808005X' , '510111197412281571' , '511025199111206175' , '510602198203126335' , '511025199112254291' , '512922197004150358' , '510105198107271833' , '511322199612157110' , '511622198912044611' , '510125198706023116' , '512925196902041115' , '510303199603130514' , '510214197309120854' , '510122198701291913' , '513021199303017295' , '510131196312183414' , '511321199110176075' , '512529197112032634' , '511027196709016973' , '510125199609143815' ) ); +------------------------------------+----------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------+----------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HashJoin_25 | 8000.00 | root | | semi join, equal:[eq(eme.test1.cardno, eme.test2.cardno)] | | ├─IndexLookUp_35(Build) | 0.00 | root | | | | │ ├─IndexRangeScan_32(Build) | 0.10 | cop[tikv] | table:p, index:p_index_6(audit_status, enable) | range:[2.000000000000000000000000000000 1,2.000000000000000000000000000000 1], keep order:false, stats:pseudo | | │ └─Selection_34(Probe) | 0.00 | cop[tikv] | | in(eme.test2.cardno, "51018420000808005X", "510111197412281571", "511025199111206175", "510602198203126335", "511025199112254291", "512922197004150358", "510105198107271833", "511322199612157110", "511622198912044611", "510125198706023116", "512925196902041115", "510303199603130514", "510214197309120854", "510122198701291913", "513021199303017295", "510131196312183414", "511321199110176075", "512529197112032634", "511027196709016973", "510125199609143815"), not(isnull(eme.test2.cardno)) | | │ └─TableRowIDScan_33 | 0.10 | cop[tikv] | table:p | keep order:false, stats:pseudo | | └─IndexLookUp_28(Probe) | 10000.00 | root | | | | ├─IndexFullScan_26(Build) | 10000.00 | cop[tikv] | table:test1, index:index_1(cardno) | keep order:false, stats:pseudo | | └─TableRowIDScan_27(Probe) | 10000.00 | cop[tikv] | table:test1 | keep order:false, stats:pseudo | +------------------------------------+----------+-----------+------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) MySQL [eme]> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1815 | There are no matching table names for (test1) in optimizer hint /*+ INL_JOIN(test1) */ or /*+ TIDB_INLJ(test1) */. Maybe you can use the table alias name | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ## Root Cause 对于这个 case semi join 无法转成 inner join，原因是两个表的关联列 collaction 不一样： * test1 表列  `cardno`  COLLATE utf8_general_ci * test2 表列 `cardno`  COLLATE utf8mb4_bin tidb 在对 in 子查询进行转换 join 时，为了保证转换 rewrite 的正确性，tidb 优化器需要做一系列的[逻辑](https://github.com/pingcap/tidb/blob/master/planner/core/expression_rewriter.go#L984-L999)判断，对于相关联的左右列 collaction 不一致时会跳过转换而选择使用 semi join 的方式。 ## Resolution NA ## Workaround 修改表列的 collaction ，使左右表关联列的 collaction 一致。 ## Affect Version ALL