MariaDB - 当连接中的列之间存在数据类型不匹配时，即使在强制转换后也不使用索引

Question

我正在尝试连接两个表，其中连接列之间的数据类型不匹配。即使在显式转换后（或在强制索引后），索引也不会被拾取。可能是什么原因？

create table table_a (
    table_a_col1 decimal(18) 
); 

create table table_b (
    rec_id decimal(18) ,
    table_b_col2 varchar(18)
);

create index table_b_col2_ndx on table_b (table_b_col2);

insert into table_a values (1);
insert into table_a values (2);

insert into table_b values (1,'1');
insert into table_b values (2,'2');
insert into table_b values (4,'S');

commit;

不进行任何转换的

explain
select  *
from table_a
left join table_b 
on  (table_b_col2 = table_a_col1)
where table_a_col1 = 2;

|id |select_type|table  |type|possible_keys   |key|key_len|ref|rows|Extra                                         |
|---|-----------|-------|----|----------------|---|-------|---|----|----------------------------------------------|
|1  |SIMPLE     |table_a|ALL |                |   |       |   |2   |Using where                                   |
|1  |SIMPLE     |table_b|ALL |table_b_col2_ndx|   |       |   |3   |Range checked for each record (index map: 0x1)|

计划 显式数据类型转换后的

explain
select  *
from table_a
left join table_b 
on  (table_b_col2 = cast(table_a_col1 as character))
where table_a_col1 = 2;

|id |select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra                                          |
|---|-----------|-------|----|-------------|---|-------|---|----|-----------------------------------------------|
|1  |SIMPLE     |table_a|ALL |             |   |       |   |2   |Using where                                    |
|1  |SIMPLE     |table_b|ALL |             |   |       |   |3   |Using where; Using join buffer (flat, BNL join)|

计划强制索引后的计划

explain
select  *
from table_a
left join table_b force index (table_b_col2_ndx)
on  (table_b_col2 = cast(table_a_col1 as character))
where table_a_col1 = 2;

|id |select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra                                          |
|---|-----------|-------|----|-------------|---|-------|---|----|-----------------------------------------------|
|1  |SIMPLE     |table_a|ALL |             |   |       |   |2   |Using where                                    |
|1  |SIMPLE     |table_b|ALL |             |   |       |   |3   |Using where; Using join buffer (flat, BNL join)|


select version() ;

|version()         |
|------------------|
|10.2.9-MariaDB-log|