inl_join(a,b) hint doesn't work in update and insert statement

[coding7805]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

--1. create 2 tables for demo

use test;
create table ods_txn_t_d03(
    id bigint not null auto_increment,
    pnbrn_cnaps varchar(5) not null,
    inst varchar(3) not null,
    txn_accno varchar(18) not null,
    txn_dt date not null,
    ori_rcrd_no varchar(9) not null,
    rcrd_no varchar(9) not null,
    acc_blng_pnbrn_orgno varchar(5) default null,
    msac_accno varchar(18) default null,
    txn_accno_sbacc_type_no varchar(4) default null,
    pd_lgcls varchar(4) default null,
    pd_des varchar(100) default null,
    yn_frz varchar(1) default null,
    txn_curr_tp varchar(3) default null,
    key idx_ods_txn_d_1(id),
    unique key ux_1(pnbrn_cnaps,inst,txn_accno,txn_dt,ori_rcrd_no,rcrd_no)
  );

create table cdm_dic_t(
    pnbrn_cnaps varchar(5) not null,
    new_accno varchar(18) not null,
    primary key(pnbrn_cnaps,new_accno) nonclustered
    );

--2. use explain to check the plan

explain 
update 
/*+ use_index(b,), inl_join(b,a) */
    ods_txn_t_d03 b,
    (
        select t1.pnbrn_cnaps,
               t1.new_accno
        from cdm_dic_t t1
        where t1.pnbrn_cnaps = '40001'
     ) a
     set b.yn_frz = '1'
where b._tidb_rowid between 1 and 10000
and b.txn_dt = str_to_date('20221201', '%Y%m%d')
and b.pnbrn_cnaps = a.pnbrn_cnaps
and b.txn_accno = a.new_accno
and b.pnbrn_cnaps = '40001'; 

2. What did you expect to see? (Required)

table a and b use IndexJoin

3. What did you see instead (Required)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(b, a) */ or /*+ TIDB_INLJ(b, a) */ is inapplicable |
+---------+------+--------------------------------------------------------------------------------+
### 4. What is your TiDB version? (Required)
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.2
Edition: Community
Git Commit Hash: 5d2030e1d19629b71811c3f14514bad7ed63261a
Git Branch: heads/refs/tags/v6.1.2
UTC Build Time: 2022-10-18 07:57:53
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[coding7805]

/** this issue also occurs in the insert ... select statement like the following case **/

use test;
create table tbl_miss(
    id bigint(20) unsigned not null auto_random(5)
    ,txn_dt date default null
    ,perip_sys_uuid varchar(32) not null
    ,rvrs_idr varchar(1) not null
    ,file_pnbrn_cnaps varchar(5) default null
    ,glbl_sn varchar(34) default null
    ,glbl_bsn_trck_no varchar(200)
    ,sbmsn_scn_refno varchar(8) default null
    ,primary key(id) clustered
    ,key idx1 (txn_dt, perip_sys_uuid, rvrs_idr)
    ,key idx2 (txn_dt, glbl_sn,glbl_bsn_trck_no,sbmsn_scn_refno,rvrs_idr)
   );
   
create table tbl_target(
     id bigint(20) unsigned not null auto_random(5)
    ,txn_dt date default null
    ,uuid varchar(32) not null
    ,ctiq_trty char(4)
    ,txn_tlr_refno char(7)
    ,txn_org_refno char(5) 
    ,atomt_cd varchar(10)
    ,orgnt_cd varchar(10)
    ,txn_chnl char(6)
    ,rvrs_idr char(1)
    ,expd_inf varchar(5000)
    ,last_mnplt_idr char(2)
    ,glbl_sn varchar(34)
    ,primary key(id) clustered
    ,key idx_1(uuid)
    ,key idx_2(glbl_sn)
 );
 
 create table tbl_src(
     txn_dt date default null
    ,uuid varchar(32) not null
    ,ctiq_trty char(4)
    ,txn_tlr_refno char(7)
    ,txn_org_refno char(5) 
    ,atomt_cd varchar(10)
    ,orgnt_cd varchar(10)
    ,txn_chnl char(6)
    ,rvrs_idr char(1)
    ,expd_inf varchar(5000)
    ,last_mnplt_idr char(2)
    ,glbl_sn varchar(34)
    ,primary key(uuid,rvrs_idr) nonclustered
 );
 
 explain
 insert into tbl_target(
     txn_dt 
    ,uuid 
    ,ctiq_trty 
    ,txn_tlr_refno 
    ,txn_org_refno 
    ,atomt_cd 
    ,orgnt_cd 
    ,txn_chnl 
    ,rvrs_idr 
    ,expd_inf 
    ,last_mnplt_idr 
  )
  select   /*+ use_index(mis,) inl_join(src) */
     src.txn_dt 
    ,src.uuid 
    ,src.ctiq_trty 
    ,src.txn_tlr_refno 
    ,src.txn_org_refno 
    ,src.atomt_cd 
    ,src.orgnt_cd 
    ,src.txn_chnl 
    ,src.rvrs_idr 
    ,src.expd_inf 
    ,'01'
 from tbl_miss mis
     ,tbl_src src
 where src.txn_dt >= str_to_date('20221201', '%Y%m%d')
 and mis.id between 1 and 10000
 and mis.perip_sys_uuid = src.uuid
 and mis.rvrs_idr = src.rvrs_idr
 and mis.txn_dt = src.txn_dt
 and (
     case when isnull(src.expd_inf) = 1 then ''
          when instr(concat_ws('',src.expd_inf,'~~'), '~~a4' ) = 0 then ''
     else
         substr(concat_ws('',src.expd_inf,'~~'),
             instr(concat_ws('',src.expd_inf,'~~'),'~~a4') + 4,
             instr(substr(concat_ws('',src.expd_inf,'~~'),
                 instr(concat_ws('',src.expd_inf,'~~'),'~~a4') + 4, length(concat_ws('',src.expd_inf,'~~'))),'~~') -1)
     end
 ) != '01'

---- output ----
Optimizer Hint /*+ INL_JOIN(src) / or /+ TIDB_INLJ(src) */ is inapplicable

[Yisaer]

For now we currently support the PROJ and Selection as the inner child of index join with switch controlled, also we generated many random test https://github.com/pingcap/tidb-test/pull/2081, thus we closed this issue now.