set tidb_cost_model_version=1;
use test;
drop table if exists t1, t2;
create table t1(a bigint, b bigint, index idx(a));
create table t2(a bigint, b bigint, index idx(a));
insert into t1 values(1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
insert into t2 values(1, 1);
analyze table t1, t2;
set session tidb_hashagg_partial_concurrency = 1;
set session tidb_hashagg_final_concurrency = 1;
explain format = 'brief' select /*+ TIDB_INLJ(t1, t2) */ * from t1 join t2 on t1.a=t2.a;
id	estRows	task	access object	operator info
IndexJoin	5.00	root		inner join, inner:IndexLookUp, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a)
├─TableReader(Build)	1.00	root		data:Selection
│ └─Selection	1.00	cop[tikv]		not(isnull(test.t2.a))
│   └─TableFullScan	1.00	cop[tikv]	table:t2	keep order:false
└─IndexLookUp(Probe)	5.00	root		
  ├─Selection(Build)	5.00	cop[tikv]		not(isnull(test.t1.a))
  │ └─IndexRangeScan	5.00	cop[tikv]	table:t1, index:idx(a)	range: decided by [eq(test.t1.a, test.t2.a)], keep order:false
  └─TableRowIDScan(Probe)	5.00	cop[tikv]	table:t1	keep order:false
explain format = 'brief' select * from t1 join t2 on t1.a=t2.a;
id	estRows	task	access object	operator info
Projection	5.00	root		test.t1.a, test.t1.b, test.t2.a, test.t2.b
└─HashJoin	5.00	root		inner join, equal:[eq(test.t2.a, test.t1.a)]
  ├─TableReader(Build)	1.00	root		data:Selection
  │ └─Selection	1.00	cop[tikv]		not(isnull(test.t2.a))
  │   └─TableFullScan	1.00	cop[tikv]	table:t2	keep order:false
  └─TableReader(Probe)	5.00	root		data:Selection
    └─Selection	5.00	cop[tikv]		not(isnull(test.t1.a))
      └─TableFullScan	5.00	cop[tikv]	table:t1	keep order:false
drop table if exists t1, t2;
create table t1(a int not null, b int not null);
create table t2(a int not null, b int not null, key a(a));
set @@tidb_opt_insubq_to_join_and_agg=0;
explain format = 'brief' select /*+ TIDB_INLJ(t2@sel_2) */ * from t1 where t1.a in (select t2.a from t2);
id	estRows	task	access object	operator info
IndexJoin	8000.00	root		semi join, inner:IndexReader, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a)
├─TableReader(Build)	10000.00	root		data:TableFullScan
│ └─TableFullScan	10000.00	cop[tikv]	table:t1	keep order:false, stats:pseudo
└─IndexReader(Probe)	12500.00	root		index:IndexRangeScan
  └─IndexRangeScan	12500.00	cop[tikv]	table:t2, index:a(a)	range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo
show warnings;
Level	Code	Message
set @@tidb_opt_insubq_to_join_and_agg=1;
drop table if exists t1, t2;
create table t1(a int not null, b int not null, key a(a));
create table t2(a int not null, b int not null, key a(a));
explain format = 'brief' select /*+ TIDB_INLJ(t1) */ * from t1 where t1.a in (select t2.a from t2);
id	estRows	task	access object	operator info
IndexJoin	10000.00	root		inner join, inner:IndexLookUp, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a)
├─StreamAgg(Build)	8000.00	root		group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a
│ └─IndexReader	10000.00	root		index:IndexFullScan
│   └─IndexFullScan	10000.00	cop[tikv]	table:t2, index:a(a)	keep order:true, stats:pseudo
└─IndexLookUp(Probe)	10000.00	root		
  ├─IndexRangeScan(Build)	10000.00	cop[tikv]	table:t1, index:a(a)	range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo
  └─TableRowIDScan(Probe)	10000.00	cop[tikv]	table:t1	keep order:false, stats:pseudo