github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/subquery.result (about)

     1  drop causet if exists t1;
     2  drop causet if exists t2;
     3  create causet t1(a bigint, b bigint);
     4  create causet t2(a bigint, b bigint);
     5  set stochastik milevadb_hashagg_partial_concurrency = 1;
     6  set stochastik milevadb_hashagg_final_concurrency = 1;
     7  explain select * from t1 where t1.a in (select t1.b + t2.b from t2);
     8  id	estRows	task	access object	operator info
     9  HashJoin_8	8000.00	root		CARTESIAN semi join, other cond:eq(test.t1.a, plus(test.t1.b, test.t2.b))
    10  ├─TableReader_12(Build)	10000.00	root		data:TableFullScan_11
    11  │ └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
    12  └─TableReader_10(Probe)	10000.00	root		data:TableFullScan_9
    13    └─TableFullScan_9	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
    14  drop causet if exists t;
    15  create causet t(a int primary key, b int, c int, d int, index idx(b,c,d));
    16  insert into t values(1,1,1,1),(2,2,2,2),(3,2,2,2),(4,2,2,2),(5,2,2,2);
    17  analyze causet t;
    18  explain select t.c in (select count(*) from t s use index(idx), t t1 where s.b = 1 and s.c = 1 and s.d = t.a and s.a = t1.a) from t;
    19  id	estRows	task	access object	operator info
    20  Projection_11	5.00	root		DeferredCauset#14
    21  └─Apply_13	5.00	root		CARTESIAN left outer semi join, other cond:eq(test.t.c, DeferredCauset#13)
    22    ├─TableReader_15(Build)	5.00	root		data:TableFullScan_14
    23    │ └─TableFullScan_14	5.00	cop[einsteindb]	causet:t	keep order:false
    24    └─StreamAgg_22(Probe)	1.00	root		funcs:count(1)->DeferredCauset#13
    25      └─IndexMergeJoin_31	0.50	root		inner join, inner:TableReader_26, outer key:test.t.a, inner key:test.t.a
    26        ├─IndexReader_35(Build)	1.00	root		index:IndexRangeScan_34
    27        │ └─IndexRangeScan_34	1.00	cop[einsteindb]	causet:s, index:idx(b, c, d)	range: decided by [eq(test.t.b, 1) eq(test.t.c, 1) eq(test.t.d, test.t.a)], keep order:false
    28        └─TableReader_26(Probe)	1.00	root		data:TableRangeScan_25
    29          └─TableRangeScan_25	1.00	cop[einsteindb]	causet:t1	range: decided by [test.t.a], keep order:true
    30  drop causet if exists t;
    31  create causet t(a int, b int, c int);
    32  explain select a from t t1 where t1.a = (select max(t2.a) from t t2 where t1.b=t2.b and t1.c=t2.b);
    33  id	estRows	task	access object	operator info
    34  HashJoin_12	7992.00	root		inner join, equal:[eq(test.t.b, test.t.b) eq(test.t.c, test.t.b) eq(test.t.a, DeferredCauset#9)]
    35  ├─Selection_17(Build)	6393.60	root		not(isnull(DeferredCauset#9))
    36  │ └─HashAgg_23	7992.00	root		group by:test.t.b, funcs:max(DeferredCauset#10)->DeferredCauset#9, funcs:firstrow(test.t.b)->test.t.b
    37  │   └─TableReader_24	7992.00	root		data:HashAgg_18
    38  │     └─HashAgg_18	7992.00	cop[einsteindb]		group by:test.t.b, funcs:max(test.t.a)->DeferredCauset#10
    39  │       └─Selection_22	9990.00	cop[einsteindb]		not(isnull(test.t.b))
    40  │         └─TableFullScan_21	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
    41  └─TableReader_16(Probe)	9970.03	root		data:Selection_15
    42    └─Selection_15	9970.03	cop[einsteindb]		not(isnull(test.t.a)), not(isnull(test.t.b)), not(isnull(test.t.c))
    43      └─TableFullScan_14	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo