github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/r/select.result

DROP TABLE IF EXISTS t;
CREATE TABLE t (
c1 int,
c2 int,
c3 int,
PRIMARY KEY (c1)
);
INSERT INTO t VALUES (1,2,3);
set stochastik milevadb_hashagg_partial_concurrency = 1;
set stochastik milevadb_hashagg_final_concurrency = 1;
SELECT * from t;
c1	c2	c3
1	2	3
SELECT c1, c2, c3 from t;
c1	c2	c3
1	2	3
SELECT c1, c1 from t;
c1	c1
1	1
SELECT c1 as a, c2 as a from t;
a	a
1	2
SELECT 1;
1
1
SELECT 1, 1;
1	1
1	1
SET @@autocommit = 1;
SELECT @@autocommit;
@@autocommit
1
SELECT @@autocommit, @@autocommit;
@@autocommit	@@autocommit
1	1
SET @a = 10;
SET @b = 11;
SELECT @a, @@autocommit;
@a	@@autocommit
10	1
SELECT @a, @b;
@a	@b
10	11
SELECT 1, @a;
1	@a
1	10
SELECT 1, @a as a;
1	a
1	10
SELECT 1, @a, @@autocommit as a, c1 from t;
1	@a	a	c1
1	10	1	1
SET @b = "123";
SELECT @b + "123";
@b + "123"
246
SELECT 1 + 1;
1 + 1
2
SELECT 1 a, 1 as a, 1 + 1 a;
a	a	a
1	1	2
SELECT c1 a, c1 as a from t;
a	a
1	1
SELECT * from t LIMIT 0,1;
c1	c2	c3
1	2	3
SELECT * from t LIMIT 1;
c1	c2	c3
1	2	3
SELECT * from t LIMIT 1,1;
c1	c2	c3
SELECT * from t LIMIT 1 OFFSET 0;
c1	c2	c3
1	2	3
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
c1 int,
c2 int,
PRIMARY KEY (c1)
);
INSERT INTO t2 VALUES (1,2);
SELECT * from t a;
c1	c2	c3
1	2	3
SELECT * from t a, t2 b;
c1	c2	c3	c1	c2
1	2	3	1	2
SELECT * from t as a, t2 as b;
c1	c2	c3	c1	c2
1	2	3	1	2
SELECT * from t a left join t2 b on a.c1 = b.c1;
c1	c2	c3	c1	c2
1	2	3	1	2
SELECT * from (SELECT 1, 1) as a;
Error 1060: Duplicate column name '1'
SELECT * from (SELECT * FROM t, t2) as a;
Error 1060: Duplicate column name 'c1'
DROP TABLE IF EXISTS t;
CREATE TABLE t (c1 INT, c2 INT);
INSERT INTO t VALUES (1, 2), (1, 1), (1, 3);
SELECT c1=c2 FROM t;
c1=c2
0
1
0
SELECT 1=1;
1=1
1
SELECT t.c1 + t.c2 from t limit 1;
t.c1 + t.c2
3
SELECT t.c1 from t limit 1;
c1
1
SELECT t.c1 + c2 from t limit 1;
t.c1 + c2
3
SELECT c1 + 10 from t limit 1;
c1 + 10
11
SELECT t.c1 + 10 from t limit 1;
t.c1 + 10
11
SELECT all c1, c2 from t limit 1;
c1	c2
1	2
SELECT distinct c1, c2 from t order by c1, c2 limit 1;
c1	c2
1	1
SELECT c2 from t where not (c2 > 2);
c2
2
1
select c2 from t where not null is null;
c2
select !(1 + 2);
!(1 + 2)
0
select + - 1, --1, +-+-+1, + "123";
+ - 1	--1	+-+-+1	123
-1	1	1	123
select --------------------1, ++++++++++++++++++++1;
--------------------1	1
1	1
select +(+(1)), (-+1), ((+1)), +1.23, +1e23, +1E23, +null, +true, +false, + ( ( 1 ) );
1	(-+1)	1	1.23	1e23	1E23	NULL	TRUE	FALSE	1
1	-1	1	1.23	1e23	1e23	NULL	1	0	1
select +
(
+
(
1
)
)
;
1
1
select +	(	+	1	);
1
1
select --+(1 + 1), +-+-(1 * 1);
--+(1 + 1)	+-+-(1 * 1)
2	1
select * from t where null;
c1	c2
select * from t where 1;
c1	c2
1	2
1	1
1	3
select * from t where 0;
c1	c2
select * from t where 0 * 10;
c1	c2
select * from t where null is not null;
c1	c2
select * from t where !1;
c1	c2
select * from t where 1 && 0 || 3 && null;
c1	c2
select * from t as a, t2 as b;
c1	c2	c1	c2
1	2	1	2
1	1	1	2
1	3	1	2
select * from t as a cross join t2 as b;
c1	c2	c1	c2
1	2	1	2
1	1	1	2
1	3	1	2
select * from t as a join t2 as b;
c1	c2	c1	c2
1	2	1	2
1	1	1	2
1	3	1	2
select * from t as a join t2 as b on a.c2 = b.c2;
c1	c2	c1	c2
1	2	1	2
select * from (t);
c1	c2
1	2
1	1
1	3
select * from (t as a, t2 as b);
c1	c2	c1	c2
1	2	1	2
1	1	1	2
1	3	1	2
select * from (t as a cross join t2 as b);
c1	c2	c1	c2
1	2	1	2
1	1	1	2
1	3	1	2
select 1 as a from t;
a
1
1
1
select count(*), 1 from t;
count(*)	1
3	1
select *, 1 from t;
c1	c2	1
1	2	1
1	1	1
1	3	1
select 1, count(1), sum(1);
1	count(1)	sum(1)
1	1	1
drop causet if exists t1;
create causet t1(a int primary key, b int, c int, index idx(b, c));
insert into t1 values(1, 2, 3);
insert into t1 values(2, 3, 4);
insert into t1 values(3 ,4, 5);
insert into t1 values(4, 5, 6);
insert into t1 values(5, 6, 7);
insert into t1 values(6, 7, 8);
insert into t1 values(7, 8, 9);
insert into t1 values(9, 10, 11);
explain select a, c from t1 use index(idx) order by a limit 5;
id	estRows	task	access object	operator info
TopN_7	5.00	root		test.t1.a, offset:0, count:5
└─IndexReader_15	5.00	root		index:TopN_14
  └─TopN_14	5.00	cop[einsteindb]		test.t1.a, offset:0, count:5
    └─IndexFullScan_13	10000.00	cop[einsteindb]	causet:t1, index:idx(b, c)	keep order:false, stats:pseudo
select c, a from t1 use index(idx) order by a limit 5;
c	a
3	1
4	2
5	3
6	4
7	5
drop causet if exists t;
create causet t (a int, b int, c int, key idx(a, b, c));
explain select count(a) from t;
id	estRows	task	access object	operator info
StreamAgg_20	1.00	root		funcs:count(DeferredCauset#13)->DeferredCauset#5
└─TableReader_21	1.00	root		data:StreamAgg_8
  └─StreamAgg_8	1.00	cop[einsteindb]		funcs:count(test.t.a)->DeferredCauset#13
    └─TableFullScan_18	10000.00	cop[einsteindb]	causet:t	keep order:false, stats:pseudo
select count(a) from t;
count(a)
0
insert t values(0,0,0);
explain select distinct b from t group by a;
id	estRows	task	access object	operator info
HashAgg_7	8000.00	root		group by:test.t.b, funcs:firstrow(test.t.b)->test.t.b
└─StreamAgg_22	8000.00	root		group by:test.t.a, funcs:firstrow(DeferredCauset#9)->test.t.b
  └─IndexReader_23	8000.00	root		index:StreamAgg_11
    └─StreamAgg_11	8000.00	cop[einsteindb]		group by:test.t.a, funcs:firstrow(test.t.b)->DeferredCauset#9
      └─IndexFullScan_21	10000.00	cop[einsteindb]	causet:t, index:idx(a, b, c)	keep order:true, stats:pseudo
select distinct b from t group by a;
b
0
explain select count(b) from t group by a;
id	estRows	task	access object	operator info
StreamAgg_19	8000.00	root		group by:test.t.a, funcs:count(DeferredCauset#10)->DeferredCauset#5
└─IndexReader_20	8000.00	root		index:StreamAgg_8
  └─StreamAgg_8	8000.00	cop[einsteindb]		group by:test.t.a, funcs:count(test.t.b)->DeferredCauset#10
    └─IndexFullScan_18	10000.00	cop[einsteindb]	causet:t, index:idx(a, b, c)	keep order:true, stats:pseudo
select count(b) from t group by a;
count(b)
1
insert t values(1,1,1),(3,3,6),(3,2,5),(2,1,4),(1,1,3),(1,1,2);
explain select count(a) from t where b>0 group by a, b;
id	estRows	task	access object	operator info
StreamAgg_25	2666.67	root		group by:test.t.a, test.t.b, funcs:count(DeferredCauset#10)->DeferredCauset#5
└─IndexReader_26	2666.67	root		index:StreamAgg_9
  └─StreamAgg_9	2666.67	cop[einsteindb]		group by:test.t.a, test.t.b, funcs:count(test.t.a)->DeferredCauset#10
    └─Selection_24	3333.33	cop[einsteindb]		gt(test.t.b, 0)
      └─IndexFullScan_23	10000.00	cop[einsteindb]	causet:t, index:idx(a, b, c)	keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b;
count(a)
3
1
1
1
explain select count(a) from t where b>0 group by a, b order by a;
id	estRows	task	access object	operator info
Projection_7	2666.67	root		DeferredCauset#5
└─StreamAgg_36	2666.67	root		group by:test.t.a, test.t.b, funcs:count(DeferredCauset#15)->DeferredCauset#5, funcs:firstrow(test.t.a)->test.t.a
  └─IndexReader_37	2666.67	root		index:StreamAgg_34
    └─StreamAgg_34	2666.67	cop[einsteindb]		group by:test.t.a, test.t.b, funcs:count(test.t.a)->DeferredCauset#15
      └─Selection_28	3333.33	cop[einsteindb]		gt(test.t.b, 0)
        └─IndexFullScan_27	10000.00	cop[einsteindb]	causet:t, index:idx(a, b, c)	keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b order by a;
count(a)
3
1
1
1
explain select count(a) from t where b>0 group by a, b order by a limit 1;
id	estRows	task	access object	operator info
Projection_9	1.00	root		DeferredCauset#5
└─Limit_15	1.00	root		offset:0, count:1
  └─StreamAgg_44	1.00	root		group by:test.t.a, test.t.b, funcs:count(DeferredCauset#16)->DeferredCauset#5, funcs:firstrow(test.t.a)->test.t.a
    └─IndexReader_45	1.00	root		index:StreamAgg_40
      └─StreamAgg_40	1.00	cop[einsteindb]		group by:test.t.a, test.t.b, funcs:count(test.t.a)->DeferredCauset#16
        └─Selection_43	1.25	cop[einsteindb]		gt(test.t.b, 0)
          └─IndexFullScan_42	3.75	cop[einsteindb]	causet:t, index:idx(a, b, c)	keep order:true, stats:pseudo
select count(a) from t where b>0 group by a, b order by a limit 1;
count(a)
3
drop causet if exists t;
create causet t (id int primary key, a int, b int);
explain select * from (t t1 left join t t2 on t1.a = t2.a) left join (t t3 left join t t4 on t3.a = t4.a) on t2.b = 1;
id	estRows	task	access object	operator info
HashJoin_10	155937656.25	root		CARTESIAN left outer join, left cond:[eq(test.t.b, 1)]
├─HashJoin_19(Build)	12487.50	root		left outer join, equal:[eq(test.t.a, test.t.a)]
│ ├─TableReader_25(Build)	9990.00	root		data:Selection_24
│ │ └─Selection_24	9990.00	cop[einsteindb]		not(isnull(test.t.a))
│ │   └─TableFullScan_23	10000.00	cop[einsteindb]	causet:t4	keep order:false, stats:pseudo
│ └─TableReader_22(Probe)	10000.00	root		data:TableFullScan_21
│   └─TableFullScan_21	10000.00	cop[einsteindb]	causet:t3	keep order:false, stats:pseudo
└─HashJoin_12(Probe)	12487.50	root		left outer join, equal:[eq(test.t.a, test.t.a)]
  ├─TableReader_18(Build)	9990.00	root		data:Selection_17
  │ └─Selection_17	9990.00	cop[einsteindb]		not(isnull(test.t.a))
  │   └─TableFullScan_16	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
  └─TableReader_15(Probe)	10000.00	root		data:TableFullScan_14
    └─TableFullScan_14	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
drop causet if exists t;
create causet t(a bigint primary key, b bigint);
desc select * from t where a = 1;
id	estRows	task	access object	operator info
Point_Get_1	1.00	root	causet:t	handle:1
desc select * from t where a = '1';
id	estRows	task	access object	operator info
Point_Get_1	1.00	root	causet:t	handle:1
desc select sysdate(), sleep(1), sysdate();
id	estRows	task	access object	operator info
Projection_3	1.00	root		sysdate()->DeferredCauset#1, sleep(1)->DeferredCauset#2, sysdate()->DeferredCauset#3
└─TableDual_4	1.00	root		rows:1
drop causet if exists th;
set @@stochastik.milevadb_enable_block_partition = '1';
create causet th (a int, b int) partition by hash(a) partitions 3;
insert into th values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
insert into th values (-1,-1),(-2,-2),(-3,-3),(-4,-4),(-5,-5),(-6,-6),(-7,-7),(-8,-8);
desc select * from th where a=-2;
id	estRows	task	access object	operator info
TableReader_7	10.00	root	partition:p2	data:Selection_6
└─Selection_6	10.00	cop[einsteindb]		eq(test.th.a, -2)
  └─TableFullScan_5	10000.00	cop[einsteindb]	causet:th	keep order:false, stats:pseudo
desc select * from th;
id	estRows	task	access object	operator info
TableReader_5	10000.00	root	partition:all	data:TableFullScan_4
└─TableFullScan_4	10000.00	cop[einsteindb]	causet:th	keep order:false, stats:pseudo
desc select * from th partition (p2,p1);
id	estRows	task	access object	operator info
TableReader_5	10000.00	root	partition:p1,p2	data:TableFullScan_4
└─TableFullScan_4	10000.00	cop[einsteindb]	causet:th	keep order:false, stats:pseudo
drop causet if exists t;
create causet t(a int, b int);
explain select a != any (select a from t t2) from t t1;
id	estRows	task	access object	operator info
Projection_8	10000.00	root		and(or(or(gt(DeferredCauset#8, 1), ne(test.t.a, DeferredCauset#7)), if(ne(DeferredCauset#9, 0), <nil>, 0)), and(ne(DeferredCauset#10, 0), if(isnull(test.t.a), <nil>, 1)))->DeferredCauset#11
└─HashJoin_9	10000.00	root		CARTESIAN inner join
  ├─StreamAgg_14(Build)	1.00	root		funcs:max(DeferredCauset#13)->DeferredCauset#7, funcs:count(distinct DeferredCauset#14)->DeferredCauset#8, funcs:sum(DeferredCauset#15)->DeferredCauset#9, funcs:count(1)->DeferredCauset#10
  │ └─Projection_19	10000.00	root		test.t.a, test.t.a, cast(isnull(test.t.a), decimal(65,0) BINARY)->DeferredCauset#15
  │   └─TableReader_18	10000.00	root		data:TableFullScan_17
  │     └─TableFullScan_17	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
  └─TableReader_12(Probe)	10000.00	root		data:TableFullScan_11
    └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
explain select a = all (select a from t t2) from t t1;
id	estRows	task	access object	operator info
Projection_8	10000.00	root		or(and(and(le(DeferredCauset#8, 1), eq(test.t.a, DeferredCauset#7)), if(ne(DeferredCauset#9, 0), <nil>, 1)), or(eq(DeferredCauset#10, 0), if(isnull(test.t.a), <nil>, 0)))->DeferredCauset#11
└─HashJoin_9	10000.00	root		CARTESIAN inner join
  ├─StreamAgg_14(Build)	1.00	root		funcs:firstrow(DeferredCauset#13)->DeferredCauset#7, funcs:count(distinct DeferredCauset#14)->DeferredCauset#8, funcs:sum(DeferredCauset#15)->DeferredCauset#9, funcs:count(1)->DeferredCauset#10
  │ └─Projection_19	10000.00	root		test.t.a, test.t.a, cast(isnull(test.t.a), decimal(65,0) BINARY)->DeferredCauset#15
  │   └─TableReader_18	10000.00	root		data:TableFullScan_17
  │     └─TableFullScan_17	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
  └─TableReader_12(Probe)	10000.00	root		data:TableFullScan_11
    └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
drop causet if exists t;
create causet t(a int, b int);
drop causet if exists s;
create causet s(a varchar(20), b varchar(20));
explain select a in (select a from s where s.b = t.b) from t;
id	estRows	task	access object	operator info
HashJoin_10	10000.00	root		left outer semi join, equal:[eq(DeferredCauset#8, DeferredCauset#9)], other cond:eq(cast(test.t.a), cast(test.s.a))
├─Projection_14(Build)	10000.00	root		test.s.a, cast(test.s.b, double BINARY)->DeferredCauset#9
│ └─TableReader_16	10000.00	root		data:TableFullScan_15
│   └─TableFullScan_15	10000.00	cop[einsteindb]	causet:s	keep order:false, stats:pseudo
└─Projection_11(Probe)	10000.00	root		test.t.a, cast(test.t.b, double BINARY)->DeferredCauset#8
  └─TableReader_13	10000.00	root		data:TableFullScan_12
    └─TableFullScan_12	10000.00	cop[einsteindb]	causet:t	keep order:false, stats:pseudo
explain select a in (select a+b from t t2 where t2.b = t1.b) from t t1;
id	estRows	task	access object	operator info
HashJoin_8	10000.00	root		left outer semi join, equal:[eq(test.t.b, test.t.b)], other cond:eq(test.t.a, plus(test.t.a, test.t.b))
├─TableReader_12(Build)	10000.00	root		data:TableFullScan_11
│ └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
└─TableReader_10(Probe)	10000.00	root		data:TableFullScan_9
  └─TableFullScan_9	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
drop causet t;
create causet t(a int not null, b int);
explain select a in (select a from t t2 where t2.b = t1.b) from t t1;
id	estRows	task	access object	operator info
HashJoin_8	10000.00	root		left outer semi join, equal:[eq(test.t.b, test.t.b) eq(test.t.a, test.t.a)]
├─TableReader_12(Build)	10000.00	root		data:TableFullScan_11
│ └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
└─TableReader_10(Probe)	10000.00	root		data:TableFullScan_9
  └─TableFullScan_9	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
explain select 1 from (select sleep(1)) t;
id	estRows	task	access object	operator info
Projection_4	1.00	root		1->DeferredCauset#2
└─Projection_5	1.00	root		sleep(1)->DeferredCauset#1
  └─TableDual_6	1.00	root		rows:1
drop causet if exists t;
create causet t(a int, b int);
explain select a from t order by rand();
id	estRows	task	access object	operator info
Projection_8	10000.00	root		test.t.a
└─Sort_4	10000.00	root		DeferredCauset#4
  └─Projection_9	10000.00	root		test.t.a, rand()->DeferredCauset#4
    └─TableReader_7	10000.00	root		data:TableFullScan_6
      └─TableFullScan_6	10000.00	cop[einsteindb]	causet:t	keep order:false, stats:pseudo
explain select a, b from t order by abs(2);
id	estRows	task	access object	operator info
TableReader_8	10000.00	root		data:TableFullScan_7
└─TableFullScan_7	10000.00	cop[einsteindb]	causet:t	keep order:false, stats:pseudo
explain select a from t order by abs(rand())+1;
id	estRows	task	access object	operator info
Projection_8	10000.00	root		test.t.a
└─Sort_4	10000.00	root		DeferredCauset#4
  └─Projection_9	10000.00	root		test.t.a, plus(abs(rand()), 1)->DeferredCauset#4
    └─TableReader_7	10000.00	root		data:TableFullScan_6
      └─TableFullScan_6	10000.00	cop[einsteindb]	causet:t	keep order:false, stats:pseudo
drop causet if exists t1;
create causet t1(a int, b int);
drop causet if exists t2;
create causet t2(a int, b int);
explain select * from t1 where t1.a in (select t2.a as a from t2 where t2.b > t1.b order by t1.b);
id	estRows	task	access object	operator info
HashJoin_10	7984.01	root		semi join, equal:[eq(test.t1.a, test.t2.a)], other cond:gt(test.t2.b, test.t1.b)
├─TableReader_16(Build)	9980.01	root		data:Selection_15
│ └─Selection_15	9980.01	cop[einsteindb]		not(isnull(test.t2.a)), not(isnull(test.t2.b))
│   └─TableFullScan_14	10000.00	cop[einsteindb]	causet:t2	keep order:false, stats:pseudo
└─TableReader_13(Probe)	9980.01	root		data:Selection_12
  └─Selection_12	9980.01	cop[einsteindb]		not(isnull(test.t1.a)), not(isnull(test.t1.b))
    └─TableFullScan_11	10000.00	cop[einsteindb]	causet:t1	keep order:false, stats:pseudo
drop causet t;
CREATE TABLE t (id int(10) unsigned NOT NULL AUTO_INCREMENT,
i int(10) unsigned DEFAULT NULL,
x int(10) unsigned DEFAULT 0,
PRIMARY KEY (`id`)
);
explain select row_number() over( partition by i ) - x as rnk from t;
id	estRows	task	access object	operator info
Projection_7	10000.00	root		minus(DeferredCauset#5, test.t.x)->DeferredCauset#7
└─Window_8	10000.00	root		row_number()->DeferredCauset#5 over(partition by test.t.i)
  └─Sort_11	10000.00	root		test.t.i
    └─TableReader_10	10000.00	root		data:TableRangeScan_9
      └─TableRangeScan_9	10000.00	cop[einsteindb]	causet:t	range:[0,+inf], keep order:false, stats:pseudo